Twin Cities Security User Group

Do you provide reports to management on the current threat level for your organization? Or are you simply interested in figuring out the ‘trouble’ areas of your organization (from an endpoint protection stance)?   If so, continue reading this article is for you!

 

This article is going to provide you with the following:

 

Hard data on where and who your risky users are

Data that a good management team can sink their teeth into

Show your management team your knowledge regarding your endpoint protection stance

Allow you to track your security statistics across your entire SEPM infrastructure

Help you find gaps in your protection of endpoints

Find weak areas of your security awareness training program.

 

Sound good? I sure hope so. I have used this information to put myself directly in front of our senior leadership team as the expert (in our organization) on our stance to internet based threats, malware protection controls, and all around good guy…. Well that last part might be in my head, but I hope they see it that way. 

 

Access to your SEPM Console as an administrator

Ability to see the Monitor/Logs in SEPM (without restrictions)

Excel (or similar spreadsheet application)

 

 

With your SEPM console open click on the Monitors item in the left window pane.

Click on the Logs tab in the main window.

Choose the Log Type Risk

Click the Advanced Settings option

Click Save Filter

Name the Filter: Monthly Risk Report Metrics

Click OK after the window pops up stating the filter was saved.

Now that the saved filter is “Monthly Risk Re….” click View Log

Once your log opens, it should look similar to below.

 

Now click the Export link.

A typical Microsoft windows box opens to save the file. Normally I will save the file in the format 0709_Risk_Report.txt for historical purposes.  (Although I used an since our deployment of SEP to have some extra large numbers for show)

 

Copy the file to your local workstation (unless it’s already there of course).

 

 

Open excel

Click Data, Import External Data, Import External Data

Navigate to the location of the text file exported from SEPM

Click Open

Choose Delimited, then Next

Select Comma

Click Finish

Your view should now look similar to the below (minus the blanked sections)

 

 

 Manipulating the Data

 

First thing we need to do is to Open the data in a PivotTable. 

Click Data, PivotTable and PivotChart Report

Here’s where the rubber meets the road. I would suggest playing with the pivot table to get an idea on how well it can help you break down the data. 

 

On the sample data I am using I have a total of 8,298 infections during the past month. Show that number alone to management and it could get ugly. Why isn’t the antivirus software stopping viruses, how could over 8,000 infections be detected, that would be one per machine? We need to replace our Antivirus product posthaste. However, when we begin digging into the data we’ll see it really is not as alarming.

 

Your blank pivot table should look similar to the below image. 



Note the headings. This is what I will refer to when dropping items from our Field list. If you are familiar with Pivot tables you can probably skip this section

 

For management I typically pull the following information (broken down into each of the respective AD domains that our users log into)

 

 

There are other views which I pull from for my own reporting/analysis but more on those for another article (or update to this article).

 

The primary fields we will work with from the Field List are:

 

The PivotTable should now resemble the image below. It starts to get difficult showing images now because I need to block out so much data from our sample set. Hopefully my explanations will suffice.

 

Now under my organization’s usernames each domain begins with a unique character making it easy for me to massage the data. Here’s the (made up) key that I have for my information.

 

Username begins with	Domain user belongs to
E	Enterprise
RS	Remote Staff
A	Asia
M	Contractors (on-shore)
V	Contractors (off-shore)
Z	Domain Administrators

Now I click on the drop down for the Username option. Uncheck Show All. Select the usernames that begin with E. Now we have all the Enterprise domain users (and their associated computers which reported an infection). Use Excel to count the number of computers and usernames. You now have an accurate count of the impact to one of the domains.   In my sample data there were 32 computers and 32 users infected during the month (we have a one-to-one relationship with user to computer).

 

 

Now rinse and repeat for each of the users and we get an accurate account of the actual number of users infected with malware. I use this data to target stronger endpoint protection policies, lucky receipts of updated security awareness training material, and to demonstrate to management ‘who’ practices insecure activities on company resources. Specifically one of our major areas of infection was our Remote Staff. This domain normally had 3:1 infections when compared to our Enterprise staff. What we found by looking at the infected file location was these Remote staff users abused policies and used work laptops for personal web surfing. We implemented a remote filtering agent that controls access to specific categories of websites. Since the implementation of that the actual number of infections for our Remote Staff has dropped to a 1:1 ratio with our Enterprise users.

 

There is a great deal of information stored in the SEPM databases. I am working on a couple of downloads that will automate more of the other metric information I use however I still need to perform my normal work duties. J Play with the data exported from SEPM Logs. 

 

Another hint on a metric I use. Clear your pivot table. Put the following into the Pivot table

 

Risk Name in the Drop Row Fields Here

Occurrences in the Drop Page Fields Here

 

 

This is a quick and dirty way to get a report on the malware infecting your systems on a monthly basis. I normally save this report for comparing with previous months. You can also take and group the malware into items like:

 

Adware -- begins with Adware.

Heuristics Detected – begins with Bloodhound

Downloaders – begins with downloader

InfoStealers – begins with infostealer

Packed – begins with packed

Suspicious – begins with suspicious

Tracking Cookies

Trojans – begins with trojans

Virus – begins with anything that I missed previously.