Check Installed Windows Operating System Patch On Servers Using Powershell

Problem

Do you always patch your Windows Operating System every month?

We do this regularly in our servers. But this task is being done by the server administrator.

In short, for us to know what patches were installed we have to go into each server and verify them.

We should be able to do this using powershell.

The script should ask the user to provide the server name or server list file.

It also should ask the user to enter the patch information they want to search for.

Below are some of the information that I immediately wanted to know after the servers are patched:

When did the Windows OS patching happened?
What patches were installed?
Were the patches successfully installed?
Does the server require a reboot after the patching?
If the latest patch were not installed, when was the last Windows OS patching happened and what patches were last installed?
If the server doesn't have the latest patch, the server name needs to be stands out from the list.

Solution

I will try to explain the sections of the script that I used for each requirements.

I need the user to provide the search string information and the server name and server list.

Recently, Microsoft have started naming their Windows OS Patch with YYYY-MM prefix on the patch name/title/description.

Example title for one of the September patches:

2018-09 Update for Windows Server 2016 (1803) for x64-based Systems (KB4100347).

Now we can just provide the string 2018-09 if we want to know what September patches were installed on the server.

To get this information we need to add a parameter in our script:

PowerShell

Param( 
    [Parameter(Mandatory=$True)] 
    [string]$KBNumber_or_TitleString, 
    [Parameter(Mandatory=$True)] 
    [string]$ServerName_or_ServerList 
)

I also added the Mandatory argument to force the user to provide this information.

Now lets see how we can get the other script requirements:

1. When did the Windows OS patching happened?
2. What patches were installed?
3. Were the patches successfully installed?

I was able to find the example to get all these three information from this URL:

http://community.idera.com/powershell/ask_the_experts/f/learn_powershell_from_don_jones-24/18493/last-windows-update-except-definitionupdate-for-microsoft-endpoint-protection

The ResultCode to know if the patches were installed successfully is being returned as numbers.
I have to transpose them to human readable values in the script below.

PowerShell

$Session = New-Object -ComObject Microsoft.Update.Session 
$Searcher = $Session.CreateUpdateSearcher() 
$HistoryCount = $Searcher.GetTotalHistoryCount() 
if ($HistoryCount -gt 0) 
{ 
    $Searcher.QueryHistory(0,$HistoryCount) | ForEach-Object -Process { 
    $Title = $null 
    $Title = $_.Title 
    $Result = $null 
    Switch ($_.ResultCode) 
    { 
        0 { $Result = 'NotStarted'} 
        1 { $Result = 'InProgress' } 
        2 { $Result = 'Succeeded' } 
        3 { $Result = 'SucceededWithErrors' } 
        4 { $Result = 'Failed' } 
        5 { $Result = 'Aborted' } 
        default { $Result = $_ } 
    } 
}

I also found out that not all the Windows Updates are stored on Microsoft.Update.Session.

Some of the Windows Patch information are installed as Hotfix.

I have to use the powershell Get-Hofix command to get the Hotfix information.

PowerShell

Get-Hotfix | Sort-Object InstalledOn,HotFixID -Descending

4. Does the server require a reboot after the patching?

When a patch needs a reboot, its ResultCode value will be 1 (In Progress) until the machine is rebooted.

This can be confirmed and verified by querying the Windows Registry Settings.

There are 3 different settings that will indicate that reboot is required but not all of them exists in the registry.

This is the reason why I used -ErrorAction Ignore in the script below.

PowerShell

$PendingRebootStatus = $null 
if ($Result -eq 'InProgress') 
{ 
    if (Get-ChildItem "HKLM:\Software\Microsoft\Windows\CurrentVersion\Component Based Servicing\RebootPending" -EA Ignore) { $PendingRebootStatus=$true } 
    if (Get-Item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired" -EA Ignore) { $PendingRebootStatus=$true } 
    if (Get-ItemProperty "HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager" -Name PendingFileRenameOperations -EA Ignore) { $PendingRebootStatus=$true } 
    try {  
        $util = [wmiclass]"\\.\root\ccm\clientsdk:CCM_ClientUtilities" 
        $status = $util.DetermineIfRebootPending() 
        if(($status -ne $null) -and $status.RebootPending) 
        { 
            $PendingRebootStatus = $true 
        } 
        } 
    catch{} 
} 
 
if ($PendingRebootStatus -eq 'True') 
{ 
    $Result = 'Pending restart' 
}

5. If the latest patch were not installed, when was the last Windows OS patching happened and what patches were last installed?

6. If the server doesn't have the latest patch, the server name needs to be stands out from the list.

These 2 requirements will have the same process as requirements 1,2, and 3. But this just needs to return the last Windows OS patching information.

For the servers to be easily identifiable, I placed an * (astirisk) in front of the server name when the report gets generated.

It is now time to merge them all together in one script.

NOTE: If you are interested in making it a GUI based script you can refer to my previous posts.

- Additonal information that can be added are cluster status and Operating Systems version.

UPDATE: Technet Gallery has been closed by Microsoft, you can view the archived version on Technet Gallery Archive

PowerShell

Param( 
    [Parameter(Mandatory=$True)] 
    [string]$KBNumber_or_TitleString, 
    [Parameter(Mandatory=$True)] 
    [string]$ServerName_or_ServerList 
) 
 
clear 
 
'Microsoft Windows Server Patch Number or Part of Patch Title String: ' + $KBNumber_or_TitleString  
 
if (($ServerName_or_ServerList.Contains("\") -eq $True) -or ($ServerName_or_ServerList.Contains(".") -eq $True)) 
#-eq $True) -or (($ServerName_or_ServerList.Contains(".") -eq $True) 
{ 
    $serverlist = get-content "$ServerName_or_ServerList" 
} 
else 
{ 
    $serverlist = $ServerName_or_ServerList 
} 
 
$KBs = @() 
 
foreach ($svr in $serverlist) 
{ 
    $svr = $svr.Trim() 
    $AllServerUpdates = $null 
 
    $AllServerUpdates = Invoke-Command -ComputerName $svr -ScriptBlock { 
        $KBNum = $args[0] 
        $Session = New-Object -ComObject Microsoft.Update.Session 
        $Searcher = $Session.CreateUpdateSearcher() 
        $HistoryCount = $Searcher.GetTotalHistoryCount() 
        if ($HistoryCount -gt 0) 
        { 
            $Searcher.QueryHistory(0,$HistoryCount) | ForEach-Object -Process { 
                $Title = $null 
                $Title = $_.Title 
                $Result = $null 
                Switch ($_.ResultCode) 
                { 
                    0 { $Result = 'NotStarted'} 
                    1 { $Result = 'InProgress' } 
                    2 { $Result = 'Succeeded' } 
                    3 { $Result = 'SucceededWithErrors' } 
                    4 { $Result = 'Failed' } 
                    5 { $Result = 'Aborted' } 
                    default { $Result = $_ } 
                } 
 
                $PendingRebootStatus = $null 
                if ($Result -eq 'InProgress') 
                { 
                    if (Get-ChildItem "HKLM:\Software\Microsoft\Windows\CurrentVersion\Component Based Servicing\RebootPending" -EA Ignore) { $PendingRebootStatus=$true } 
                    if (Get-Item "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\Auto Update\RebootRequired" -EA Ignore) { $PendingRebootStatus=$true } 
                    if (Get-ItemProperty "HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager" -Name PendingFileRenameOperations -EA Ignore) { $PendingRebootStatus=$true } 
                    try {  
                            $util = [wmiclass]"\\.\root\ccm\clientsdk:CCM_ClientUtilities" 
                            $status = $util.DetermineIfRebootPending() 
                            if(($status -ne $null) -and $status.RebootPending) 
                            { 
                                $PendingRebootStatus = $true 
                            } 
                        } 
                    catch{} 
                } 
 
                if ($PendingRebootStatus -eq 'True') 
                { 
                    $Result = 'Pending restart' 
                }  
 
                $details = New-Object PSObject  
                $details | Add-Member -MemberType NoteProperty -Name Date -Value $_.Date 
                $details | Add-Member -MemberType NoteProperty -Name Title -Value $Title 
                $details | Add-Member -MemberType NoteProperty -Name Status -Value $Result 
                $details  
            } | Sort-Object -Descending:$true -Property Date  
        } else { 
            Get-Hotfix | Sort-Object InstalledOn,HotFixID -Descending | ForEach-Object -Process { 
                $Title = $null 
                $Title = $_.Description + ' - ' + $_.HotFixID 
                $Result = $null 
                $Result = 'No Status when using Get-Hotfix' 
 
                $details = New-Object PSObject  
                $details | Add-Member -MemberType NoteProperty -Name Date -Value $_.InstalledOn 
                $details | Add-Member -MemberType NoteProperty -Name Title -Value $Title 
                $details | Add-Member -MemberType NoteProperty -Name Status -Value $Result 
                $details  
            } 
        }  
 
        #Select-Object -Property * -ExcludeProperty Name | Format-Table -AutoSize -Wrap 
        $details | Where-Object {$_.PSComputerName -ne $null} | Select-Object -Property Date, Status, Title #| Format-List | Out-String).Trim()  
    #} -ArgumentList $KBNumber_or_TitleString 
    } -ErrorAction SilentlyContinue 
 
    #$AllServerUpdates.Count  
 
    #$AllServerUpdates | Where-Object {$_.Title -like "*$KBNumber_or_TitleString*"} 
 
    $ServerUpdates = $AllServerUpdates | Where-Object {$_.Title -like "*$KBNumber_or_TitleString*"} 
 
    if ($AllServerUpdates -eq $null) 
    { 
        $KB = New-Object -TypeName PSObject  
        $KB | Add-Member -MemberType NoteProperty -Name ServerName -Value *$svr 
        $KB | Add-Member -MemberType NoteProperty -Name PatchStatus -Value 'Cannot Access Server' 
        $KB | Add-Member -MemberType NoteProperty -Name PatchDate -Value '-----' 
        $KB | Add-Member -MemberType NoteProperty -Name PatchName -Value '-----' 
        $KBs += $KB 
    }  
 
    if ($ServerUpdates -ne $null) 
    { 
        $counter = 0 
        foreach ($ServerUpdate in $ServerUpdates) 
        { 
            $KB = New-Object -TypeName PSObject  
             
            if ($counter -eq 0) 
            { 
                $KB | Add-Member -MemberType NoteProperty -Name ServerName -Value $svr 
            } else { 
                $KB | Add-Member -MemberType NoteProperty -Name ServerName -Value '' 
            } 
            $counter++ 
 
            $KB | Add-Member -MemberType NoteProperty -Name PatchStatus -Value $ServerUpdate.Status 
            $KB | Add-Member -MemberType NoteProperty -Name PatchDate -Value $ServerUpdate.Date  
            $KB | Add-Member -MemberType NoteProperty -Name PatchName -Value $ServerUpdate.Title 
            $KBs += $KB 
        } 
    } else { 
         
        $LatestServerUpdateDate = $AllServerUpdates | Select-Object -First 1 Date | Get-Date -Hour 0 -Minute 0 -Second 0 
        #$LatestServerUpdateDate 
 
        $ServerUpdates = $AllServerUpdates | Where-Object {$_.Date -ge $LatestServerUpdateDate} 
        #$ServerUpdates.Count 
        #$ServerUpdates 
 
        $counter = 0 
        foreach ($ServerUpdate in $ServerUpdates) 
        { 
            $KB = New-Object -TypeName PSObject  
            if ($counter -eq 0) 
            { 
                $KB | Add-Member -MemberType NoteProperty -Name ServerName -Value *$svr 
            } else { 
                $KB | Add-Member -MemberType NoteProperty -Name ServerName -Value '' 
            } 
            $counter++ 
 
            $KB | Add-Member -MemberType NoteProperty -Name PatchStatus -Value $ServerUpdate.Status 
            $KB | Add-Member -MemberType NoteProperty -Name PatchDate -Value $ServerUpdate.Date  
            $KB | Add-Member -MemberType NoteProperty -Name PatchName -Value $ServerUpdate.Title 
            $KBs += $KB 
        } 
    } 
 
} 
 
$KBs | ft -AutoSize -Wrap 
 
read-host “Press ENTER to continue...”

Problem

My previous post talked about how to check the version of SSMS that is installed on different machines remotely.

Now we need to find a way to update/upgrade older versions of SSMS remotely based on the list that was generated by our software version checker.

Solution

I created a GUI powershell script and made it executable.

Below is my thought process for making this script:

Step 1

How do I install the SSMS upgrade silently? I ran the executable with /? for help it showed this screen:

I tried the following command to test the SSMS Silent Install:

C:\Temp\SSMS-Setup-ENU-Upgrade.exe /install /quiet /passive /norestart

But there is no option to run it remotely. This needs to run on the machine that I need to upgrade.

Step 2

Copy the installer to the machine that you need to install. The only common place where I can copy the file on the machine is c:\temp directory.

Using powershell, I created a simple script to copy a file from a source path to the server's c:\temp folder:
PS> Copy-Item "<source path>\SSMS-Setup-ENU-Upgrade.exe" -Destination "\\<servername>\C$\temp\SSMS-Setup-ENU-Upgrade.exe" -Force

Step 3

Run the executable remotely. This is where I had spent most of my time troubleshooting. I tried running an Invoke-Command to run SSMS-Setup-ENU-Upgrade.exe with silent install parameters and I got security/permissions error.

As a work around, you can execute a powershell script remotely, and that powershell script can run/call the executable on the server.

Since I know that the executable file will always be in the server's c:\temp folder, I created an update_ssms.ps1 powershell script with the following command:

$ssms_path = "c:\temp\SSMS-Setup-ENU-Upgrade.exe"

$ssms_arguments = "/install /quiet /passive /norestart"
Start-Process -FilePath $ssms_path -ArgumentList $ssms_arguments -NoNewWindow

I used the process of copying the update_ssms.ps1 from a source path to the server's c:\temp folder:

PS> Copy-Item "<source path>\update_ssms.ps1" -Destination "\\<servername>\C$\temp\update_ssms.ps1" -Force

The following files are now on the server's c:\temp folder:

SSMS-Setup-ENU-Upgrade.exe
update_ssms.ps1

Now we can run the update_ssms.ps1 and bypass security/permissions by executing the following command:
PS> Invoke-Command -ComputerName <servername> -ScriptBlock {powershell.exe -ExecutionPolicy ByPass -File c:\temp\update_ssms.ps1}

Step 4

Now we can put them all together and be able to upgrade a machine remotely.

But, I still have a few more requirements:

I want to have the ability to upgrade more than one server in a single run execution.
I also don't like running the powershell script in a command line and provide several parameters for the script to work.
I wanted the script to be executable
The update_ssms.ps1 should be in the same folder as the executable powershell script.

I create a GUI screen for wherein the user will provide the following:

Name of server or servers. separated by comma (,) if more than one
Or the path of the server list in .txt file.
The path of SSMS-Setup-ENU-Upgrade.exe

I used PS2EXE-GUI to compile my powershell script into executable.

The executable file can be downloaded from this URL:
https://gallery.technet.microsoft.com/scriptcenter/Updating-SSMS-v17x-62e5af20

UPDATE: Technet Gallery has been closed by Microsoft, you can view the archived version on Technet Gallery Archive

Previous post: Installed SSMS Version Checker...

SQLingRAT

Pages

Check Installed Windows Operating System Patch On Servers Using Powershell

Problem

Solution

Updating SSMS v17.x Remotely On Server/s

Problem

Solution

Step 1

Step 2

Step 3

Step 4

Installed SSMS Version Checker

Problem

Solution