ConfigMgr Pending Reboot Report

We wanted a list of servers that are waiting for a reboot. Thankfully, ConfigMgr has a pending restart field that allows admins to see when systems are waiting for a reboot. Our server team wanted that list to be automatically generated and emailed to them daily. Other than myself, others that have the ConfigMgr console rarely look at it since they wear many hats in IT.

I could not find any PowerShell cmdlets in the ConfigMgr module for viewing a pending restart. Thankfully, Eswar Koneti's blog shows the information is stored in sms_combineddeviceresources.

After learning that part, I decided it would be dramatically faster to query the SQL database directly. The table the information resides in is dbo.vSMS_CombinedDeviceResources and is under ClientState. ClientState will have a value of zero if no pending reboot, and a value between 2 and 15 if there is a pending reboot. The list of those values is in the above blog link.

In the PowerShell script below, there are three parameters you will need to populate. The first is $Collection that contains the name of the collection you want to query. $SQLServer is the name of the SQL server. Finally, $SQLDatabase is the name of the ConfigMgr SQL database. You can populate them either at the command line, or hard code the data in the parameter field of the script.

I wrote the script in a way that it can be easily implemented into Azure Automation, SMA, or Orchestrator. The script will output the list of machines waiting for a reboot using the Write-Output and Exit with a return code of 1 if there is nothing to report. The exit code 1 is used with Orchestrator or SMA for the Link between the PowerShell script and the email. The link would not continue to the email task if there were an exit code of 1, as shown below.

NOTE: For this to access the SQL server, the script must be executed on the SQL server, or on a machine that has the SQL console. This is required, so PowerShell has access to the SQL PowerShell module.

You can download this script from my GitHub site.

 <#  
      .SYNOPSIS  
           ConfigMgr Reboot Report  
        
      .DESCRIPTION  
           This script will query ConfigMgr for a list of machines that are waiting for a reboot.  
        
      .PARAMETER Collection  
           Name of the collection to query  
        
      .PARAMETER SQLServer  
           Name of the SQL server  
        
      .PARAMETER SQLDatabase  
           A description of the SQLDatabase parameter.  
        
      .PARAMETER SQLInstance  
           Name of the SQL Database  
        
      .PARAMETER SCCMFQDN  
           Fully Qualified Domain Name of the ConfigMgr server  
        
      .NOTES  
           ===========================================================================  
           Created with:     SAPIEN Technologies, Inc., PowerShell Studio 2017 v5.4.142  
           Created on:       05/01/2020 09:39 AM  
           Created by:       Mick Pletcher  
           Filename:         ConfigMgrRebootReport.ps1  
           ===========================================================================  
 #>  
 [CmdletBinding()]  
 param  
 (  
      [ValidateNotNullOrEmpty()]  
      [string]$Collection = '',  
      [ValidateNotNullOrEmpty()]  
      [string]$SQLServer = '',  
      [ValidateNotNullOrEmpty()]  
      [string]$SQLDatabase = ''  
 )  
   
 $RebootListQuery = 'SELECT * FROM dbo.vSMS_CombinedDeviceResources WHERE ClientState <> 0'  
 $RebootList = (Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query $RebootListQuery).Name | Sort-Object  
 $CollectionQuery = 'SELECT * FROM' + [char]32 + 'dbo.' + ((Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query ('SELECT ResultTableName FROM dbo.v_Collections WHERE CollectionName = ' + [char]39 + $Collection + [char]39)).ResultTableName)  
 $CollectionList = (Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query $CollectionQuery).Name | Sort-Object  
 $List = @()  
 $RebootList | ForEach-Object { If ($_ -in $CollectionList) { $List += $_ } }  
 If ($List -ne '') {  
      Write-Output $List  
 } else {  
      Exit 1  
 }