Once a security solution has been implemented you should routinely check how well it’s working and if it’s in fact doing its job. One of our customers have implemented Endpoint Security (Microsoft Configuration Manager).

Jocha manages the platform and we were asked to supply a report with how well Endpoint Security has been implemented and if there are things we could do to improve the setup.

This article gives you the queries for collections, they’re based on the duration since the last Definition Update (signature date).

First of all you will have to create a Device Collection. Set the limiting collection to it All Workstations, All Servers, or another suitable collection.

Built-in you can use three different time scopes to use in collections, 1, 3 and 7 days since the last signature update.

Definitions older than 1 day:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,
SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join
SMS_G_System_EndpointProtectionStatus on SMS_G_System_EndpointProtectionStatus.ResourceID = 
SMS_R_System.ResourceId where SMS_G_System_EndpointProtectionStatus.SignatureUpTo1DayOld = 0

Definitions older than 3 day:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,
SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join 
SMS_G_System_EndpointProtectionStatus on SMS_G_System_EndpointProtectionStatus.ResourceID = 
SMS_R_System.ResourceId where SMS_G_System_EndpointProtectionStatus.SignatureUpTo3DaysOld = 1

Definitions older than 7 day:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,
SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join 
SMS_G_System_EndpointProtectionStatus on SMS_G_System_EndpointProtectionStatus.ResourceID = 
SMS_R_System.ResourceId where SMS_G_System_EndpointProtectionStatus.SignatureUpTo7DaysOld = 1

Let us you know you need any additional information or help.