I'm trying to write a powershell script that queries several servers and checks to see if they have any pending updates.
Basically I'm using the "MSCatalog" module to query Updates like so:
$Search = (Get-Date -Format "yyyy-MM")
$Updates = (Get-MSCatalogUpdate -Search $Search -AllPages ) | Where-Object { ($_.Title -like "*Server*") -OR ($_.Products -like "*Server*") }
I'm mostly interested in new Monthly updates so I just use get date to query for this months updates. And of course I filter out by Server being in the title or the products list.
Now my servers are all across the board. We have some legacy servers 2008,2012,2012R2, 2016, and 2019.
I'm trying to figure out a good way to check and see if any updates are missing. To get current server updates I run the below command:
$ServerKBs = (((Get-HotFix -ComputerName $($FilteredServerResult.ServerName)) | Select-Object HotFixID,InstalledOn) | Where-Object {($_.InstalledOn -like "$ServerUpdateSearch" )}).HotFixID
The part I'm stuck on is I'm trying to compare only the updates that are relevant to the server. For eample I don't care about 2019 updates for a 2016 or 2012r2 server. I only want the ones specific to that server to show up. Now I understand I can filter it by the column name and compare it to the OS on the server to make sure it matches. For example to get the numeric version of the OS I can use this:
$ServerOSString = (($FilteredServerResult.OS) -replace '\D+(\d+)\D+','$1')
The output would be for example 2016 instead of "Windows Server 2016 Standard" Then I can do something like this:
if(($Update.Products -match $ServerOSString) -eq $True )
{
#Do Stuff
}
This is the part I'm stuck on, because let's say this all aligns and now I compare the KB's installed on the server with what I queried from Microsoft Windows Catalog... There could be some updates that match up and others that may not match up. I'd like to only focus on the ones that have pending updates that don't match with what he server has. How can I go about doing something like this?
I'm using poshrs-job to run this on several servers so it's faster, so I'd like to accomplish this as efficiently as possible and hopefully output a simple list of servers that still need to be patched with the KB &\or title.
Here is an example of what I'm trying to do:
CLS
#Main Variables
#The Search will always be the year-month when you run the script
$Search = (Get-Date -Format "yyyy-MM")
#Formatting is different one servers than on Windows update catalog
$ServerUpdateSearch = (Get-Date -Format "*MM*yyyy*")
#------------- MSCatalog (For Querying Windows Catalog)
if((Get-Module -ListAvailable -Name "MSCatalog") -or (Get-Module -Name "MSCatalog"))
{
Import-Module MSCatalog
}
else
{
Install-Module -Name MSCatalog -Scope CurrentUser -Force -Confirm:$False
Import-Module MSCatalog
}
#------------- PoshRSJob (multitasking)
if((Get-Module -ListAvailable -Name "PoshRSJob") -or (Get-Module -Name "PoshRSJob"))
{
Import-Module PoshRSJob
}
else
{
Install-Module -Name PoshRSJob -Scope CurrentUser -Force -Confirm:$False
Import-Module PoshRSJob
}
#------------- ImportExcel Module
if((Get-Module -ListAvailable -Name "ImportExcel") -or (Get-Module -Name "ImportExcel"))
{
Import-Module ImportExcel
}
else
{
#Install NuGet (Prerequisite) first
Install-PackageProvider -Name NuGet -Scope CurrentUser -Force -Confirm:$False
Install-Module -Name ImportExcel -Scope CurrentUser -Force -Confirm:$False
Import-Module ImportExcel
}
#Clear screen again
CLS
#----------------------------------------------------------------------------------------------------------------
#Start Timestamp
$Start = Get-Date
#Global Variables
$Path = (Split-Path $script:MyInvocation.MyCommand.Path)
$ErrorFile = (Split-Path $script:MyInvocation.MyCommand.Path) + "\ERROR.csv"
#------------------------------------ Setup Excel Variables
#The file we will be reading from
$ExcelFile = (Get-ChildItem -Path "$Path\*.xlsx").FullName
#Worksheet we are working on (by default this is the 1st tab)
$worksheet = (((New-Object -TypeName OfficeOpenXml.ExcelPackage -ArgumentList (New-Object -TypeName System.IO.FileStream -ArgumentList $ExcelFile,'Open','Read','ReadWrite')).Workbook).Worksheets[1]).Name
$ExcelServers = Import-Excel -Path $ExcelFile -WorkSheetname $worksheet -StartRow 1
#------------------------------------ Populate our variable with data from spreadsheet
$ExcelServersList = foreach($ExcelServer in $ExcelServers) {
$ExcelServer | Select-Object @{Name="ServerName";Expression={$_.Child}}, "Primary", @{Name="PatchWindow";Expression={$_."Patch Window"}}, @{Name="TestServer";Expression={$_."Test Server"}}, "DMZ", @{Name="OS";Expression={$_."Operating System"}}
}
#------------------------------------ Remove Duplicate entries
$SortedExcelServersList = ($ExcelServersList | Sort-Object -Property ServerName -Unique)
#------------------------------------ Seperate Servers from DMZ Servers
$FilteredServers = ForEach($SortedExcelServerList in $SortedExcelServersList) {
if($($SortedExcelServerList.DMZ) -eq $true)
{
$SortedExcelServerList.ServerName = [System.String]::Concat("$($SortedExcelServerList.ServerName)",".DMZ.com")
}
$SortedExcelServerList
}
#------------------------------------ Grab all servers from AD so we can use to compare against our list - also trimany whitespaces from output
$Servers = (dsquery * -filter "(&(objectClass=Computer)(objectCategory=Computer)(!(userAccountControl:1.2.840.113556.1.4.803:=2))(operatingSystem=*Server*))" -limit 0 -attr Name | sort).trim()
#------------------------------------ Compare our list to servers in AD and filter out appliances
$FilteredServersResult = $Null
$FilteredServersResult = ForEach ($Item in $FilteredServers)
{
If (($item.servername -in $Servers) -or ($item.DMZ -eq $True))
{
$Item
}
}
#---------------------------- Perform our search. In this case all Monthly Updates and filter it to only show updates for Servers
$Updates = (Get-MSCatalogUpdate -Search $Search -AllPages ) | Where-Object { ($_.Title -like "*Server*") -OR ($_.Products -like "*Server*") }
#------------------------------------ Multithreading Magic
$FilteredServersResult | Start-RSJob -Throttle 50 -Batch "Test" -ScriptBlock {
Param($Server)
#Ping servers to make sure they're responsive
if($NULL -ne (Get-CimInstance -ClassName Win32_PingStatus -Filter "Address='$($Server.servername)' AND Timeout=100").ResponseTime)
{
Try
{
$ServerKBs = (((Get-HotFix -ComputerName $($Server.servername)) | Select-Object HotFixID,InstalledOn) | Where-Object {($_.InstalledOn -like "$ServerUpdateSearch" )}).HotFixID
foreach($Update in $Updates)
{
If (($Server.OS -like "*2016*") -And $Update.Products -match "*2016*")
{
#Check if there are any missing Updates
#($Server | Add-Member -NotePropertyMembers @{"Missing Updates" = $Update} -PassThru)
}
If (($Server.OS -like "*2019*") -And $Update.Products -match "*2019*")
{
#Check if there are any missing Updates
#($Server | Add-Member -NotePropertyMembers @{"Missing Updates" = $Update} -PassThru)
}
}
}
Catch
{
($Server | Add-Member -NotePropertyMembers @{"Error" = [string]$Error} -PassThru) | Export-Csv -Path $using:ErrorFile -NoTypeInformation -Force -Append
}
#Get list of Updates for servers
}
} | Wait-RSJob -ShowProgress | Receive-RSJob | Export-Csv -Path "$Path\Results.csv" -NoTypeInformation -Force
$End = (Get-Date)
$End - $Start
I was able to achieve this using 3 different modules.
The highlevell steps I took are as follows:
Here is the script I used:
CLS
#------------- MSCatalog (For Querying Windows Catalog)
if((Get-Module -ListAvailable -Name "MSCatalog") -or (Get-Module -Name "MSCatalog"))
{
Import-Module MSCatalog
}
else
{
Install-Module -Name MSCatalog -Scope CurrentUser -Force -Confirm:$False
Import-Module MSCatalog
}
#------------- PoshRSJob (multitasking)
if((Get-Module -ListAvailable -Name "PoshRSJob") -or (Get-Module -Name "PoshRSJob"))
{
Import-Module PoshRSJob
}
else
{
Install-Module -Name PoshRSJob -Scope CurrentUser -Force -Confirm:$False
Import-Module PoshRSJob
}
#------------- ImportExcel Module
if((Get-Module -ListAvailable -Name "ImportExcel") -or (Get-Module -Name "ImportExcel"))
{
Import-Module ImportExcel
}
else
{
#Install NuGet (Prerequisite) first
Install-PackageProvider -Name NuGet -Scope CurrentUser -Force -Confirm:$False
Install-Module -Name ImportExcel -Scope CurrentUser -Force -Confirm:$False
Import-Module ImportExcel
}
#Clear screen again
CLS
#----------------------------------------------------------------------------------------------------------------
#Start Timestamp
$Start = Get-Date
#Global Variables
$Path = (Split-Path $script:MyInvocation.MyCommand.Path)
$ErrorFile = (Split-Path $script:MyInvocation.MyCommand.Path) + "\ERROR.csv"
$ResultsFile = (Split-Path $script:MyInvocation.MyCommand.Path) + "\Results.csv"
#------------------------------------ Setup Excel Variables
#The file we will be reading from
$ExcelFile = (Get-ChildItem -Path "$Path\*.xlsx").FullName
#Worksheet we are working on (by default this is the 1st tab)
$worksheet = (((New-Object -TypeName OfficeOpenXml.ExcelPackage -ArgumentList (New-Object -TypeName System.IO.FileStream -ArgumentList $ExcelFile,'Open','Read','ReadWrite')).Workbook).Worksheets[1]).Name
$ExcelServers = Import-Excel -Path $ExcelFile -WorkSheetname $worksheet -StartRow 1
#------------------------------------ Populate our variable with data from spreadsheet
$ExcelServersList = foreach($ExcelServer in $ExcelServers) {
$ExcelServer | Select-Object @{Name="ServerName";Expression={$_.Child}}, "Primary", @{Name="PatchWindow";Expression={$_."Patch Window"}}, @{Name="TestServer";Expression={$_."Test Server"}}, "DMZ", @{Name="OS";Expression={((($_."Operating System").Replace("Windows ","")) -replace "(\s\S+)$") }}
}
#------------------------------------ Remove Duplicate entries
$SortedExcelServersList = ($ExcelServersList | Sort-Object -Property ServerName -Unique)
#------------------------------------ Seperate Servers from DMZ Servers
$FilteredServers = ForEach($SortedExcelServerList in $SortedExcelServersList) {
if(($($SortedExcelServerList.DMZ) -eq $true) -AND ($($SortedExcelServerList.ServerName) -notlike "*.dmz.com"))
{
$SortedExcelServerList.ServerName = [System.String]::Concat("$($SortedExcelServerList.ServerName)",".dmz.com")
}
$SortedExcelServerList
}
#------------------------------------ Grab all servers from AD so we can use to compare against our list - also trimany whitespaces from output
$Servers = (dsquery * -filter "(&(objectClass=Computer)(objectCategory=Computer)(!(userAccountControl:1.2.840.113556.1.4.803:=2))(operatingSystem=*Server*))" -limit 0 -attr Name | sort).trim()
#------------------------------------ Compare our list to servers in AD and filter out appliances
$FilteredServersResult = ForEach($Item in $FilteredServers) {
If (($item.ServerName -in $Servers) -or ($item.DMZ -eq $True))
{
$Item
}
}
#---------------------------- Perform our search. In this case all Monthly Updates and filter it to only show updates for Servers
$Search = (Get-Date -Format "yyyy-MM")
$Updates = (Get-MSCatalogUpdate -Search $Search -AllPages -ErrorAction Stop)
$WinCatalog = foreach($Update in $Updates) {
$Update | Where { (($_.Title -like "*Server*") -OR ($_.Products -like "*Server*")) -AND (($_.Classification -eq "Critical Updates") -OR ($_.Classification -eq "Security Updates")) } | Select-Object @{Name="Title";Expression={([regex]::Matches(($($_).Title), '(?<=\().+?(?=\))')).Value}}, @{Name="Products";Expression={(($_."Products").Replace("Windows Server ",""))}}, "Classification", "LastUpdated", "Size"
}
#------------------------------------ Multithreading Magic
$DMZAccount = Get-Credential ((whoami).replace("domain","dmz.com"))
$FilteredServersResult | Start-RSJob -Throttle $($FilteredServersResult.Count) -Batch "Test" -ErrorAction Stop -ScriptBlock {
Param($Server)
$Check = $False
#------------------------------------ Ping servers to make sure they're responsive
Try
{
if($NULL -ne (Get-CimInstance -ClassName Win32_PingStatus -Filter "Address='$($Server.servername)' AND Timeout=100" -ErrorAction Stop).ResponseTime)
{
#------------------------------------ Get Server KB's
$NeedsUpdates = $ServerKBs = $NULL
Try
{
[ScriptBlock]$SB = {
$DateSearch = (Get-Date -Format "*MM*yyyy*")
$updateSession = New-Object -ComObject Microsoft.Update.Session
$updateSearch = $updateSession.CreateUpdateSearcher()
$updateCount = $updateSearch.GetTotalHistoryCount()
$ServerUpdates = ($updateSearch.QueryHistory(0,$updateCount) | Select Date,Title,Description | Where-Object { ($PSItem.Title) -AND ($_.Date -like $DateSearch) })
([regex]::Matches(($($ServerUpdates).Title), '(?<=\().+?(?=\))')).Value
}
if($Server.DMZ -eq $TRUE)
{
$ServerKBs = Invoke-Command -ComputerName $($Server.servername) -Credential $using:DMZAccount -ErrorAction Stop -ScriptBlock $SB | where {$_ -like "KB*"}
$Check = $True
}
else
{
$ServerKBs = Invoke-Command -ComputerName $($Server.servername) -ErrorAction Stop -ScriptBlock $SB | where {$_ -like "KB*"}
$Check = $True
}
}
Catch
{
if($Check -eq $False)
{
Try
{
$DateSearch = (Get-Date -Format "*MM*yyyy*")
$ServerKBs = (((Get-HotFix -ComputerName $($Server.servername) -ErrorAction Stop) | Select-Object HotFixID,InstalledOn) | Where {($_.InstalledOn -like "$DateSearch" )}).HotFixID
$Check = $True
}
Catch
{
($Server | Add-Member -NotePropertyMembers @{"Error" = [string]$Error} -Force -PassThru) | Export-Csv -Path $using:ErrorFile -NoTypeInformation -Force -Append
}
}
}
#---------------------------- Compare Updates on server to WIndows Update Catalog to determine which updates are missing
$NeedsUpdates = foreach($item in $using:WinCatalog) {
#Match up the Update for the OS of the server
if($item.Products -eq $($Server.OS))
{
#Now check if the update is missing
if(($NULL -eq $ServerKBs) -OR ($item.Title -notin $ServerKBs))
{
$item.Title
}
}
}
if($NULL -ne $NeedsUpdates)
{
($Server | Add-Member -NotePropertyMembers @{"KBs" = (@($($NeedsUpdates)) -join ', ')} -PassThru)
}
}
}
Catch
{
($Server | Add-Member -NotePropertyMembers @{"Error" = [string]$Error} -Force -PassThru) | Export-Csv -Path $using:ErrorFile -NoTypeInformation -Force -Append
}
} | Wait-RSJob -ShowProgress -Timeout 30 | Receive-RSJob | Export-Csv -Path $ResultsFile -NoTypeInformation -Force
$End = (Get-Date)
$End - $Start