Config Manager 2012 - Custom Reports Infected systems with Computer Name

Endpoint Protection Infected systems with Computer Name

DECLARE @StartDate datetime

DECLARE @EndDate datetime

SET @StartDate = DATEADD(hour, -336, GETDATE())

SET @EndDate = GETDATE()

select

ComputerName as 'Computer Name',

ComputerStatus as 'EP Remediation Status (1=None, 2=Cleaned)',

EngineVersion as 'Client Definition Version',

ProcessName as 'Process Name',

ThreatName as 'Threat Name',

SeverityID as 'Severity (5=Severe, 4=Moderate)',

ActionSuccess as 'Removed Threat',

PathName as 'Path Name',

MIN(DetectionTime) as FirstDetection,

MAX(DetectionTime) as LastDetection,

COUNT(*) as 'Infection Count'

from

(

select

t.ResourceID,

ISNULL((select top 1 Resource_Names0 from v_RA_System_ResourceNames where

ResourceID=s.ResourceID),Netbios_Name0) as ComputerName,


ai.ComputerStatus,

ah.AntivirusSignatureVersion EngineVersion,

t.Process ProcessName,

t.ThreatName ThreatName,

t.SeverityID SeverityID,

t.ActionSuccess ActionSuccess,

t.Path PathName,

t.DetectionTime

from v_GS_Threats t

join v_R_System s on t.ResourceID=s.ResourceID

join v_FullCollectionMembership c on t.ResourceID=c.ResourceID

left join v_ThreatCatalog tc on t.ThreatID=tc.ThreatID

left join v_GS_AntimalwareHealthStatus ah on t.ResourceID = ah.ResourceID

left join v_GS_AntimalwareInfectionStatus ai on t.ResourceID = ai.ResourceID

where

--c.CollectionID=@CollectionID and

DATEADD(day, 0, DATEDIFF(day, 0, t.DetectionTime)) between @StartDate and @EndDate

) as Infections

group by ResourceID, ComputerName, ComputerStatus,EngineVersion, ProcessName, SeverityID, ActionSuccess,

Threatname, PathName

order by FirstDetection