SCCM can be a goldmine when hunting for evil, all you need to do is enable some inventory collections, send them to Splunk and get creative. While the data is snapshot in time (usually the last 24-hours) it can be a great first start when dealing with incidents, plus most enterprises already have SCCM.
In this post all breeze over the setup & configuration of and focus more on some of the searches you can do to find evil. As a bonus, you can you use this to monitor the health and coverage of your security agents with some pretty dashboards in Splunk.
Some enterprise wide hunting-for-evil examples I’ll cover:
Finding service outliers
Find least frequency of occurrence for persistent mechanisms via autoruns.
Find least frequency of occurrence of installed services.
Least frequency of occurrence of executables.
Services running from abnormal paths.
Triage enterprise using IoC
Ingredients
1 Microsoft SCCM server + access to the SCCM SQL server.
1 Splunk server
1 Splunk DB Connect App
* Adult beverage is optional but strongly recommended.
Preparing SCCM
In SCCM enable the collection of Autoruns, Browser Helper Objects (BHO) and inventory collection of all executables. This collected data will be pulled into Splunk or directly querying with SQL statements.
Preparing DB Connect App in Splunk
The DB app executes the SQL queries on a scheduled basis and dumps them into a defined index.
Under "Identities" add in MS SQL server credentials. In "Connections" add in SCCM SQL server. Click validate to make sure everything is configured correctly.
Now to get useful data into Splunk you’ll need to make SQL queries. Your SQL server table names will probably be different so verify directly on SQL manager to make sure your queries work and pull back data in a way that is expected. As an example we’ll use autoruns, however this process will be the same for all queries.
Autorun lookup
Autorun has a ton of good info so you’ll want to have this data imported after the collection process runs, default (I think) is every 24-hours. Here is the query I used to import all the autorun data:
SELECT [Name] AS Hostname ,
[Description0] ,
[FileName0] ,
[FilePropertiesHash0] ,
[FilePropertiesHashEx0] ,
[FileVersion0] ,
[Location0] ,
[Product0] ,
[ProductVersion0] ,
[Publisher0] ,
[StartupType0] ,
[StartupValue0]
FROM [SCCM].[dbo].[v_GS_AUTOSTART_SOFTWARE]
LEFT JOIN [SCCM].[dbo].[v_ActiveClients] Name ON ResourceID = MachineResourceID
The LEFT JOIN statement will give you the hostname based upon the ResourceID along with all of the autorun details. Modify this to your needs and paste in “Input SQL” field.
Select batch and past in your SQL statement:
Click more settings and set the lookup period to every 24-hours, set sourcetype and index:
Save the input, take a swig and you’re done.
Hunting
If you’re not able to import the entire inventory of executables due to $plunk license, consider outputting the aggregated data from these examples or investigate Elasticsearch.
Since we now have all autorun data going to Splunk, we can use the following query to show autorun persistence that appear on less than 10 systems in the enterprise (by filename).
If you see a filename like ‘Update.exe’ which only exists on one system, probably going to want to investigate it. Get creative, search by product name, description or a combination of both. Since everything is timestamped you might consider adding a report on servers or high-valued targets like executives and their administrative staff.
Find malware that attempts to hide by using common Windows system filenames like ‘svchost.exe’ that are in the wrong directory:
If you see ‘svchost.exe’ in an appdata or temp folder, that would be red flag to investigate further. To take this example further you could include other common windows filenames with:
WHERE FileName IN ('wininit.exe', 'svchost.exe', ‘services.exe’) group by [FilePath]
It will be obvious where to investigate further by looking at the outliers. If you investigated enough malware infections you will no doubt have a list of clever names used my malware authors such as 'system.exe', 'lsass.exe', 'services.exe'. Also keep in mind the data collected by SCCM the next time you read about a new threat and how it can be used to quickly search for indictors of compromise.
Executables in $Recycle.Bin
Service outliers, least frequency of occurrence by path on disk:
Search for a service where the executable is located in an odd place like %temp% or %appdata% by appending:
WHERE [Name0] LIKE ‘%temp%
Using the same methodology, search for least frequency of occurrence based on filename. Since you have the data in Splunk you might even add an alert based upon new service running on a domain controller or publicly accessible server.
If you’re feeling really adventurous, try checking out any executables hanging out in temp folder.
Fast Triage All the Files in < 1 Minute.
If you have an incident occur or just want to triage some intelligence across the enterprise using static indicators like:
Filename
Filepath
Internal File Description
Internal File Version
Files Modified Timestamp
Autorun persistence: Registry key/value or file/lnk in startup folders.
You can use those indicators to quickly triage every binary file (or any file metadata you’re collecting) in your enterprise that was available at the time the client inventory process was ran. If you don't have the files internal file description in the provided intelligence, consider looking up the MD5 on Virustotal to get it.
In previous investigations I came across malware with the internal metadata company name of “Microsoft Corp.” which is unique since Microsoft doesn’t abbreviate Corporation. Using this we could triage every executable in the enterprise to see any others executables have this company name:
The same could be done with filename, path or using a combination of the collected metadata e.g. any executable between 55K and 57K modified in the last week. This is a good way to quickly search the enterprise while your other hunting tools are still running, but keep in mind this is searching a snapshot of time, you should know at what frequency your SCCM server collects this information.
Security Agent Health Monitoring & Coverage
As a bonus, throw in some queries to check if your security agents are installed and if their service is started. As an example, here is query to check for hosts that do not have GRR installed. In this example you can replace GRR with any application, keep in mind this query checks if the service is not present. Its also possible to query add/remove programs but you get more bang for the buck by querying services.
Add the following DB input query:
SELECT DISTINCT [ResourceID],
[Name] AS Missing_GRR
FROM [SCCM].[dbo].[v_GS_SERVICE]
LEFT JOIN [SCCM].[dbo].[v_ActiveClients] [Name]
ON [ResourceID] = [MachineResourceID]
WHERE [ResourceID] NOT IN
(select [ResourceID] FROM [SCCM].[dbo].[v_GS_SERVICE]
WHERE [DisplayName0] = 'GRR Service') AND [Name] IS NOT NULL
Set the input to run every 24-hours and now you can use a simple query in Splunk to see what hosts don’t have your security agents installed:
Add in another DB input to query the status of services for your security agents that are installed:
In Splunk you can search for disabled agents with:
index=sccm StartMode=Disabled |stats count by Service
Now you have pretty picture to show you how many of your security agents are missing from endpoints.
As you can see there is a lot of good data to find evil and also to search for indicators of compromise from known threats or when you receive new intelligence. This is only scratching the surface but hopefully will give you some ideas that are useful in your enterprise. While SCCM doesn’t collect everything, sometime it collects enough to get you what you need for hunting or during an incident. If you found anything useful for malware hunting or DFIR in SCCM database, please share it with me +Keith Tyler
Cheers and happy hunting!
