SCCM Query Days Since Last Communication | Show Offline Machines

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.

Creating The Query

I find the easiest way to create the query to see the results is in a device collection.  In the below example I am going to create an SCCM 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 SCCM 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.

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