Create SCCM Query To Show Days Since Last Communication

In this article I will show you how to create a sccm query to show days since last communication of a client.

I have found this query to be the most accurate to detecting SCCM clients that are showing as offline in the SCCM console.

This query looks at the days since last communication, heart beat discovery and clients showing offline / inactive. Below I will show you how to create a device collection using a query rule to populate the collection.

The Query

Below we are going to use a WQL Query in sccm to get the machines that have been offline for 14 days or longer.  This query can be used in device collections, Reports or queries from the configuration manager console.

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”))

Creating The Device Collection

I find the easiest way to see which machines have been offline for 14 days or more is to use a device collection.

In the below example I am going to create an SCCM device collection using a query to show 14 days since last communication with the SCCM server.

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

1. Open the configuration manager console
2. Go to \Assets and Compliance\Overview\Device Collections and click “Create Device Collection” in the tool bar
   
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
   
4. Click Add rule and then select query
   
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”))

   


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”

Changing The SCCM Query To Show ** Days

The query above will show sccm clients showing offline for 14 days, if you want to change 14 days to another number all you need to do is change the number 14 to the amount of days you require.

28 Days

Below I have change the query to show last online time of 28 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()) >=28) 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()) >=28) and AgentName = “Heartbeat Discovery”))

60 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()) >=60) 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()) >=60) and AgentName = “Heartbeat Discovery”))