ConfigMgr Systems Pending Reboot Collections and Queries

A couple builds ago, Microsoft added a cool new column to the ConfigMgr console.  It is really cool but I wanted to more with it.  I wanted pending reboot collections and reports.  Now I want to share what I found and did.

The Pending Reboot information can be found in the ConfigMgr DB as part of the v_CombinedDeviceResources view.  This view has a lot of cool info, but we are interested in ClientState.  ClientState is  what lets us know if there is a reboot pending.

There are five main states:
0 = No reboot Pending
1 = Configuration Manager
2 = File Rename
4 = Windows Update
8 = Add or Remove Feature

Now, if you look at that view, you will notice that there are more than those five listed.  Two of my biggest ClientState values are 12 and 13.  That is because the five are bit flags and your system may have multiple reasons for needing a reboot.

By looking at the message in the console and comparing it to the ClientState value, I determined these additional ClientState values are the various main states added together.

1 – Configuration Manager
2 – File Rename
3 – Configuration Manager, File Rename
4 – Windows Update
5 – Configuration Manager, Windows Update
6 – File Rename, Windows Update
7 – Configuration Manager, File Rename, Windows Update
8 – Add or Remove Feature
9 – Configuration Manager, Add or Remove Feature
10 – File Rename, Add or Remove Feature
11 – Configuration Manager, File Rename, Add or Remove Feature
12 – Windows Update, Add or Remove Feature
13 – Configuration Manager, Windows Update, Add or Remove Feature
14 – File Rename, Windows Update, Add or Remove Feature
15 – Configuration Manager, File Rename, Windows Update, Add or Remove Feature

I can now work on my collection and SQL queries.

This is collection query is for all systems pending reboot.

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 JOIN sms_combineddeviceresources comb ON comb.resourceid = sms_r_system.resourceid WHERE comb.clientstate <> 0

And this SQL query will return all systems pending reboot and the reason

SELECT [Name],
[ClientState],
"Pending Reboot"=
CASE [ClientState]
WHEN '1' THEN 'Configuration Manager'
WHEN '2' THEN 'File Rename'
WHEN '3' THEN 'Configuration Manager, File Rename'
WHEN '4' THEN 'Windows Update'
WHEN '5' THEN 'Configuration Manager, Windows Update'
WHEN '6' THEN 'File Rename, Windows Update'
WHEN '7' THEN 'Configuration Manager, File Rename, Windows Update'
WHEN '8' THEN 'Add or Remove Feature'
WHEN '9' THEN 'Configuration Manager, Add or Remove Feature'
WHEN '10' THEN 'File Rename, Add or Remove Feature'
WHEN '11' THEN 'Configuration Manager, File Rename, Add or Remove Feature'
WHEN '12' THEN 'Windows Update, Add or Remove Feature'
WHEN '13' THEN 'Configuration Manager, Windows Update, Add or Remove Feature'
WHEN '14' THEN 'File Rename, Windows Update, Add or Remove Feature'
WHEN '15' THEN 'Configuration Manager, File Rename, Windows Update, Add or Remove Feature'
ELSE 'Unknown'
END
FROM [v_CombinedDeviceResources]
WHERE ClientState <> '0'
ORDER BY Name

I have uploaded the SQL query and one for counts to https://github.com/NecroMonkey/vault/blob/master/SQL/configmgr_systems_pending_reboot.sql
I have also uploaded a PS script that will create collections based off ClientState to https://github.com/NecroMonkey/vault/blob/master/ConfigMgr-Scripts/configmgr_CB_pendingrebootcolletions.ps1