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

About: admin


11 thoughts on “WSUS–PowerShell Reporting”

  1. Hi, I am trying this script, somehow i am getting table with dark black background.
    Could you please assist on this

  2. I have checked output for following commands manually Get-PSWSUSUpdateSummaryPerClient
    and Get-PSWSUSClient, its working fine.
    Not sure i am getting black box in emial

    1. Try different settings in Define HTML section. Under TD change background-color to green and see if you get the green background
      This HTML header can be used not just for this, but all different things.
      You can try $body = Get-services and export it to HTML. See what you get after that.

      Important thing is you must put correct semicolon (;) and double qoutes in the right places.

  3. Hello, I have tried to change background color to green but no change, still i am getting heading and contents section black screen. Tried to $body = Get-services and export it to HTML but its giving error
    Get-services : The term ‘Get-services’ is not recognized as the name of a cmdlet, function, script file, or operable
    program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
    At C:\Users\vpawar\Desktop\ws.ps1:59 char:10
    + $body = Get-services and export it to HTML
    + ~~~~~~~~~~~~
    + CategoryInfo : ObjectNotFound: (Get-services:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

    1. sorry, Its not Get-Services but Get-Service
      have you even tried to Google it?
      I can’t get you all the awnsers, I don’t know how you have setup your enviroment.
      I tried following, and I got just fine mail

      copy just the body part of my code and paste it here

      #HERE#

      #Sent to admin
      $to = “to@company.com”
      $body = Get-Service |Select-Object Name,DisplayName,starttype
      $body = $body |ConvertTo-HTML -head $a |Out-String
      #and finayl send mail to admin
      send-mailmessage -from “from@company.com” -to $to -subject “test” -bodyAsHtml -body $Body -priority Normal -smtpServer “mailserver”

  4. How can i make this Get all the Computers including Downstream computers

  5. Hi, I have client requirement for WSUS reporting on CSV format for both Server and Client.
    Details are mentioned below.
    Can anyone Share/help me on PowerShell Coding. Thank you.
    1)WSUS_Status
    A UpdateServerName WSUS server name
    B ComputerTarget Hostname of client system
    C TargetGroups WSUS group to which client system belongs
    D IpAddress IP address of client system
    E OSDescription Operating System version of client system
    F ClientVersion WSUS client version of client system
    G UpdateStatus Patch update status (%) on client system
    H LastSyncTime Date and time of the last update
    J LastReportedStatusTime Date and time of the last status
    K NeededCount Number of needed patches (sum of M+O)
    L Installed/NotApplicableCount Number of installed and not applicable patches (sum of N+P)
    M DownloadedCount Number of downloaded patches
    N NotApplicableCount Number of not applicable patches
    O NotInstalledCount Number of not installed patches
    P InstalledCount Number of installed patches
    Q FailedCount Number of failed patches

    2)WSUS_Client

    A UpdateServerName WSUS server name
    B Computername Hostname of client system
    C UpdateKB KB number
    D UpdateTitle KB name
    E UpdateInstallationState Install status of the patch
    F UpdateApprovalAction Approval status of the patch
    G UpdateId Technical ID of the patch

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.