SCCM. SQL Query for AV Detections. v_gs_threats.

Rather than using the in built SCCM’s reports, I run a customized query to return Anti Virus detection.

SELECT v_R_System.Name0 as Name,Username,DetectionID,DetectionTime,ThreatName,Path,CleaningAction,ActionSuccess
From v_GS_Threats
Inner Join v_R_System
ON v_GS_Threats.ResourceID=v_R_System.ResourceID
WHERE DetectionTime >= DATEADD(DAY,-%days%, GETDATE())

Edit the days value depending on how far back you want to go. I like to run this weekly and in my case, it would be -7.

You can use this if you build your own report in Reporting Services or if you are that way inclined, wrap it in Powershell to send an HTML email.

You can download and install the SQL Powershell module on a non sql server by following the instructions here http://guidestomicrosoft.com/2015/01/13/install-sql-server-powershell-module-sqlps/

Advertisements

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