PowerShell Multiple Get-Hotfix Search Code Review / Suggestion Help Request

Ok,

I've modified this code to:

1) Get a list of servers
2) Get a list of MS KBBs/QFEs
3) Write the KB List across an excel spreadsheet

However, I need suggestions / help in expanding my single version of searching for one KBB to searching for a ENTIRE LIST of KBBs applied to a system.

Can anyone come up with a good modification to search for multiple KBBs?

Here's what I've got:

# Use QAD CMD-Let to get list of server and remove the annoying header metadata from array

$ListofComputersTemp = (Get-QADComputer -OSName 'Windows*Server*' | Select-Object name | Sort-Object name )
$ListofComputers = ($ListofComputersTemp | ForEach-Object {$_.name})

$KBAuditFileName = "C:\KBAudit_"+([datetime]::Now).tostring("yyyyMMddhhmmss")+".xlsx"

# Import the list of KB (QFE) numbers we want, and strip out that annoying header associated with the CSV imported object

$KBTemp = Import-CSV "KBList.csv" | Sort-Object KBNumber
$KBNumber = ($KBTemp | ForEach-Object {$_.KBNumber})
$KBCol = 2
#
# We want the script to continue on errors, and NOT report them on the screen
#

#$erroractionpreference = "SilentlyContinue"
#
# Create an Excel Spreadsheet
#

$ExcelDocument = New-Object -comobject Excel.Application
$ExcelDocument.visible = $True 
$ExcelDocument.DisplayAlerts = $False

$ExcelWorkSheet = $ExcelDocument.Workbooks.Add()
$ExcelDocumentComplete = $ExcelWorkSheet.Worksheets.Item(1)

# Create the column headers
# Add a colmn header KB number for each of the KB's being searched for that was read from the KB List File

$ExcelDocumentComplete.Cells.Item(1,1) = "Machine Name"

ForEach ($KBB in $KBNumber)
{
	$ExcelDocumentComplete.Cells.Item(1,$KBCol) = "$KBB"
	$KBCol = $KBCol+1
}	

# Set the colors and fonts

$ExcelDocumentCompleteSetup = $ExcelDocumentComplete.UsedRange
$ExcelDocumentCompleteSetup.Interior.ColorIndex = 19
$ExcelDocumentCompleteSetup.Font.ColorIndex = 11
$ExcelDocumentCompleteSetup.Font.Bold = $True

# Set the starting row for data writing

$intRow = 2
#
# Take the list of computers in this file and for each computer, check for the KB article in $KBNumber
#

# Here we need to change this code to search across the list of KB's that were provided in KB List file.

foreach ($strComputer in $ListofComputers)
{
$ExcelDocumentComplete.Cells.Item($intRow,1) = $strComputer

# Get-Hotfix cmdlet is set up to post the error output in the variable Err.  Each time the cmdlet is run the Err text is updated, and there is text, then the patch was not found (errored).

If ((Test-Connection -Computername $strComputer -quiet) -eq $True)

{
$PatchStatus = Get-HotFix -ErrorVariable Err -Id $KBNumber -computer $strComputer

If($Err -ne $Null)
	{
	$ExcelDocumentComplete.Cells.Item($intRow,2).Interior.ColorIndex = 3
	$ExcelDocumentComplete.Cells.Item($intRow,2) = "NO"
	}
Else
	{
	$ExcelDocumentComplete.Cells.Item($intRow,2).Interior.ColorIndex = 4
	$ExcelDocumentComplete.Cells.Item($intRow,2) = "YES"
	}

$intRow = $intRow + 1
	
}Else 
	{
	$ExcelDocumentComplete.Cells.Item($intRow,2) = "Offline"
	$intRow = $intRow + 1
	}
}
# Reset the excel columns so that they are the proper width.

$ExcelDocumentCompleteSetup.EntireColumn.AutoFit()

# Save Excel document and close Excel

$ExcelWorkSheet.SaveAs("$KBAuditFileName")
$ExcelWorkSheet.Close()
$ExcelDocument.Quit()

# Send email with the spreadsheet

$smtpServer = “<SMTP Server>”

Send-MailMessage -To "<email address>” -Subject "KB Patch Search Status for $KBNumber" -From “<email address>” -Body “Your KB patch search status is attached for $KBNumber" -Priority High -SmtpServer $smtpServer -Attachments $KBAuditFileName

Remove-Item $KBAuditFileName -Force

cls

Select allOpen in new window