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.
- 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 = @CollectionResults – SCCM Client InActive Obsolete Status
Let’s see the results of the above client status SQL query:
| Device | ConfigMgr Client | Active Client | Obsolete Client |
|---|---|---|---|
| ADMEMCM | NA | NA | NA |
| CMMEMCM | NA | NA | NA |
| CMMEMCMTP01 | NA | NA | NA |
| PROD-WIN10TEST | NA | NA | NA |
| PROD-WIN20 | YES | YES | NO |
| SQLMEMCM | NA | NA | NA |
| TPCMMEMCM | NA | NA | NA |
Resources
- Monitor ConfigMgr Client Status from SCCM Console | Online | Offline
- ConfigMgr Custom Report for Chrome Browser | SCCM | SQL Query
- Creating custom report models for Configuration Manager in SQL Server Reporting Services.