How To Create A Device Collection To Show Offline Machines Since 14 Days

clients not online last 14 days sccmIn this article I will show you how you can create a device collection in SCCM to show you machines  that have been offline since 14 days or longer.

You can use this device collection for many things, I use it mostly for when I do software updates + application deployment reports.  I use the Show Offline Machines Since 14 Days device collection to exclude these machines from the report.

How To Create A Device Collection To Show Offline Machines Since 14 Days

To create a device collection in SCCM which will show you Offline Machines Since 14 Days follow these steps

  1. Open the SCCM console
  2. Go to \Assets and Compliance\Overview\Device Collections and click “Create Device Collection” in the tool bar
    Create Device Collection
  3. Give the device collection a name such as “Windows 10 Machines Offline Machines Since 14 Days” then select the limiting collection you need. In the example below I have selected “All Windows 10 Machines” This means only windows 10 machines will show in this collection. Click next
    device collection
  4. Click Add rule and then select query
    query sccm rule
  5. Give the rule a name and click “Edit Query Statement”
  6. Click “show query language” and paste the below text in to the query statement window
    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.Name in ( select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=14) and AgentName = “SMS_AD_SYSTEM_DISCOVERY_AGENT”) ) and SMS_R_System.Name in ( select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=14) and AgentName = “Heartbeat Discovery”))
    sccm collection query
  7. Click ok
  8. Click ok
  9. Click next
  10. Click next
  11. Click close
  12. Right click on the created device collection and select “Update Membership”
  13. Wait 1 minute
  14. Right click on the created device collection and select “Refresh”
  15. You should now see how many windows 10 machines have been offline for 14 days or longer. On the SCCM system I manage we have 507 machines
  16. Right click on the created device collection and select “View Members”

Frequently Asked Questions

Question : I want to change the query to show XX amount of days instead of 14 days.

Answer : This is easily done by changing the query from

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.Name in ( select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=14) and AgentName = “SMS_AD_SYSTEM_DISCOVERY_AGENT”) ) and SMS_R_System.Name in ( select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=14) and AgentName = “Heartbeat Discovery”))

replace 14 with the amount of days you need, in the example below I changed to 21 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.Name in ( select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=21) and AgentName = “SMS_AD_SYSTEM_DISCOVERY_AGENT”) ) and SMS_R_System.Name in ( select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=14) and AgentName = “Heartbeat Discovery”))

 

f

Add a Comment

Your email address will not be published.

Left Menu Icon