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
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.
- 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:
|Device||ConfigMgr Client||Active Client||Obsolete Client|