Generating patch compliance report from WSUS with PowerShell

The built in reports from WSUS are adequate if you’re satisfied with only the information reported to it from the Windows Update agent of each client. But what if you want to include other information in the reports as well, like the computer description of each WSUS client?

Since I’m not a very good programmer I didn’t really want to fiddle around with the WU/WSUS API:s, and I’m not even sure the computer description could even be reported to WSUS by modifying the API:s anyway.

My go-to scripting language, being a Microsoft guy, is of course PowerShell. Could I perhaps achieve my goal by writing a PS script? Turns out I could. The full script is typed out at the bottom of this post and here is a download link in case the formatting gets all weird if you copy/paste it.

In writing this script I discovered the awesome PowerShell ISE plugin ISESteroids (http://www.powertheshell.com/isesteroids/) which helped me to format my code properly. I’m not a PowerShell expert at all so I take all the help I can get.

On to the meat of this post. I wanted to get the count of ‘applicable/not installed’ patches for every computer from the WSUS database. Doing this in SQL Server Management Studio, and since WSUS was installed with a WID, requires this connection string if the WSUS server is installed on Server 2012 (R2):

 \\.\pipe\MICROSOFT##WID\tsql\query

1-ssms_connect_to_wid

When connected I ran this query, which includes update installation states ‘Installed’, ‘Installed Pending Reboot’,  ‘Downloaded’ and ‘Failed’:


USE SUSDB

SELECT
LEFT(comp.Name,30) as 'Computer Name',
count(COMP.Name) as 'Number of missing updates',
COMP.IPAddress AS 'IP Address',
COMP.LastSyncTime AS 'Last Sync Time',
COMP.OSArchitecture AS 'OS Architecture',
 CASE
 CAST (COMP.OSMajorVersion AS varchar(50)) + CAST ('.' AS varchar(50))
 + CAST (COMP.OSMinorVersion AS varchar(50)) + CAST ('.' AS varchar(50))
 + CAST (COMP.OSBuildNumber AS varchar(50))
 WHEN '6.3.9600' THEN 'Windows Server 2012 R2'
 WHEN '6.1.7601' THEN 'Windows Server 2008 R2 w/ SP1'
 WHEN '5.2.3790' THEN 'Windows Server 2003'
 WHEN '6.1.7600' THEN 'Windows Server 2008 R2 RTM'
 WHEN '5.1.2600' THEN 'Windows XP'

 END AS 'Operating System'

FROM
[PUBLIC_VIEWS].[vUpdateInstallationInfoBasic] UPDBASIC
LEFT JOIN [PUBLIC_VIEWS].[vUpdate] UPD ON UPD.UpdateID = UPDBASIC.UpdateID
LEFT JOIN [PUBLIC_VIEWS].[vComputerTarget] COMP ON COMP.ComputerTargetId = UPDBASIC.ComputerTargetId

WHERE
UPDBASIC.State IN ('2') OR
UPDBASIC.State IN ('3') OR
UPDBASIC.State IN ('5') OR
UPDBASIC.State IN ('6')

GROUP BY
COMP.Name
,COMP.IPAddress
,COMP.LastSyncTime
,COMP.OSArchitecture
,COMP.OSBuildNumber
,COMP.OSMajorVersion
,COMP.OSMinorVersion
,COMP.OSServicePackMajorNumber

ORDER BY COMP.Name

If you want to tweak your report, the different update installation states are listed here:
https://msdn.microsoft.com/en-us/library/windows/desktop/bb313558(v=vs.85).aspx

The query results are these. And ideally I would’ve liked being able to get Active Directory attributes back as well:

3-ssms_missingupdatesqueryresults

I was interested in knowing what results putting the SQL query in a PowerShell script would give back.

First I would need to create the actual connection to the WID database, which I start by specifying the connection string and database. Replace these with your own if needed (connecting to a SQL Server WSUS database installed in the default instance would mean the $SQLServer variable = ‘<database_server_name>’ for instance). After that a whole bunch of magic takes place which I got help with from this website:
http://www.systemcentercentral.com/powershell-how-to-connect-to-a-remote-sql-database-and-retrieve-a-data-set/

# SQL commands to connect to database and run the patch compliance query
$SQLServer = '\\.\pipe\Microsoft##WID\tsql\query'
$SQLDatabase = 'SUSDB'
$SQLQuery = "
&lt;query pasted above&gt;
"
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection
$SQLConnection.ConnectionString = "Data Source=$SQLServer;Initial Catalog=$SQLDatabase;Integrated Security=sspi"
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand
$SQLCommand.CommandText = $SQLQuery
$SQLCommand.Connection = $SQLConnection
$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SQLAdapter.SelectCommand = $SQLCommand
$SQLDataset = New-Object System.Data.DataSet
$SQLAdapter.Fill($SQLDataset) | Out-Null # Out-Null to remove unwanted output
$SQLConnection.Close()

 

This code gives back a dataset ($SQLDataset variable) from which we can extract data. So how do we do that? Since I wanted to pull the AD description of every computer returned by the SQL query it made perfect sense to insert a foreach statement to my code.


$Data = foreach ($row in $SQLDataset.Tables[0].Rows)

{

# to make it a bit tidyer we set new variables based on each "row" from the dataset
[string]$ComputerName = $row[0] -replace "ad.exampledomain.com","" # we need to remove the FQDN to be able to search AD for the computer name
[int]$NumberOfMissingUpdates = $row[1]
[string]$IPAddress = $row[2]
[string]$LastSyncTime = $row[3]
[string]$OSArchitecture = $row[4]
[string]$OperatingSystem = $row[5]
[string]$ADDescription = Get-ADComputer -Identity $ComputerName -Properties Name,Description | Select-Object -ExpandProperty Description # since we want the AD description for every computer we need this

# create custom object for putting compliance data in
New-Object -TypeName PSObject -Property @{
ComputerName=$ComputerName
NumberOfMissingUpdates=$NumberOfMissingUpdates
IPAddress=$IPAddress
LastSyncTime=$LastSyncTime
OSArchitecture=$OSArchitecture
OperatingSystem=$OperatingSystem
ADDescription=$ADDescription

} |

Select-Object ComputerName,NumberOfMissingUpdates,IPAddress,LastSyncTime,OSArchitecture,OperatingSystem,ADDescription # Select is needed to list the data in the correct order in the report

So what data do we get back from this? Let’s have a look in the $Data variable.

12-ise_data_variable_results

Awesome! So now we have all the data we want in a custom PS object that we can manipulate in whatever way we’d like. My goal was to put this data into an Excel spreadsheet that could be given to management whenever they wanted information about patch compliance.

Everyone who’ve ever set out to export PowerShell data to Excel have probably found it as frustrating as me. Sure, you can create an Excel COM object, create a workbook and worksheet and start to add the information. The drawback to that is that you actually need Excel installed on the computer from where you run the PowerShell script. In my case it wasn’t an option to install it (and I wouldn’t have wanted to anyway) on our WSUS server so I had to find another way.

I found a great Excel module on GitHub that gave me exactly what I needed.
https://github.com/dfinke/ImportExcel

Using this module it’s as easy as piping the $Data variable to an ImportExcel cmdlet. To make things even easier there are parameters to the cmdlet which let’s us freeze the top row and set other user friendly options, making the Excel sheet ready to open and just scroll through the document from the get-go.

# using the ImportExcel module here to format the report, i.e. less manual work after
$Data | Export-Excel -Path $ReportPath\$ReportName.xlsx -FreezeTopRow -AutoFilter -BoldTopRow -AutoSize

 

The data is exported to an Excel document specified by variables which I’ll show below when I post the full script.

Writing this code quickly got pretty messy, as most scripting is before cleaning up. I have a tendency to sometimes use cmdlet aliases instead of the full cmdlet name. Doing that in a script is bad practice, even if you don’t intend to share the script with anyone else. Doing it right from the start will get you into a habit of writing good code without having to do much cleanup after.

I decided to try out the ISESteroids module that I mentioned at the beginning of this post and I was surprised at the amount of awesome features it contained. I haven’t begun to explore them all but I thought I would share my findings on this blog. Just follow the installation instructions on their site.

First I’ll post a screenshot of a part of my script as it looked before letting ISESteroids sink its teeth into it:

4-ise_poorlyformattedcode

In my opinion the code isn’t all that bad, but it could definitely be better. So what are some of the things that ISESteroids can do? To help us mere mortals there is an Auto-Format add-on that can be enabled which puts it next to the native Command add-on tab.

5-ise_poorlyformattedcode_autoformat

Using this we can, among other things, automatically replace aliases with their full cmdlet names. Let’s try it out by clicking Apply.

6-ise_poorlyformattedcode_autoformat_replacealiases

Before:

7-ise_poorlyformattedcode_autoformat_replacealiases_before

After:

7-ise_poorlyformattedcode_autoformat_replacealiases_after

This is applied to every cmdlet alias in the whole script!

Another thing that I hadn’t thought about regarding my script was that I’d put data in a variable that wasn’t even used later on, i.e. totally unnecessary. ISESteroids alerts us to this by “dimming” the variable.

8-ise_unusedvariable

Hovering the mouse over the variable will explain. Right-clicking the variable and choosing PSSharper –> Manage Issue will open another add-on tab and give you options about what to do. In my case I just deleted that part of my code.

9-ise_unusedvariable_pssharper

Properly indenting code makes it a lot easier to read. There is a keyboard shortcut for automatically indenting script block code. Pressing ALT+I, this window pops up. After clicking OK my foreach statement is indented for me:

4-ise_poorlyformattedcode_after

Another great feature when testing scripts is the extra “Run Script” button. Right-clicking it will let you run the script in different contexts and with different PowerShell versions. Very handy when trying to make sure the script will run as expected.

11-ise_run_script_in_console_right_click

Being able to create an application (exe) from your script is another feature that’ll be very useful if you’re creating script tools that are intended to be executed by other people, for example the helpdesk guys.

21-ise_create_application

Clicking the admin privileges checkbox will trigger a UAC prompt when the exe is started.

22-ise_create_application_options

 

I’m going to try to put all this together now. This post got a lot longer than I expected when I started writing.

This is the script in its entirety:

# requires -Version 3.0 -Modules ActiveDirectory, ImportExcel
# Name: Get-ComputersMissingUpdatesCount.ps1
# Purpose: Lists all computers reporting to WSUS.
# Includes all computers with at least 1 applicable patch that is not installed on that computer.
#
# References:
#
# ImportExcel
# https://github.com/dfinke/ImportExcel
#
# Making SQL connections in PowerShell
# http://www.systemcentercentral.com/powershell-how-to-connect-to-a-remote-sql-database-and-retrieve-a-data-set/

$TodaysDate = Get-Date -Format yyyy-MM-dd
$ReportPath = '&lt;report folder path&gt;'
$ReportName = "Patch_Compliance_Report_$TodaysDate"

$SMTPServer = '&lt;smtp server here&gt;'
$SMTPPort = 25
$EmailSender = '&lt;sender email address&gt;'
$EmailRecipient = '&lt;recipient email address here&gt;'
$EmailSubject = "Patch Compliance Report - generated: $TodaysDate"
$EmailBody = "
Hello!`r`n
This is an auto-generated report which lists all computers currently reporting to WSUS.`r`n
The report includes all computers with at least 1 applicable patch that is not installed on that computer.
"
$EmailAttachment = "$ReportPath\$ReportName.xlsx"

Import-Module ActiveDirectory,ImportExcel

# SQL commands to connect to database and run the patch compliance query
$SQLServer = '\\.\pipe\Microsoft##WID\tsql\query'
$SQLDatabase = 'SUSDB'
$SQLQuery = "
SELECT LEFT(comp.Name,30) as 'Computer Name',
count(COMP.Name) as 'Number of missing updates',
COMP.IPAddress AS 'IP Address',
COMP.LastSyncTime AS 'Last Sync Time',
COMP.OSArchitecture AS 'OS Architecture',
CASE
CAST (COMP.OSMajorVersion AS varchar(50)) + CAST ('.' AS varchar(50))
+ CAST (COMP.OSMinorVersion AS varchar(50)) + CAST ('.' AS varchar(50))
+ CAST (COMP.OSBuildNumber AS varchar(50))
WHEN '6.3.9600' THEN 'Windows Server 2012 R2'
WHEN '6.1.7601' THEN 'Windows Server 2008 R2 w/ SP1'
WHEN '5.2.3790' THEN 'Windows Server 2003'
WHEN '6.1.7600' THEN 'Windows Server 2008 R2 RTM'
WHEN '5.1.2600' THEN 'Windows XP'

END AS 'Operating System'

FROM
[PUBLIC_VIEWS].[vUpdateInstallationInfoBasic] UPDBASIC
LEFT JOIN [PUBLIC_VIEWS].[vUpdate] UPD ON UPD.UpdateID = UPDBASIC.UpdateID
LEFT JOIN [PUBLIC_VIEWS].[vComputerTarget] COMP ON COMP.ComputerTargetId = UPDBASIC.ComputerTargetId

WHERE
UPDBASIC.State IN ('2') OR
UPDBASIC.State IN ('3') OR
UPDBASIC.State IN ('5')

GROUP BY
COMP.Name
,COMP.IPAddress
,COMP.LastSyncTime
,COMP.OSArchitecture
,COMP.OSBuildNumber
,COMP.OSMajorVersion
,COMP.OSMinorVersion
,COMP.OSServicePackMajorNumber

ORDER BY COMP.Name
"

$SQLConnection = New-Object System.Data.SqlClient.SqlConnection
$SQLConnection.ConnectionString = "Data Source=$SQLServer;Initial Catalog=$SQLDatabase;Integrated Security=sspi"
$SQLCommand = New-Object System.Data.SqlClient.SqlCommand
$SQLCommand.CommandText = $SQLQuery
$SQLCommand.Connection = $SQLConnection
$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SQLAdapter.SelectCommand = $SQLCommand
$SQLDataset = New-Object System.Data.DataSet
$SQLAdapter.Fill($SQLDataset) | Out-Null # Out-Null to remove unwanted output
$SQLConnection.Close()

$Data = foreach ($row in $SQLDataset.Tables[0].Rows)

{

# to make it a bit tidyer we set new variables based on each "row" from the dataset
[string]$ComputerName = $row[0] -replace "ad.exampledomain.com","" # we need to remove the FQDN to be able to search AD for the computer name
[int]$NumberOfMissingUpdates = $row[1]
[string]$IPAddress = $row[2]
[string]$LastSyncTime = $row[3]
[string]$OSArchitecture = $row[4]
[string]$OperatingSystem = $row[5]
[string]$ADDescription = Get-ADComputer -Identity $ComputerName -Properties Name,Description | Select-Object -ExpandProperty Description # since we want the AD description for every computer we need this

# create custom object for putting compliance data in
New-Object -TypeName PSObject -Property @{
ComputerName=$ComputerName
NumberOfMissingUpdates=$NumberOfMissingUpdates
IPAddress=$IPAddress
LastSyncTime=$LastSyncTime
OSArchitecture=$OSArchitecture
OperatingSystem=$OperatingSystem
ADDescription=$ADDescription } |

# Select is needed to list the data in the correct order in the report
Select-Object ComputerName,NumberOfMissingUpdates,IPAddress,LastSyncTime,OSArchitecture,OperatingSystem,ADDescription
}

# using the ImportExcel module here to format the report, i.e. less manual work after
$Data | Export-Excel -Path $ReportPath\$ReportName.xlsx -FreezeTopRow -AutoFilter -BoldTopRow -AutoSize

Send-MailMessage -SmtpServer $SMTPServer -Port $SMTPPort -Subject $EmailSubject -Body $EmailBody -Attachments $EmailAttachment -From $EmailSender -To $EmailRecipient 

 

Running this script will now create a nice report and email it to me and management every week so we can follow up on patching that’s been done throughout the week.

20-excel_report_results

This script doesn’t contain any error handling and is provided as-is. The account used to execute the script needs at least read access to the SUSDB database. Whenever I have time I might update the script here and make it more robust.

Advertisements