Luka Gros
Tips n' Tricks from SQL And Windows administrator

WSUS–PowerShell Reporting

admin 26.02.2016
WSUS, WSUS 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”

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

    Reply

  3. 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

    Reply

    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.

      Reply

  4. 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

    Reply

    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”

      Reply

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

    Reply

  6. 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

    Reply

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.