Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

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

Avatar of gerhardub
gerhardub asked on
VB ScriptPowershellWindows Server 2008
1 Comment1 Solution1257 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Avatar of gerhardub
gerhardub

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 1 Comment.
See Answers