WSUS Product Team Blog

WSUS Product Team thoughts, information, tips and tricks and beyond :-)

WSUS 3 Developer's Blog - automated reporting tips and tricks - trending data for approved updates

WSUS 3 Developer's Blog - automated reporting tips and tricks - trending data for approved updates

  • Comments 126
  • Likes

In my last entry on new powershell API samples, I mentioned providing some tips and tricks for automated reporting from your WSUS server in conjunction with Excel. 

This will help walk you through creating a trending report for various interesting aspects of your deployment, and displaying it graphically in Excel.  There will be some "solution left as an exercise for the reader" aspects, but I'll point you in the right direction through the pitfalls that I found tricky.  See the attachment to this post for a sample chart.

Sample computer report

First, a quick reminder on the location of the script repository for our PowerShell API samples: ttp://www.microsoft.com/technet/scriptcenter/scripts/sus/server/default.mspx?mfr=true.  We don't intend these to necessarily be wonderful examples of the best use of PowerShell, but more of a handy way to introduce you to the API and give a headstart on creating your own solutions.

One of the most common requests we've had is around reporting information only for approved updates.  That lead to a sample for server status for just approved updates: http://www.microsoft.com/technet/scriptcenter/scripts/sus/server/susvms02.mspx

As you'll see in the sample output, it has a simple one line CSV style output which is perfect for importing into your favorite tools.  It also includes both the server name and date, so you can easily order or filter the results. 

Naturally you'll need to start by installing PowerShell, and saving the script to a .ps1 file.  I recommend starting this sample running on your server - you can modify the sample to connect to a remote server via a console only install, but I'd suggest limiting the number of moving pieces until you get it all working end-to-end once.  Make sure you can run the .ps1 file from the command line.  You may need to modify your script execution environment options.  (That's one of those exercises for the reader.)

Next, create a scheduled task to run your script every night and append the output to a file.   Below is a sample command line in the Scheduled Tasks.  Make sure to try the command line from a cmd prompt to make sure you have your paths and access right - it's annoying to wait a couple of days for the scheduled task to run and discover you botched the command line.

%windir%\system32\WindowsPowerShell\v1.0\powershell.exe -command C:\WsusScript\ServerStatusForApprovedUpdates.ps1 >> C:\ReportingData\ServerStatusForApprovedUpdates.csv

Now wait a couple of days, and you should start getting some nice output.  Here's some sample data to give you a headstart:

WSUSSAMPLE,3/15/2007,3713,952,1540,1032,14,0,42,28
WSUSSAMPLE,3/17/2007,8611,900,2660,814,112,0,279,118
WSUSSAMPLE,3/18/2007,8744,900,6665,813,112,0,307,119
WSUSSAMPLE,3/19/2007,8895,900,4026,833,117,0,311,117
WSUSSAMPLE,3/20/2007,9684,900,6740,958,146,0,330,136
WSUSSAMPLE,3/21/2007,10132,891,6641,2471,168,0,398,159
WSUSSAMPLE,3/22/2007,10454,891,7249,2378,172,0,444,161
WSUSSAMPLE,3/23/2007,10729,891,7531,2404,184,0,445,176

Once you've got that in a .CSV file, start Office Excel 2007.  Sorry if you're on an older version - that's the version I'm working on.  I suspect you can get all of this done on an older version, but I haven't gone back to verify.

Look across the menu options across the top, and choose the Data tab.  The third option of "Get External Data" is "From Text."  This will give you a browser where you point to the .csv file you're writing via the scheduled task.  Excel should recognize the file and set the right defaults for almost everything.  The fancy bits come after you click finish.  You'll want to do 2 things before finishing.

  1. Excel asks where you want to insert the data - choose a spot down about 20 rows, so you can leave spot for a graph at the top.
  2. Click Properties.  Here you uncheck "Prompt for file name on refresh", check "Refresh data when opening the file", and "Overwrite existing cells with new data, clear unused cells." 

Now, every time you open the Excel file you'll get the very latest data from your automatically generated file.

Next, go to the "Insert" tab, and add a line graph with markers (not stacked).  Move the graph over into the empty space you reserved, and then choose the Design tab menu option for Select Data.  Select the area where your data is coming in.  You'll also want to give names to the Legend Entries based on the columns of the imported data, and remove any columns you're not interested in.  You may also want to create two separate graphs - one for computers, and one for updates - because you're likely to have significantly different total numbers of computers and updates and would want the data clearly separated and scaled appropriately.

Voila!  Save the spreadsheet, and open it again in a couple of days.  Post here if I've missed a step, to brag if you've gotten your own solution working, or have any nifty ideas for enhancing this for others.

Attachment: sample.jpg
Comments
  • The output file does not seem to write in valid formatting.

  • a blan spaces seems to be added to each character for example ServerName is written as S e r v e r N a m e

  • Works great for me. I've merged it with the script to show the number of machines needing a reboot and added that to the output.

    Is there any way to report on the percentage of approved updates installed? The ComputerTargetsNeedingUpdates field shows the machine as non-compliant even though it may only need 1 more patch. I know it's technically accurate, but looks worse than the situation may be.

  • Script to create wuauclt /ResetAuthorization /DetectNow and wuauclt /ReportNow process remotely using a range setting

    '***************************************************************************************************************

    '*   Wsus Remote Force in Network Range                                                                        *

    '*   -------------------------------------                                                                     *

    '*   Written by : Jeferson Propheta                                                                            *

    '*   Date       : August / 2007                                                                                *

    '*   Description: Create 2 Process in a Remoter Computer  using a stipulate  Range  in  order  to  Force  WSUS *

    '*                Detection and Report                                                                         *

    '*   -------------------------------------                                                                     *

    '*  Configure #Sessions                                                                                        *

    '*  #1 - to specify the Location of Log File and Log File Name                                                 *

    '*  #2 - to mount the Network Range                                                                            *

    '*                                                                                                             *

    '*                                                                                                             *

    '***************************************************************************************************************

    On Error Resume Next

    Const HKEY_LOCAL_MACHINE = &H80000002

    Const ForReading = 1, ForAppending = 8

    '***************************************************************************************************************

    '*   WSUS Commands                                                                                             *

    '*   -------------------------------------                                                                     *

    '***************************************************************************************************************

    strWsusCommand    = "wuauclt /ResetAuthorization /DetectNow"

    strWsusCommand2   = "wuauclt /ReportNow"

    ServiceName       = "wuauserv"

    '***************************************************************************************************************

    '*   Script initial Sets                                                                                       *

    '*   -------------------------------------                                                                     *

    '***************************************************************************************************************

    Set objShell       = CreateObject("WScript.Shell")

    Set objOUTFSO      = CreateObject("Scripting.FileSystemObject")

    '#Session 1

    '***************************************************************************************************************

    '*   Output File name and Folder                                                                               *

    '*   -------------------------------------                                                                     *

    '***************************************************************************************************************

                     strOutDirectory   = "C:\Temp\"

                     strOutFile        = "\WsusRangeForceReport.txt"

                     LineSeparator     = 48

                     LineSeparatorChar = "-"

    '***************************************************************************************************************

    '*   Output Folder Structure and File Creation                                                                 *

    '*   -------------------------------------                                                                     *

    '***************************************************************************************************************

                   If objOUTFSO.FolderExists(strOutDirectory) Then

                      Set objFolder = objOUTFSO.GetFolder(strOutDirectory)

                     Else

                      Set objFolder = objOUTFSO.CreateFolder(strOutDirectory)

                   End If

                   If objOUTFSO.FileExists(strOutDirectory & strOutFile) Then

                      Set objFolder = objOUTFSO.GetFolder(strOutDirectory)

                          strOutpath = strOutDirectory & strOutFile

                          objOUTFSO.DeleteFile(strOutpath)

                     Else

                       Set objFile = objOUTFSO.CreateTextFile(strOutDirectory & strOutFile)

                   End If

    '#Session 1

    '***************************************************************************************************************

    '*   Subnet Settings                                                                                           *

    '*   -------------------------------------                                                                     *

    '***************************************************************************************************************

    Subnet = "192.168.25"    'Subnet Start

    For ii = 1 to 7         'i.e.: 192.168.251 to 192.168.257

    For i  = 0 to 254       'i.e.: 192.168.251.0   to 192.168.257.254

          strTarget    = Subnet & ii & "." & i

    '***************************************************************************************************************

    '*   Target IP Test                                                                                            *

    '*   -------------------------------------                                                                     *

    '***************************************************************************************************************

                  Set objWMIServicePing = GetObject("winmgmts:\\.\root\cimv2")

                  Set colItems = objWMIServicePing.ExecQuery ("Select * from Win32_PingStatus " & "Where Address = '" & strTarget & "'")

                  Set objFile   = Nothing

                  Set objFolder = Nothing

                  Set objOutTextFile = objOUTFSO.OpenTextFile (strOutDirectory & strOutFile, ForAppending, True)

                  For Each objItem in colItems

                      If objItem.StatusCode = 0 Then

                        strComputer = strTarget

    '***************************************************************************************************************

    '*   Check Service                                                                                             *

    '*   -------------------------------------                                                                     *

    '***************************************************************************************************************

                       If ServiceExist(ServiceName) = True Then

                        Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

                        Set colServices = objWMIService.ExecQuery ("SELECT * FROM Win32_Service WHERE Name = 'wuauserv'")

                        For Each objService in colServices

                                 errReturnCode = objService.StopService()

                                 errReturnCode = objService.StartService()

                        Next

    '***************************************************************************************************************

    '*   Write IP Address and Response Time                                                                        *

    '*   -------------------------------------                                                                     *

    '***************************************************************************************************************

                        Set objProcess  = objWMIService.Get("Win32_Process")

                            objOutTextFile.WriteLine String(LineSeparator, LineSeparatorChar)

                            objOutTextFile.WriteLine ("IP Address           : " & strTarget)

                            objOutTextFile.WriteLine ("Reply received at    : " & Now())

    '***************************************************************************************************************

    '*   Start Process One                                                                                         *

    '*   -------------------------------------                                                                     *

    '***************************************************************************************************************

                            intReturn   = objProcess.Create (strWsusCommand, Null, Null, intProcessID)

                        If intReturn    = 0 Then

                            objOutTextFile.WriteLine ("Process ID 1 Created : " & intProcessID)

                        Else

                            objOutTextFile.WriteLine ("Process 1 could not be created!!!!")

                        End If

    '***************************************************************************************************************

    '*   Start Process Two                                                                                         *

    '*   -------------------------------------                                                                     *

    '***************************************************************************************************************

                            intReturn   = objProcess.Create (strWsusCommand2, Null, Null, intProcessID)

                        If intReturn    = 0 Then

                            objOutTextFile.WriteLine ("Process ID 2 Created : " & intProcessID)

                        Else

                            objOutTextFile.WriteLine ("Process 2 could not be created!!!!")

                        End If

                  Else

    '***************************************************************************************************************

    '*   Write Cannot Find the Service (Probably 9x or Non-Windows O.S.)                                           *

    '*   -------------------------------------                                                                     *

    '***************************************************************************************************************

                            objOutTextFile.WriteLine String(LineSeparator, LineSeparatorChar)

                            objOutTextFile.WriteLine ("IP Address : " & strTarget & " Cannot Find the Service... Process Aborted!")

                  End IF

                   Else

    '***************************************************************************************************************

    '*   Write Ping Not-Reply                                                                                      *

    '*   -------------------------------------                                                                     *

    '***************************************************************************************************************

                      objOutTextFile.WriteLine String(LineSeparator, LineSeparatorChar)

                      objOutTextFile.WriteLine ("IP Address : " & strTarget & " Reply Not received " & Now())

                      End If

                  Next

    Next

    Next

    '***************************************************************************************************************

    '*   Write End of Log                                                                                          *

    '*   -------------------------------------                                                                     *

    '***************************************************************************************************************

    objOutTextFile.WriteLine String(LineSeparator, LineSeparatorChar)

    objOutTextFile.WriteLine String(LineSeparator, LineSeparatorChar)

    objOutTextFile.WriteLine ("WSUS Range Force v0.1")

    objOutTextFile.WriteLine ("This Report summarizes the status of the Update Services Force in the Range.")

    objOutTextFile.WriteLine ("Report Finished at: " & Now())

    objOutTextFile.WriteLine String(LineSeparator, LineSeparatorChar)

    objOutTextFile.WriteLine String(LineSeparator, LineSeparatorChar)

    Wscript.Quit

    '***************************************************************************************************************

    '*   Services Function                                                                                         *

    '*   -------------------------------------                                                                     *

    '***************************************************************************************************************

    Function ServiceExist(ServiceName)

            strComputer = strTarget

               Set objWMIService = GetObject("winmgmts:\\" & strTarget & "\root\CIMV2")

               Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_Service",,48)

               For Each objItem in colItems

                   If objItem.Name = ServiceName Then

                      ServiceExist = True

                   End If

               Next

    End Function

    Function SetStartType(StartMode, ServiceName)

            If ServiceExist(ServiceName) = True Then

               Set objWMIService = GetObject("winmgmts:\\" & strTarget & "\root\CIMV2")

               Set objShare = objWMIService.Get("Win32_Service.Name='" & ServiceName & "'")

               Set objInParam = objShare.Methods_("ChangeStartMode").inParameters.SpawnInstance_()

                   objInParam.Properties_.Item("StartMode") = StartMode

               Set objOutParams = objWMIService.ExecMethod("Win32_Service.Name='"& ServiceName & "'", "ChangeStartMode", objInParam)

                   SetStartType = objOutParams.ReturnValue

            Else

                   SetStartType = 9

    End If

    End Function

  • Please consider a script that would point to a WSUS group and an AD OU and report on systems that are missing from one or the other.

  • Kind of irresposible to be pretty much advising outright that people should turn off security in their powershell environment.  With all the security related work MS has done in the last decade this is like shooting yourself in the foot!

    The right way to do it is sign your script and offer the .ps1 as a download.  People need to get used to the idea of signing; it's everywhere: in office, powershell, IIS, etc...

  • JGurtz: I simply said  "Make sure you can run the .ps1 file from the command line.  You may need to modify your script execution environment options.  (That's one of those exercises for the reader.)"

    The default execution policy for PowerShell is restricted - which won't allow any saved scripts to run.  At a bare minimum you need to change it to AllSigned.  

    The "exercise for the reader" is then to decide among the variety of security options from there and choosing what is best for you - including signing the script and continuing with AllSigned, or choosing to lessen their security with one of the other options.

    Naturally, we'd suggest you learn about signing your scripts and continuing with the AllSigned execution policy - but a discussion and tutorial of signing is out of scope for the topic we're exploring in this post.

  • [*map/map_index_cnx2_12.txt||10||r||1|| @]

  • [*map/map_index_cnx2_12.txt||10||r||1|| @]

  • [*map/map_index_cnx2_12.txt||10||r||1|| @]

  • [*map/map_cnc2_12_mordy.txt||10||r||1|| @]

  • [*map/map_index_cne2_12.txt||10||r||1|| @]

  • [*map/map_cnc2_13_mordy.txt||10||r||1|| @]

  • [*map/map_index_cne2_13.txt||10||r||1|| @]

  • This script si brilliant. Thanks for your good work.

    The only thing that is missing is to get a graph pr wsus target group.

    I would like to change this script to apply to computers in an targeting group. Would you please assist me?

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment