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

  • Article

 

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

  • Anonymous
    January 01, 2003
    Is there a way wherein we can create a collection of disabled computers in SCCM 2012

  • Anonymous
    June 07, 2010
    Hi Sudheesh.Really very Good Collection of blogs you had posted.All r very useful to us.

  • Anonymous
    October 07, 2010
    Man your such a savier!!! thanks a lot!

  • Anonymous
    February 14, 2014
    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)?

  • Anonymous
    February 24, 2015
    Great & useful queries, work like a charm.
    Many thanks.

  • Anonymous
    December 01, 2015
    The comment has been removed