Check-CMForUpdateStatus.ps1

25 April 2023
Sean Huggans
Description:
Solution I came up with to check whether or not a ConfigMgr site is currently running an upgrade (needed a way to let some automation know to skip actions vs. trying and failing or even causing issues). This is very much a scratch solution with various things broken out/returning as an example, so adapt as needed!
Language:
PowerShell
Usage Type:
Standalone
Script Source:
############################################
# Check-CMForUpdateStatus.ps1
# Author(s): Sean Huggans
############################################
# Scratch Project for checking upgrade status of a
# ConfigMgr Site.
############################################
 
$global:ConfigMgrSQLServer = "Server01.Network.net"
$global:ConfigMgrSMSProviderServer = "Server01.Network.net"
$global:ConfigMgrSiteCode = "FOO"
 
Function Log-Action ($Message, $LogName) {
    #$Message
}
 
Function Save-LogLine ($Message) {
    $Message | Out-File -FilePath "D:\Temp\SiteStatusTrackingTest2.log" -Append -Force
    Write-Host $Message
}
 
function Invoke-SQL
{
	param (
		[string]$dataSource,
		[string]$database,
		[string]$sqlCommand = $(throw "Please specify a query.")
	)
	if (Test-Connection -ComputerName $dataSource -Count 2 -Quiet)
	{
		$connectionString = "Data Source=$dataSource; " +
		"Integrated Security=SSPI; " +
		"Encrypt=True;" +
		"TrustServerCertificate=True;" +
		"Initial Catalog=$database"
 
		$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
		$command = new-object system.data.sqlclient.sqlcommand($sqlCommand, $connection)
		Try
		{
			$connection.Open()
 
			$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
			$dataset = New-Object System.Data.DataSet
			$adapter.Fill($dataSet) | Out-Null
 
			$connection.Close()
 
			if ($LogLevel -lt 1)
			{
				Log-Action -Message "Database ""$($database)"" on host ""$($dataSource)"" was successfully queried." -LogName $global:SQLLogFile
			}
			else
			{
				Log-Action -Message "Database ""$($database)"" on host ""$($dataSource)"" was successfully queried.  Log level higher than 0 (debug logging): $($sqlCommand)" -LogName $global:SQLLogFile
			}
			$Result = $dataSet.Tables
		}
		catch
		{
			$ErrorMessage = $PSItem.ToString()
			switch -Wildcard ($ErrorMessage)
			{
				"*Login failed for user*" {
					$ErrorParts = $ErrorMessage.split("`n")
					$UserName = $ErrorParts[1].Split("'")[1].Replace("'", "")
 
					$QueryError = "User ""$($UserName)"" failed to login to the database."
				}
				default {
					$QueryError = "Unhandled Error: $($ErrorMessage)"
				}
			}
			$Result = "Error: A query to database ""$($database)"" on host ""$($dataSource)"" failed."
			Log-Action -Message $Result -LogName $global:SQLLogFile
			if ($LogLevel -gt 0)
			{
				Log-Action -Message " - Log level higher than 0 (debug logging): $($QueryError)" -LogName $global:SQLLogFile
			}
		}
	}
	else
	{
		$Result = "Error: A query to the Database ""$($database)"" on host ""$($dataSource)"" was not attempted: $($dataSource) is unreachable."
		Log-Action -Message $Result -LogName $global:SQLLogFile
	}
	return $Result
}
 
function Check-CMForInstallingUpdates {
    # Create a variable to track current update statuses (We will set true if ANY of the updates we loop through have an installation type status)
    $UpdateInstalling = $false
    # Query ConfigMgr DB for currently listed updates
    [array]$CurrentUpdateInfo = Invoke-SQL -dataSource $global:ConfigMgrSQLServer -database "CM_$($global:ConfigMgrSiteCode)" -sqlCommand "SELECT * FROM vSMS_CM_UpdatePackages;"
    # Check if the query returned results
    if (($CurrentUpdateInfo -ne $null) -and ($CurrentUpdateInfo.Count -gt 0)) {
        $CurrentUpdateInfo = $CurrentUpdateInfo | Sort-Object -Property "FullVersion" -Descending
        # Loop through each, check for installation type statuses (https://blog.visuafusion.com/CMSiteUpdateStatusCodes)
        foreach ($CurrentUpdate in $CurrentUpdateInfo) {
            if (($CurrentUpdate.State -eq '196609') -or ($CurrentUpdate.State -eq '2') -or ($CurrentUpdate.State -eq '196611') -or ($CurrentUpdate.State -eq '196614') -or ($CurrentUpdate.State -eq '196615') -or ($CurrentUpdate.State -eq '196616') -or ($CurrentUpdate.State -eq '196617') -or ($CurrentUpdate.State -eq '196618') -or ($CurrentUpdate.State -eq '196619') -or ($CurrentUpdate.State -eq '196620') -or ($CurrentUpdate.State -eq '196621') -or ($CurrentUpdate.State -eq '196622') -or ($CurrentUpdate.State -eq '196623') -or ($CurrentUpdate.State -eq '196624') -or ($CurrentUpdate.State -eq '196625') -or ($CurrentUpdate.State -eq '196626') -or ($CurrentUpdate.State -eq '196627') -or ($CurrentUpdate.State -eq '196628')) {
                # If Update type status is found, set UpdateInstalling variable to true
                $UpdateInstalling = $true
            } else {
                # Do Nothing
            }
        }
    } else {
        # Do Nothing
    }
    return $UpdateInstalling
}
 
function Check-CMForUpdatesStatus ($SiteCode)
{
	# Create a variable to track current update statuses (We will set true if ANY of the updates we loop through have an installation type status)
	$CurrentStatus = 99
	#Ping ConfigMgr SQL Server and Site Server 
	if ((Test-Connection -ComputerName $global:ConfigMgrSQLServer -Count 2 -Quiet) -and (Test-Connection -ComputerName $global:ConfigMgrSMSProviderServer -Count 2 -Quiet))
	{
		$CurrentStatus = 1
		# Query ConfigMgr DB for currently listed updates
		[array]$CurrentUpdateInfo = Invoke-SQL -dataSource $global:ConfigMgrSQLServer -database "CM_$($SiteCode)" -sqlCommand "SELECT * FROM vSMS_CM_UpdatePackages;"
		# Check if the query returned results
		if (($CurrentUpdateInfo -ne $null) -and ($CurrentUpdateInfo.Count -gt 0))
		{
			$CurrentUpdateInfo = $CurrentUpdateInfo | Sort-Object -Property "FullVersion" -Descending
			# Loop through each, check for installation type statuses (https://blog.visuafusion.com/CMSiteUpdateStatusCodes)
			foreach ($CurrentUpdate in $CurrentUpdateInfo)
			{
				if (($CurrentUpdate.State -eq '2') -or ($CurrentUpdate.State -eq '196609') -or ($CurrentUpdate.State -eq '196611') -or ($CurrentUpdate.State -eq '196614') -or ($CurrentUpdate.State -eq '196615') -or ($CurrentUpdate.State -eq '196616') -or ($CurrentUpdate.State -eq '196617') -or ($CurrentUpdate.State -eq '196618') -or ($CurrentUpdate.State -eq '196619') -or ($CurrentUpdate.State -eq '196620') -or ($CurrentUpdate.State -eq '196621') -or ($CurrentUpdate.State -eq '196622') -or ($CurrentUpdate.State -eq '196623') -or ($CurrentUpdate.State -eq '196624') -or ($CurrentUpdate.State -eq '196625') -or ($CurrentUpdate.State -eq '196626') -or ($CurrentUpdate.State -eq '196627') -or ($CurrentUpdate.State -eq '196628') -or ($CurrentUpdate.State -eq '65537') -or ($CurrentUpdate.State -eq '65538'))
				{
					# If Update type status is found, set CurrentStatus variable to 5 (Upgrade in CM_ServerSiteStatusCodes Table)
					$CurrentStatus = 5
                    $CurrentState = "Upgrade ($($CurrentUpdate.State))"
                    Save-LogLine -Message "[$(Get-Date -Format 'yyyy.MM.dd HH:mm:ss')] - ($($CurrentUpdate.FullVersion)) -> $($CurrentState)"
				}
				else
				{
					# Do Nothing
                    $CurrentState = ""
                    # Translations - https://blog.visuafusion.com/CMSiteUpdateStatusCodes
                    Switch ($CurrentUpdate.State) {
                        393213 {
                            $CurrentState = "Hidden"
                        }
                        262146 {
                            $CurrentState = "Not Yet Installed"
                        }
                        196612 {
                            $CurrentState = "Installed"
                        }
                        Default {
                            $CurrentState = "Not Translated"
                        }
                    }
                    $CurrentState = "$($CurrentState) ($($CurrentUpdate.State))"
                    Save-LogLine -Message "[$(Get-Date -Format 'yyyy.MM.dd HH:mm:ss')] - ($($CurrentUpdate.FullVersion)) -> $($CurrentState)"
				}
			}
		}
		else
		{
			# Do Nothing
		}
	}
	else
	{
		# If Unreachable, Set CurrentStatus variable to 99 (Unreachable in CM_ServerSiteStatusCodes Table)
		$CurrentStatus = 99
	}
	return $CurrentStatus
}
 
do {
    $UpdateStatus = ""
    $StatTranslation = ""
    $UpdateStatus = $(Check-CMForUpdatesStatus -SiteCode $global:ConfigMgrSiteCode)
    switch ($UpdateStatus) {
        1 {
            $StatTranslation = "$($UpdateStatus) (Online)"
        }
        2 {
            $StatTranslation = "$($UpdateStatus) (Pending)"
        }
        3 {
            $StatTranslation = "$($UpdateStatus) (Failed)"
        }
        4 {
            $StatTranslation = "$($UpdateStatus) (Deleted)"
        }
        5 {
            $StatTranslation = "$($UpdateStatus) (Upgrade)"
        }
        6 {
            $StatTranslation = "$($UpdateStatus) (Unreachable)"
        }
        Default {
            $StatTranslation = "$($UpdateStatus) (Unknown)"
        }
    }
    Save-LogLine -Message "[$(Get-Date -Format 'yyyy.MM.dd HH:mm:ss')] UpdateStatus ($global:ConfigMgrSiteCode): $($StatTranslation) <---------------------"
    Start-Sleep -Seconds 10
} until ($null)
Note: that all applications posted here are posted for use, both commercial and non-commercial, free of charge, and as such are provided as-is, without warranty of any kind whatsoever. visuaFUSION, FMSCUG or any other program listed here's author are not responsible for any damages or shortcomings that result from usage of any of these applications.
You are here

Check-CMForUpdateStatus.ps1

Error message

Error
