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.

 

 

Comments (6)

  1. Anonymous says:

    Is there a way wherein we can create a collection of disabled computers in SCCM 2012

  2. Suneetha Suthari says:

    Hi Sudheesh.Really very Good Collection of blogs you had posted.All r very useful to us.

  3. Saul says:

    Man your such a savier!!! thanks a lot!

  4. Fadi says:

    Is there a way to query AgentTime value for DDR Heartbeat on its own, and not including the other discovery times (through WQL of course)?

  5. Hamou says:

    Great & useful queries, work like a charm.
    Many thanks.

  6. Baba says:

    Thaaaaaaaaaaanks a lot. you just save my ass from been kicked. great tool