WSUS–PowerShell Reporting

Hi,

So, since it took me a while to understand that you cannot get scheduled reports from WSUS 3.0, I had a quest to create my own. I searched through SQL database, and WSUS public Views and procedures, but it was time expensive.

Then I stumbled upon PoshWSUS scripts (here). Boeprox created PowerShell scripts, which help to administer WSUS from PowerShell. You can read all about it on his page.

So I needed to create an scheduled report which would periodically send me the status of servers updated by WSUS. And to look nice and shiny, the report will be HTML.

1 #Define HTML Body,table, cells 2 $a = "<style>" 3 $a = $a + "BODY{background-color:white;font-family: Arial; font-size: 8pt;}" 4 $a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;table-layout: fixed; width: 100%;}" 5 $a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:white;}" 6 $a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:white;}" 7 $a = $a + "</style>" 8 $a = $a + "<p style="+'"font-family: Arial;font-size: 10pt;"'+">Table Summary WSUS</p><p style="+'"font-family: Arial; font-size: 10pt;"'+">Admin</p>" 9 10 11 #Sent to admin 12 $to = "admin@company.com" 13 14 #Now Import Posh modul and connect to server 15 Import-module poshwsus 16 Connect-PoshWSUSServer -WsusServer wsusserver.company.com -port 8530 -verbose 17 18 19 <# 20 So, PoshWSUS has couple of scripts, and I foud out I need two, and I need them to combined 21 Get-PoshWSUSUpdateSummaryPerClient 22 and 23 Get-PoshWSUSClient 24 25 First I get status by computer, then mor information about client itself 26 27 #> 28 29 30 $Output = @() #define array to fill 31 $Computers = Get-PoshWSUSUpdateSummaryPerClient 32 33 foreach ($Computer in $Computers) 34 { 35 $computersOstalo = Get-PoshWSUSClient $Computer.Computer 36 $grupaOstalo = Get-PoshWSUSGroup 37 38 #save fields in array 39 $Props = @{ 40 "ComputerName" = $Computer.Computer 41 "IPAddress" = $computersOstalo.IPAddress 42 "OS" = $computersOstalo.OSDescription 43 "Group" = $computersOstalo.ComputerGroup 44 "LastUpdated" = $Computer.LastUpdated 45 "LastSyncTime" = $computersOstalo.LastSyncTime 46 "LastReportedStatusTime" = $computersOstalo.LastReportedStatusTime 47 "NeededCount" = $Computer.NeededCount 48 "FailedCount" = $Computer.FailedCount 49 "InstalledCount"=$Computer.InstalledCount 50 "PendingReboot" = $Computer.InstalledPendingRebootCount 51 } 52 #Save every record to @output array 53 $Output += New-Object PSObject -Property $Props 54 } 55 56 # convert records to HTML 57 $body = $output |Select-Object ComputerName,IPAdresa,OperativniSustav,Grupa,ZadnjeUpdateirano,SinhroniziranoSaWSUS,SinhroniziranoStatusSaWSUS,PotrebnoZakrpa,NeuspjesnihZakrpa,InstaliranoZakrpaZadnjiUpdate,TrebaRestart |Sort-Object Grupa,ComputerName 58 59 $body = $body |ConvertTo-HTML -head $a |Out-String 60 61 #and finayl send mail to admin 62 send-mailmessage -from "WSUS Servers <wsusreports@company.com>" -to $to -subject "Report Server Summary" -bodyAsHtml -body $Body -priority Normal -smtpServer "mailServer"

So just task scheduler this script, and you’ll have a nice report.

Good Luck