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

gerhardub
gerhardub used Ask the Experts™
on
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

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Figured it out for myself...

Swap out the center code for:

# Set the starting row for data writing

$intRow = 2

# 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
$intCol = 2

# 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)

{

      foreach ($KBB in $KBNumber)

      {

      $PatchStatus = Get-HotFix -ErrorVariable Err -Id $KBB -computer $strComputer

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

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

...and it will build an excel sheet color coded, wth a KBB per column, and indicating what the status of the patch is on a specific Windows Server.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial