• Josh

Reports using PowerShell

Most anyone that knows me, knows that I love PowerShell. It is one of the most versatile languages for a Systems Engineer, Consultant and even DevOps Engineers in the weeds everyday. My philosophy is to know what and how my environment is doing at all times. Yes, monitoring tools such as SolarWinds, SCOM, Nagios, ManageEngine OpManager, OP 5, or What's Up Gold are great tools but I often find that my customers alert me to problems before the product does.


I use PowerShell as my primary... get information… let me know what's going on... method. The integration of PowerShell scripts and scheduled tasks can keep me in the know.. and fast. The trick is to get the information in a readable format. That is what we are going to do today...


Step 1: Identify what you want to know and how often

Step 2: Design how you want it to look when you get it... An easy to read format...

Step 3: PowerShell it

Step 4: Deliver it to the person that can make a decision and act on the information.


**Note: In this demo I am using a script I wrote to check servers and send me the updates they are lacking. This script works best for Windows Server 2012 R2 and above.


Here we go:


Step 1 : Identify what you want to know and how often

I want to know which of my servers need updates, if the updates are considered Critical, Important, Moderate, or low, when the update was released, the KB number, and the size of the download.


Step 2: Design how you want it to look when you get it... An easy to read format...

I'd like it in a table format. The name of the server at the top spanned across my data fields. Next I'd like my data field headings of Title, Severity, KB Number, Release Date, and Download Size (MB). Last, I'd like to see those with a Severity of Critical in red, Important in yellow and Moderate in blue. Once it is all put together I want it emailed to me. Yes, this is part of Step 4 but still good to already know.


Step 3: PowerShell it

This is tough part. If we stop and think about it, there are two parts here. The first part is getting the information and the second part is putting it in that readable format. Lets start by designing that readable format. I am going to use HTML and I'm going to break it out so that it is a little easier to read here.


First I need to create my HTML header.

$HTML =

"<html> <head> <meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'> </head> <body> <style type='text/css'> TABLE{border: 2px solid black; border-collapse: collapse; font-size: 10pt; width:auto;} TH{border: 2px solid black; background: #dddddd; padding: 5px; color: #000000;} TD{border: 2px solid black; padding: 5px;} </style>

<h3><center>Servers: Patches Needed Report</center></h3>"


Now that our header is in place, lets get our servers with a quick get-adcomputer command.

$Computernames = Get-adcomputer -filter * -Properties * |?{$_.operatingsystem -like "*Server*"}


We can now reach out to each of those servers and ask it, "What updates are you missing?"

$computernames | %{

$Updates = Invoke-Command -ComputerName $_ -ScriptBlock {(new-object -ComObject microsoft.Update.Session).createUpdateSearcher().search("isInstalled=0").updates | select title,maxdownloadsize,MsrcSeverity,KBArticleIDs,LastDeploymentChangeTime | sort MsrcSeverity -Descending} #-yes there is a } missing but it comes later in the code


OHHH Yeah... Things are coming together now! Lets now add some HTML for our report, do some color coding, and close out the report.


Step 4:

$Body +=

"<TABLE> <TR><TH style='background-color:grey' colspan='5'><center>$Computer</center></TH></TR> <TR><TH>Title</TH><TH>Severity</TH><TH>KB Number</TH><TH>Release Date</TH><TH>Download Size (MB)</TH></TR>"

$Updates | %{ $Title = $_.title $Severity = $_.MsrcSeverity $KBNum = $_.KBArticleIDs $ReleaseDate = $_.LastDeploymentChangeTime.ToShortDateString() $Size = [math]::Round($_.maxdownloadsize/1mb,2) $Body += "<TR><Td>$Title</Td>" switch($Severity){ "Moderate"{$Body += "<Td style='background-color:Blue'>$Severity</Td>"} "Critical"{$Body += "<Td style='background-color:Red'>$Severity</Td>"} "Important"{$Body += "<Td style='background-color:Yellow'>$Severity</Td>"} Default{$Body += "<Td>$Severity</Td>";$L++} } $Body += "<Td>$KBNum</Td><Td>$ReleaseDate</Td><Td>$Size</Td></TR>" } $Body += "</table><br><br>"

}

$Body += "</table></body></html>"


We are almost done... this is where Step 3 and Step 4 merge and we deliver our new report to the person needing it the most... You!


Step 4: Deliver it to the person that can make a decision and act on the information.

$smtpsettings = @{ subject = "Report: Microsoft Patches Missing from Servers" SMTPServer = "<your smtp server>" To = @('<your email address>','<a distribution list>') From = "ITReports@<yourorganization>.com"} $report = $HTML + $Body $report | out-file "C:\Scripts\ServerReports\MissingUpdatesfromServers.htm"

#-Send the email send-mailmessage @smtpsettings -bodyashtml -body $report -Encoding ([System.Text.Encoding]::UTF8) Remove-Variable * -ErrorAction SilentlyContinue


There was a lot to take in here and there is so much more we can do with this report. I hope it helps in some way and inspires you to integrate reporting. Here is a quick glimpse of how my report turned out...

 

PO Box 1157
Florence, MS 39073

©2020 by Arrow Head Consulting LLC.
View our privacy policy here