SCCM Client InActive Obsolete Status Using SQL Query | ConfigMgr

0
SCCM Client InActive Obsolete Status Using SQL Query

Let’s help to analyze SCCM client InActive Obsolete Status using SQL query. I keep getting questions about ConfigMgr SQL queries in the HTMD forum. The following SQL query helps you identify ConfigMgr client status based on collections.

I know these details can be found in the collection. However, with this query, you might be able to learn the following SQL query syntaxes:

  • Declare SCCM Collection in a SQL query
  • How to Use a CASE statement in ConfigMgr SQL query
  • How to inner join two ConfigMgr views to build a query

Related post 👉 Monitor ConfigMgr Client Status from SCCM Console | Online | Offline

SQL Query

Let’s find out the SCCM client InActive, Obsolete, and Status. The following steps shall help you to create a custom report to find out 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
SCCM Client InActive Obsolete Status
  • 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
SCCM Client InActive Obsolete Status Using SQL Query |ConfigMgr

Resources

LEAVE A REPLY

Please enter your comment!
Please enter your name here

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