Pulling detection data from dedicated Malware systems such as McAfee ePO is quite straightforward, simple, and fairly powerful.
The SCCM 2012 reports are arguably powerful, but whereas ePO has a fairly straightforward report creator built in, with SCCM you need to know how to use the (not very user-friendly for non-specialists) Microsoft Report Viewer and/or SQL Server Reporting Services to get exactly the data you want. I do not currently fall into the category of people who can write their own SSRS reports, but I can knock up an SQL query, so I decided to just pull the data direct from the SCCM SQL database.
The built-in Infected Computers report does show you which machines have had malware detected, but it groups them by computer, so you can’t easily get an overview of malware detections by date – if a machine has detected malware multiple times you have to drill into that machine to see all its detection dates. So whilst you can save the report output as CSV or Excel, it’s not good to me as I can’t get all the data in one place.
I like to be able to see detections over time, and store and analyse the results. For this I need raw data, so I wrote the following SQL query and PowerShell script that will pull the appropriate data out of SCCM and save it to a CSV file. When writing the SQL query, this SQL script was handy to find the tables containing the column references.
$SCCMSQL = "SCCMSQLServer.rcmtech.co.uk" $SCCMDB = "CM_RCM" $OutFile = "D:\SCCM Malware Detections.csv" # Open connection to SCCM DB $SCCMDBConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList "Server=$SCCMSQL;Database=$SCCMDB;Integrated Security=SSPI" $SCCMDBConnection.Open() # Get data $SQLCommand = $SCCMDBConnection.CreateCommand() $SQLSelect = "SELECT Computer_System_DATA.Name00 as ComputerName, DetectionTime, Users.UserName, Process, ThreatName, Path, EP_ThreatSeverities.Severity, EP_ThreatCategories.Category, CleaningAction, ExecutionStatus, ActionSuccess, PendingActions, ErrorCode, RemainingActions, LastRemainingActionsCleanTime FROM $SCCMDB.dbo.EP_Malware INNER JOIN $SCCMDB.dbo.Computer_System_DATA on EP_Malware.MachineID = Computer_System_DATA.MachineID INNER JOIN $SCCMDB.dbo.EP_ThreatCategories on EP_Malware.CategoryID = EP_ThreatCategories.CategoryID INNER JOIN $SCCMDB.dbo.EP_ThreatSeverities on EP_Malware.SeverityID = EP_ThreatSeverities.SeverityID INNER JOIN $SCCMDB.dbo.Users on EP_Malware.UserID = Users.UserID ORDER BY DetectionTime ASC" $SQLCommand.CommandText = ($SQLSelect) $SQLReader = $SQLCommand.ExecuteReader() $SQLResultsTable = New-Object System.Data.DataTable $SQLResultsTable.Load($SQLReader) $SQLReader.Close() $SCCMDBConnection.Close() Write-Host ("Found "+($SQLResultsTable | Measure-Object -Line).Lines+" results") $SQLResultsTable | Export-Csv -Path $OutFile -NoTypeInformation -Force