How to create a collection of computers that has not been inventoried for more than 30 days

 

We had a requirement to get a colletion computer that has not been inventoried for more than  30 days (Software or hardware). Here is the query

WQL query for Last Software Scan

 

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_LastSoftwareScan on SMS_G_System_LastSoftwareScan.ResourceId = SMS_R_System.ResourceId where SMS_G_System_LastSoftwareScan.LastScanDate < DateAdd(dd,-30,GetDate())

 

 

WQL query for Last Hardware Scan

 

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_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_WORKSTATION_STATUS.LastHardwareScan < DateAdd(dd,-30,GetDate())

WQL Query for DDR\Heartbeat (Not send by last 14 days)

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 where SMS_R_System.ResourceId not in (select ResourceID from SMS_R_System where AgentName in ("Heartbeat Discovery") and DATEDIFF(day,AgentTime,GetDate())<=14)

 

This can be used for both SMS and SCCM. The query can be added in the query memebership rules created when creatinga collection. You can click on  Edit query and then going ahead and click on show query design. Cut and paste the query and save. The picture shown below will show you where to paste the query.

 

 

Hope this information is helpful

Sudheesh Narayanaswamy.