SCCM Client InActive Obsolete Status Using SQL Query | ConfigMgr

Let’s use SQL queries to analyze SCCM Client Inactive Obsolete Status. I keep getting questions about ConfigMgr SQL queries in the HTMD forum.

If you have inactive SCCM clients, adjust the client status settings at the site level. These settings determine when to mark clients as inactive and can be configured to send alerts for low client activity. Afterwards, use the Configuration Manager console to monitor client health and activity.

According to Microsoft, a device is online when connected to its assigned management point and sends regular messages. The client is offline if the management point doesn’t receive notifications for five minutes. A client is active if it has communicated with Configuration Manager in the past seven days and inactive if it hasn’t requested a policy update, sent a heartbeat message, or sent hardware inventory in seven days.

I found a helpful SQL query for identifying ConfigMgr client status based on collections. This query can teach you how to use a CASE statement, declare SCCM collections, and inner join two ConfigMgr views in a SQL query. It’s a great way to learn important SQL query syntaxes that can be useful for working with ConfigMgr and understanding its data sources better.

Patch My PC
[sibwp_form id=2]
Index
SQL Query – SCCM Client InActive Obsolete Status
Results – SCCM Client InActive Obsolete Status
SCCM Client InActive Obsolete Status Using SQL Query | ConfigMgr – Table 1

SQL QuerySCCM Client InActive Obsolete Status

Let’s find out the SCCM client’s Active, Inactive, and obsolete Status. The following steps will help you create a custom report to determine the status of the active, inactive, and obsolete Configuration Manager clients.

  • Open the SQL Management Studio.
  • Click on the New Query button.
  • Select the CM_MEM database from the drop-down menu.
    • MEM is the ConfigMgr site code.
SCCM Client InActive Obsolete Status Using SQL Query | ConfigMgr - Fig.1
SCCM Client InActive Obsolete Status Using SQL Query | ConfigMgr – Fig.1

Copy the following SQL query to find the client status.

  • Click on the Execute button.
Declare @Collection varchar(8)
Set @Collection = 'SMS00001'
select Distinct 
sys.name0 AS 'Device',
CASE WHEN sys.Client0 = 1 THEN 'YES' WHEN sys.Client0 = 0 THEN 'NO' ELSE 'NA' END AS 'ConfigMgr Client',
CASE WHEN sys.active0 = 1 THEN 'YES' WHEN sys.active0 = 0 THEN 'NO' ELSE 'NA' END AS 'Active Client',
CASE WHEN sys.Obsolete0 = 1 THEN 'YES' WHEN sys.Obsolete0 = 0 THEN 'NO' ELSE 'NA' END AS 'Obsolete Client'
from  v_r_system Sys
INNER Join v_FullCollectionMembership Col on Sys.ResourceID = Col.ResourceID
where Col.CollectionID = @Collection

Results – SCCM Client InActive Obsolete Status

Let’s see the results of the above client status SQL query:

DeviceConfigMgr ClientActive ClientObsolete Client
ADMEMCMNANANA
CMMEMCMNANANA
CMMEMCMTP01NANANA
PROD-WIN10TESTNANANA
PROD-WIN20YESYESNO
SQLMEMCMNANANA
TPCMMEMCMNANANA
SCCM Client InActive Obsolete Status Using SQL Query | ConfigMgr – Table 1
SCCM Client InActive Obsolete Status Using SQL Query | ConfigMgr - Fig.2
SCCM Client InActive Obsolete Status Using SQL Query | ConfigMgr – Fig.2

Resources

We are on WhatsApp. To get the latest step-by-step guides and news updates, Join our Channel. Click here –HTMD WhatsApp.

Adaptiva

Author

Anoop C Nair is Microsoft MVP! He is a Device Management Admin with more than 20 years of experience (calculation done in 2021) in IT. He is a Blogger, Speaker, and Local User Group HTMD Community leader. His primary focus is Device Management technologies like SCCM 2012, Current Branch, and Intune. He writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.