PowerShell: Get SCCM Malware Detections to CSV file

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
This entry was posted in PowerShell and tagged , , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s