Fix Http 500.19 Error after Removing WSUS

This post is more of a ‘note to self’ for troubleshooting IIS errors.

Recently I decided to remove the WSUS role from an SCCM distribution point as it was previously being used for patching during OSD, but now we patch only the reference image instead. After removing the WSUS role, I also did some cleanup including deleting the WSUS_Updates directory, the %Program Files%\Update Services directory and removing the WsusPool website and application pool from IIS.

After that, clients using that distribution point failed to get content for packages, returning an Http 500 error:

smsts

Checking the IIS log on the distribution point I found the following corresponding entry:

iis

The error code is 500.19 which translates to ‘Internal Server Error / Configuration data is invalid‘ and is documented in more detail in this Microsoft article. A further clue is found in the 126 windows error code, which translates to ‘The specified module could not be found‘.

Browsing to the website directly, ie http://localhost/SMS_DP_SMSPKG$, gave the following information:

siteerror

The error code 0x8007007e is the same as the windows error code 126 and also means ‘The specified module could not be found”. The module referenced in the error is the DynamicCompressionModule.

To get more detailed information on the error, I decided to enable Failed Request Tracing in IIS and log the 500.19 error. The process for enabling and using Failed Request Tracing is nicely summarised here as well as documented by Microsoft here. Using that, I found that a couple of modules were being referenced that were no longer present.

ftrerror

Following a hint from here, I checked the applicationHost.config file and found a reference to a dll in the %Program Files%\Update Services directory that was installed with WSUS and that I deleted after removing the WSUS role:


<scheme name="xpress" doStaticCompression="false" doDynamicCompression="true" dll="C:\Program Files\Update Services\WebServices\suscomp.dll" staticCompressionLevel="10" dynamicCompressionLevel="0" />

To remove the reference, I ran the following command as admin:


%windir%\system32\inetsrv\appcmd.exe set config -section:system.webServer/httpCompression /-[name='xpress']

You can find more info on IIS modules and how to add/remove/disable/enable here.

After removing the reference to the xpress schema and restarting the W3SVC service, everything was back to normal 🙂

Parse the WindowsUpdate.log on Local and Remote Computers with PowerShell

The WindowsUpdate.log, which logs activities of the Windows Update client, is not the easiest of log files to parse through but it’s handy one for finding details about update installation successes and failures.  To make searching this log file easier both on the local computer, a remote computer (where PS remoting is enabled) or groups of computers, I wrote a simple but handy PowerShell script.

The script will retrieve the most important elements of the log – the date, time, component and entry text – and put them into a PS object.  This allows us to summarise the number of entries in the log by either component or date, for example, or provide some search filters to find specific entries and in a specific time period, for example all the software updates installed in the last 3 days.

I haven’t tried this with Windows 10 yet, where the WU logging mechanism has changed, but it will work for older operating systems that have PowerShell installed.

Download

Download the script from the Technet Gallery.

Examples

Note: due to the number of results returned when parsing log entries, I recommend piping to Gridview for easier viewing.

First, let’s find the number of entries in the log for each component:


Parse-WindowsUpdateLog.ps1 -GroupBy Component

 

Capture

So there are 54 entries for the “Content Install” component, lets read those:


Parse-WindowsUpdateLog.ps1 -Component "Content Install" | Out-Gridview

Capture

Now let’s search for the keyword “warning” anywhere in the log, in the last 3 days, on a remote computer:


Parse-WindowsUpdateLog.ps1 -ComputerName SRV001 -Days 3 -Text warning | Out-GridView

Capture

A benefit of using PowerShell’s Gridview is that you can also filter the results dynamically using the criteria:

capture

Let’s wrap the script in some additional code to find the number of updates successfully installed in the last 7 days across an array of computers:


"srvsccm-01","srvsccm-02","srvsccm-03v" | ForEach-Object {
    $UpdatesInstalled = (Parse-WindowsUpdatesLog -ComputerName $_ -Days 7 -Text "Installation successful").Count
    New-Object -TypeName PSObject -Property @{
        ComputerName = $_
        UpdatesInstalled = $UpdatesInstalled
        }
    } | ft -AutoSize

capture

Finally, I want to find out if KB3092627 was installed on a group of servers in an AD group in the last 30 days:


Get-ADGroupMember -Identity SCCM2012_Secondary_WSUS_Servers | 
    Sort Name | 
    Select -ExpandProperty Name |
    ForEach-Object {
        $KB = Parse-WindowsUpdatesLog -ComputerName $_ -Days 30 -Text "KB3092627" | 
            where {$_.Details -match "Installation Successful"} | 
            Select -ExpandProperty Date
        If ($KB -ne $null)
            {$Installed = "True"}
        Else {$Installed = "False"}
        New-Object -TypeName PSObject -Property @{
            ComputerName = $_
            KBInstalled = $Installed
            InstallDate = $KB
            }
    } | ft -AutoSize

capture

Pretty handy 🙂

There is some (outdated) advice for searching the WindowsUpdate.log in the following MS KB: http://support.microsoft.com/en-us/kb/902093

The list of parameters you can use are:

-ComputerName (optional, to get results from a remote computer)
-Days (mandatory, the number of days past to search the log)
-Component (optional, choose from the list of available components to filter results)
-Text (optional, search for a specific keyword or phrase)
-GroupBy (optional, group results by either date or component to find the number of log entries)

WSUS Database Maintenance for SQL Express

To perform database maintenance (defragmentation, re-indexing) on a WSUS database that is installed using SQL Express, I use the following solution.  Because SQL Express has no SQL Server Agent, you cannot create jobs or maintenance plans, but we can use Powershell with a Scheduled Task to perform regular maintenance.  I am using SQL Express 2012.

Download the WSUSDBMaintenance script from the Technet Gallery, and save it to a local folder on your WSUS server/s.

Then use the following Powershell script as a scheduled task to call the sql maintenance script and send an email to yourself with the output of the sql script as a log file.


$WSUSServers = @(
    "wsussrv-01v",
    "wsussrv-02v",
    "wsussrv-03v",
    "wsussrv-04",
    "wsussrv-05v",
    "wsussrv-06v",
    "wsussrv-07v"
    "wsussrv-08",
    "wsussrv-09v",
    "wsussrv-10v",
    "wsussrv-11v"
    )

foreach ($server in $WSUSServers)
    {
        $S = New-PSSession -ComputerName $server
        Invoke-Command -Session $S -ArgumentList $server -ScriptBlock {
        param ($server)
        Invoke-SQLCmd -InputFile C:\LocalScripts\SUSDB_Maintenance.sql -ServerInstance $env:COMPUTERNAME -OutputSqlErrors $True -Verbose *>$env:TEMP\SUSDB_Maintenance_$Server.log
        Send-MailMessage -To Trevor.jones@contoso.com -From Powershell@contoso.com -SmtpServer mysmtpserver -Subject "WSUS DB Maintenance log for $server" -Attachments $env:TEMP\SUSDB_Maintenance_$Server.log
        }
        Remove-PSSession $S
    }


Note that I am performing this maintenance on multiple WSUS servers in succession using an array.

In the Invoke-SQLCmd command, change the inputfile to the location of your sql script.

In the Send-MailMessage command, use the particulars for your environment

Powershell remoting must be available as the command will be run on the WSUS server itself.

If you are using an older version of SQL Express, you may need to add the SQL snapins first as described here.  In SQL Express 2012, the cmdlet is a part of a module that will be imported when you call it (Powershell 3.0 +).

WSUS Server Cleanup Report

There are a few scripts out there that will perform a cleanup of your WSUS server/s, but here’s my contribution 🙂  It uses the Invoke-WSUSServerCleanup cmdlet only available in Windows Server 2012 / Windows 8 onwards, so for previous versions try something like Kaido Järvemets’ script.  This script will perform the WSUS cleanup for any number of WSUS servers, then send you a summary html email report.  It will also give you the time taken for each WSUS server to perform the cleanup, convert the ‘Freed diskpace’ value into a more useful GB value, and report any errors that occurred trying to perform the cleanup.  An error can sometimes occur when maintenance has not been performed for a long time on the server, and the cleanup can timeout.

Simply add your WSUS server names to the $WSUSservers variable, and add the mail settings for your environment. If you are using upstream/downstream WSUS servers, perform the cleanup on the lowermost downstream server first, and work up the heirarchy, as recommended by Microsoft.

You can then run the script as a scheduled task to perform regular maintenance.

Capture


$WSUSServers = @(
    "wsussrv-01v",
    "wsussrv-02v",
    "wsussrv-03v",
    "wsussrv-04v",
    "wsussrv-05v",
    "wsussrv-06v",
    "wsussrv-07",
    "wsussrv-08v",
    "wsussrv-09",
    "wsussrv-10v",
    "wsussrv-11v",
    "wsussrv-12v"
    )

# Mail settings
$smtpserver =  "mysmtpserver"
$MailSubject = "WSUS Cleanup Report"
$MailRecipients = "trevor.jones@contoso.com"
$FromAddress = "WSUS@contoso.com"

# Location of temp file for email message body (will be removed after)
$msgfile = "$env:Temp\mailmessage.txt"

$ErrorActionPreference = "Stop"

#region Functions
function New-Table (
$Title,
$Topic1,
$Topic2,
$Topic3,
$Topic4,
$Topic5,
$Topic6,
$Topic7

)
{ 
       Add-Content $msgfile "<style>table {border-collapse: collapse;font-family: ""Trebuchet MS"", Arial, Helvetica, sans-serif;}"
       Add-Content $msgfile "h2 {font-family: ""Trebuchet MS"", Arial, Helvetica, sans-serif;}"
       Add-Content $msgfile "th, td {font-size: 1em;border: 1px solid #87ceeb;padding: 3px 7px 2px 7px;}"
       Add-Content $msgfile "th {font-size: 1.2em;text-align: left;padding-top: 5px;padding-bottom: 4px;background-color: #87ceeb;color: #ffffff;}</style>"
       Add-Content $msgfile "<h2>$Title</h2>"
       Add-Content $msgfile "<p><table>"
       Add-Content $msgfile "<tr><th>$Topic1</th><th>$Topic2</th><th>$Topic3</th><th>$Topic4</th><th>$Topic5</th><th>$Topic6</th><th>$Topic7</th></tr>"
}

function New-TableRow (
$col1, 
$col2,
$col3,
$col4,
$col5,
$col6,
$col7

)
{
Add-Content $msgfile "<tr><td>$col1</td><td>$col2</td><td>$col3</td><td>$col4</td><td>$col5</td><td>$col6</td><td>$col7</td></tr>"
}

function New-TableEnd {
Add-Content $msgfile "</table></p>"}
#endregion


# Create file
New-Item $msgfile -ItemType file -Force | Out-Null

# Add html header
Add-Content $msgfile "<style>h2 {font-family: ""Trebuchet MS"", Arial, Helvetica, sans-serif;}</style>"
Add-Content $msgfile "<p></p>"
        
# Create a new html table
New-Table -Title "WSUS Cleanup Report $(Get-Date -Format f)" -Topic1 "WSUS Server" -Topic2 "Declined Expired Updates" -Topic3 "Declined Superseded Updates" -Topic4 "Cleaned Obsolete Updates" -Topic5 "Compressed Updates" -Topic6 "CleanedUp Unneeded Content Files" -Topic7 "Time Taken"
        

# Run the cleanup on each server
foreach ($WsusServer in $WSUSServers)
    {
        try
            {
            $startDTM = (Get-Date)
            write-host "Doing cleanup on $WSUSServer"
            $Cleanup = Get-WsusServer -Name $WsusServer -PortNumber 8530 | Invoke-WsusServerCleanup -DeclineExpiredUpdates -DeclineSupersededUpdates -CleanupObsoleteUpdates -CompressUpdates -CleanupUnneededContentFiles
            $endDTM = (Get-Date)
            $Time = $endDTM-$startDTM
            $TimeTaken = $Time.Hours.ToString() + " hours, " + $Time.Minutes.ToString() + " minutes, " + $Time.Seconds.ToString() + " seconds"
            $DiskspaceFreed = $cleanup[4].Split(":")[0] + ":" + ([math]::round(($cleanup[4].Split(":")[1] / 1GB),2)).ToString() + " GB" 
            New-TableRow -col1 $WSUSServer -col2 $Cleanup[1] -col3 $Cleanup[0].Replace("Obsolete Updates Deleted", "Superseded Updates Declined") -col4 $Cleanup[2] -col5 $Cleanup[3] -col6 $DiskspaceFreed -col7 $TimeTaken
            }
        catch
            {
            New-TableRow -col1 $WSUSServer -col2 "Failed to perform cleanup." -col3 $($_.Exception.Message)
            }
    }

# Add html table to file
New-TableEnd

# Set email body content
$mailbody = Get-Content $msgfile

Send-MailMessage -Body "$mailbody" -From $FromAddress -to $MailRecipients -SmtpServer $smtpserver -Subject $MailSubject -BodyAsHtml 

# Delete tempfile 
Remove-Item $msgfile

You can follow the cleanup process on each server from the SoftwareDistribution.log located at %ProgramFiles%\Update Services\LogFiles.

Dealing with ‘multiple-reboot’ patches during OSD

If you are incorporating some patching process during your OS Deployments, you’ve undoubtedly come across the issue where some patches released by Microsoft cause multiple reboots.  These additional reboots are unhandled by the task sequence, which causes it to quit with little explanation.  This is documented in the following MS KB article, where MS also maintains the list of patches that are known to cause this: http://support.microsoft.com/kb/2894518.

Depending on how you do your patching, there are different ways to handle this.  One way to do it is to update your reference images to include the patches.  But it may not be practical to do this every time a new ‘multiple-reboot’ patch is discovered.  And let’s face it, it cannot be done quickly.

If you are using the Software Updates feature of ConfigMgr, then you can remove those patches from both the Software Update groups and the Deployment Packages, and make sure your ADRs don’t try to pull in those patches again.

If, like me, you use WSUS to patch your builds, which is nicely documented by Chris Nackers here, then you can use the WUMU_ExcludeKB variable either in your customsettings.ini file, or in the task sequence itself, to block the patches from being installed.  However, I have seen some reports that this is not 100% reliable.

My preferred method to do this is simply to ‘decline’ the updates on all our WSUS servers (which are standalone).  This prevents them from being installed by WSUS as only ‘approved’ updates can be installed.  Obviously to decline all those patches manually would take some time if you have a few WSUS servers as we do, but thankfully Powershell can help us.

Here are a couple of scripts that I use to do this.  The first will search each WSUS server for any of those ‘multiple-reboot’ patches by using the KB number.  Then it will report on the approval status of each patch so you can identify if it needs to be declined or not.

The next script will then go ahead and decline all those updates on each WSUS server in succession.  You could run the first script again afterwards to verify that the updates were declined.

Search for ‘multiple-reboot’ patches in WSUS

Logging is done in brief to the console, and in more detail to a text file which shows you each patch and it’s status on each server.


<#

This script will search for all the updates in the $Updates variable on each WSUS server in the $WSUSservers variable, and report their approval status.
It logs to the console and more detailed logging to a text file.

#>
# This update list is the one from http://support.microsoft.com/kb/2894518 which lists software updates that cause multiple reboots and kills the WSUS step in the OSD Task Sequence
$Updates = `
"2984976", `
"2981685", `
"2966034", `
"2965788", `
"2920189", `
"2871777", `
"2871690", `
"2862330", `
"2771431", `
"2821895", `
"2545698", `
"2529073"

$WsusServers = `
"wsusserver01", `
"wsusserver02", `
"wsusserver03", `
"wsusserver04", `
"wsusserver05"

$Log = "$env:USERPROFILE\SearchedUpdates.txt"

[reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration") | out-null
$approveState = 'Microsoft.UpdateServices.Administration.ApprovedStates' -as [type]

foreach ($WsusServer in $WsusServers)
{
$finalCount = $null
write-host "Searching for updates on $WsusServer" -ForegroundColor Green
write-output "############################################" | Out-File - FilePath $Log -Append
write-output "## Searching for updates on $WsusServer ##" | Out-File -FilePath $Log -Append
write-output "############################################" | Out-File -FilePath $Log -Append
$new = $null
$new = @()
foreach ($update in $updates)
{
$Count = $null
write-host " Searching for kb$update" | Out-File -FilePath $Log -Append
$wsus = Get-WSUSServer -Name $WsusServer -PortNumber 8530
$updateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope -Property @{
TextIncludes = "$update"
ApprovedStates = $approveState::Any
}
$UpdateList = $wsus.GetUpdates($updateScope)
If ($UpdateList -ne $null)
{
$Count = $UpdateList.Count
$finalcount += $Count
write-host " Update found" -ForegroundColor DarkGray
write-output "KB$Update found. There are $count updates with this KB." | Out-File -FilePath $Log -Append
$new += $wsus.GetUpdates($updateScope) | Select Title,IsLatestRevision,IsSuperseded,CreationDate,IsApproved,IsDeclined
}
Else
{
write-host " Update not found" -ForegroundColor Red
write-output "KB$Update NOT found" | Out-File -FilePath $Log -Append
}
}
Write-Output "Found $finalcount updates in total" | Out-File -FilePath $Log -Append
Write-Output " " | Out-File -FilePath $Log -Append
$new | ft -AutoSize | Out-String -Width 4096 | Out-File -FilePath $Log -Append
Write-Output " " | Out-File -FilePath $Log -Append
}
Invoke-Item $Log

You can also output to html if you prefer:


<#

This script will search for all the updates in the $Updates variable on each WSUS server in the $WSUSservers variable, and report their approval status.
It logs to the console and more detailed logging in an html page.

#>
# This update list is the one from http://support.microsoft.com/kb/2894518 which lists software updates that cause multiple reboots and kills the WSUS step in the OSD Task Sequence
$Updates = `
"2984976", `
"2981685", `
"2966034", `
"2965788", `
"2920189", `
"2871777", `
"2871690", `
"2862330", `
"2771431", `
"2821895", `
"2545698", `
"2529073"

$WsusServers = `
"wsusserver01", `
"wsusserver02", `
"wsusserver03", `
"wsusserver04", `
"wsusserver05"

$Log = "$env:USERPROFILE\SearchedUpdates.html"
$html = ""

[reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration") | out-null
$approveState = 'Microsoft.UpdateServices.Administration.ApprovedStates' -as [type]

foreach ($WsusServer in $WsusServers)
{
$finalCount = $null
write-host "Searching for updates on $WsusServer" -ForegroundColor Green
$html += write-output "#####################################"
$html += "<br>"
$html += write-output "## Searching for updates on $WsusServer ##"
$html += "<br>"
$html += write-output "#####################################"
$html += "<br><br>"
$new = $null
$new = @()
foreach ($update in $updates)
{
$Count = $null
write-host " Searching for kb$update" | Out-File -FilePath $Log -Append
$wsus = Get-WSUSServer -Name $WsusServer -PortNumber 8530
$updateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope -Property @{
TextIncludes = "$update"
ApprovedStates = $approveState::Any
}
$UpdateList = $wsus.GetUpdates($updateScope)
If ($UpdateList -ne $null)
{
$Count = $UpdateList.Count
$finalcount += $Count
write-host " Update found" -ForegroundColor DarkGray
$html += write-output "KB$Update found. There are $count updates with this KB."
$html += "<br>"
$new += $wsus.GetUpdates($updateScope) | Select Title,IsLatestRevision,IsSuperseded,CreationDate,IsApproved,IsDeclined
}
Else
{
write-host " Update not found" -ForegroundColor Red
$html += write-output "KB$Update NOT found" | ConvertTo-Html | Out-File -FilePath $Log -Append
$html += "<br>"
}
}
$html += "<br>"
$html += write-output "Found $finalcount updates in total"
$html += "<br>"
$new = $new | ConvertTo-Html
$html += $new
$html += "<br><br>"
}

$html | Out-File $log
invoke-item $log

Declining ‘multiple-reboot’ patches in WSUS

Again, logging is done in brief to the console, and in more detail to a log file which shows you each patch that was declined.


<#

This script will decline all updates with the KB number listed in the $Updates variable on each WSUS server in the $WSUSservers variable.
It logs to the console and more detailed logging to a log file.

#>
# This update list is the one from http://support.microsoft.com/kb/2894518 which lists software updates that cause multiple reboots and kills the WSUS step in the OSD Task Sequence
$Updates = `
"2984976", `
"2981685", `
"2966034", `
"2965788", `
"2920189", `
"2871777", `
"2871690", `
"2862330", `
"2771431", `
"2821895", `
"2545698", `
"2529073"

$WsusServers = `
"wsusserver01", `
"wsusserver02", `
"wsusserver03", `
"wsusserver04", `
"wsusserver05"

$Log = "$env:USERPROFILE\DeclinedUpdates.log"

[reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration") | out-null
$approveState = 'Microsoft.UpdateServices.Administration.ApprovedStates' -as [type]

foreach ($WsusServer in $WsusServers)
{
$finalCount = $null
$wsus = Get-WSUSServer -Name $WsusServer -PortNumber 8530
write-host "Declining Unwanted Updates on $WsusServer" -ForegroundColor Green
write-output "#############################################" | Out-File $Log -Append
write-output "## DECLINING UNWANTED UPDATES ON $WsusServer ##" | Out-File $Log -Append
write-output "#############################################" | Out-File $Log -Append
foreach ($Update in $Updates)
{
$updateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope -Property @{
TextIncludes = "$update"
ApprovedStates = $approveState::Any
}
$UpdateList = $wsus.GetUpdates($updateScope)
$Count = $UpdateList.Count
write-output "Declining $Count updates for KB$Update" | Out-File $Log -Append
write-host " KB$Update" -NoNewline
$UpdateList | ForEach {
Write-Output (" Declining {0}" -f $_.Title) -Verbose | Out-File $Log -Append
Write-host "." -NoNewline
$_.Decline()
}
write-host " "
$finalCount += $Count
}
write-output ">>Declined a total of $finalCount updates for $WsusServer<<" | Out-File $Log -Append
write-output " " | Out-File $Log -Append
}

Invoke-Item $Log

Monitoring Disk Space and Content Directories on ConfigMgr Distribution Points

Recently I noticed that the available disk space on my ConfigMgr distribution points was getting low.  Since they are mostly VMs using SAN storage it’s fairly easy to increase the disk space, but it got me thinking about how I could monitor the DPs for available free space, as well as the sizes of the key directories that ConfigMgr is using to store large amounts of data, including the SCCMContentLib, the legacy package share (SMSPKGx$), and in my case, the WSUSUpdates folder, as I also use our DPs as WSUS servers for patching during OSD.  These directories will certainly increase in size over time, as new content is distributed and new patches are downloaded.

There are, of course, many ways to monitor the overall free disk space, including WMI, SNMP, various 3rd party software, your corporate monitoring tool and even ConfigMgr itself.  But monitoring the ConfigMgr/WSUS directory sizes can be a little trickier.  Rather than purchase some new software to do it, I wanted to build a ‘free’ solution using tools already available to me in the Enterprise.  I also wanted to have all this data for all our distributions points displayed graphically in a report.

Since Microsoft Excel is my favourite reporting tool, I decided to build a workbook that has a data connection to a SQL database where I will store the disk and directory information from the DPs.  Then I run a PowerShell script on each of the DPs as a scheduled task that will get the information I need and insert it into the SQL database.

The result is a nice graphical report that displays all the data I want and keeps itself up-to-date automatically.  You can download a sample report here.

BigGraph

AvailableSpace

SCCMContentLib

Here is a step-by-step guide to create such a report.

Create a SQL Database and Table

First, we will create an SQL database where we will store this data.  You could, of course, use an existing database as we only need a single table to store this data, but just for good organisation I will create a new one for this purpose. Here is a sql script that will create the database, create a table, and create records for each of my distribution points with blank data values for now.  Edit the script with the hostnames of your distribution points, and run the script in your SQL instance.


CREATE DATABASE SCCM_Server_Data
GO
USE SCCM_Server_Data;
CREATE TABLE DiskSpace (
ID INT NOT NULL,
Server VARCHAR (20),
WSUS_Updates DECIMAL(6,2),
SMSPKGx DECIMAL(6,2),
SCCMContentLib DECIMAL(6,2),
DiskCapacity DECIMAL(6,2),
AvailableSpace DECIMAL(6,2),
PRIMARY KEY (ID)
);

INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (1, 'SCCMDP-01', 0, 0, 0, 0, 0 );

INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (2, 'SCCMDP-02', 0, 0, 0, 0, 0 );

INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (3, 'SCCMDP-03', 0, 0, 0, 0, 0 );

INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (4, 'SCCMDP-04', 0, 0, 0, 0, 0 );

INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (5, 'SCCMDP-05', 0, 0, 0, 0, 0 );

INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (6, 'SCCMDP-06', 0, 0, 0, 0, 0 );

INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (7, 'SCCMDP-07', 0, 0, 0, 0, 0 );

INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (8, 'SCCMDP-08', 0, 0, 0, 0, 0 );

INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (9, 'SCCMDP-09', 0, 0, 0, 0, 0 );

INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (10, 'SCCMDP-10', 0, 0, 0, 0, 0 );

INSERT INTO DiskSpace (ID,Server,WSUS_Updates,SMSPKGx,SCCMContentLib,DiskCapacity,AvailableSpace)
VALUES (11, 'SCCMDP-11', 0, 0, 0, 0, 0 );

SQL Permissions

I recommend to create an SQL login to the database you created that has only ‘db_datareader’ permission.  This way you can include the credentials required to get data from the database in the report, and not have to give SQL database permissions to everyone you want to share the report with.  If your SQL Instance is not using Mixed-mode authentication, you can easily change this.  I created a SQL login called ‘ConfigMgrDB_Read’ for this purpose (I use the same login to access my ConfigMgr database for reporting purposes).

Note: The SQL credentials will be stored in clear text in the Excel data connection file, but since they are read-only permission, it shouldn’t be a security concern.

Create a PowerShell Script and Scheduled Task on each DP

Next, we need to create a PowerShell script that will get the directory sizes of the following locations:

  • SCCMContentLib (SCCM Content Library)
  • SMSPKGx$ (SCCM Legacy Package Share)
  • WSUS_Updates (WSUS Update files location)

We will also get the disk capacity and amount of free space remaining.

We will put this data into a simple formatted text table that can be sent in an email.  We will then create a connection to the SQL database where we will store this data, and update the records with the new data values.

I recommend to run this script as a scheduled task on each DP, rather than running it against multiple DPs from one location, especially if your DPs are across a WAN, as the Get-ChildItem commands run much quicker locally than remotely.

The account running this script must have permission to input data to the SQL database.

You will need to change some things in the script for your environment:

  • Line 4: Enter the hostname of the server you are running the script on and for
  • Lines 7-11: Enter the email details
  • Lines 16,19,22: Enter the locations of the directories you want to get the size for
  • Line 25: Change the drive letter for your DP data drive
  • Lines 47,48: Enter the SQL Instance and database name

## Variables

# Enter ComputerName
$ComputerName = "SCCMDP-01"

# Enter email details
$SendEmail = "Yes"
$smtpServer = "mysmtpserver@mydomain.com"
$smtpFrom = "$ComputerName@mydomain.com"
$smtpTo = "MyRecipients@mydomain.com"
$messageSubject = "Drive Space Report for $ComputerName"

## Get and email the data

# Get drive and directory data
$colItems = (Get-ChildItem G:\WSUS_Updates -recurse | Measure-Object -property length -sum)
$WSUS_Updates = "{0:N2}" -f ($colItems.sum / 1GB)

$colItems = (Get-ChildItem G:\SMSPKGG$ -recurse | Measure-Object -property length -sum)
$SMSPKGG = "{0:N2}" -f ($colItems.sum / 1GB)

$colItems = (Get-ChildItem G:\SCCMContentLib -recurse | Measure-Object -property length -sum)
$SCCMContentLib = "{0:N2}" -f ($colItems.sum / 1GB)

$data = Get-WmiObject -Class Win32_Volume -ComputerName $ComputerName | Where-Object {$_.DriveLetter -eq 'G:'} | Select Capacity,FreeSpace,SystemName,DriveLetter

$capacity = "{0:N2}" -f ($data.capacity / 1GB)
$freespace = "{0:N2}" -f ($data.freespace / 1GB)
$systemname = $data.SystemName
$driveletter = $data.DriveLetter

# Add data to a simple text 'table'
$table = "All values in GB`nServer:`t`t`t$systemname`nWSUS_Updates:`t$WSUS_Updates`nSMSPKGx:`t`t$SMSPKGG`nSCCMContentLib:`t$SCCMContentLib`nDiskCapacity:`t`t$capacity`nAvailableSpace:`t`t$freespace"

# Send data as an email
if ($SendEmail -eq "Yes")
{
$message = New-Object System.Net.Mail.MailMessage $smtpfrom, $smtpto
$message.Subject = $messageSubject
$message.Body = $table
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($message)
}
# SQL Stuff

# Database info
$dataSource = “MySQLServer\INST_SCCM”
$database = “SCCM_Server_Data”

# Open a connection
cls
Write-host "Opening a connection to '$database' on '$dataSource'"
$connectionString = “Server=$dataSource;Database=$database;Integrated Security=SSPI;”
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

# Set and run the SQL Command
$update = "UPDATE DiskSpace SET WSUS_Updates = '$WSUS_Updates', SMSPKGx = '$SMSPKGG', SCCMContentLib = '$SCCMContentLib', DiskCapacity = '$capacity', AvailableSpace = '$freespace' WHERE SERVER = '$ComputerName'"
$command = $connection.CreateCommand()
$command.CommandText = $update
$command.ExecuteNonQuery()

# Close the connection
$connection.Close()

Note: if you don’t want to receive the email from each DP, simply put ‘No’ in the $SendEmail variable, although it’s a useful way to verify that the Scheduled Task has run.

Now create a scheduled task on the DP with the schedule of your choice and point to the location of your Powershell script:

  • Action: Start a program
  • Program/script: Powershell
  • Add arguments: -Command “C:\LocalScripts\DP-DiskSpaceScript.ps1” -ExecutionPolicy Bypass

You can manually start the task to verify that it works, you should receive an email like below, and data should be entered into the SQL table in the record for that server.  In this case, I now have lots of free space.  Nice 🙂

Capture

Create an Excel Workbook

Create the Data Connection

Open a new blank workbook in Microsoft Excel (I’m using Office 2013), right-click the tab for Sheet1 and rename it Data.

On the Data ribbon, in the Get External Data section, click From Other Sources and choose From SQL Server.

In the Data Connection Wizard, enter the name of the Instance that contains your database, and enter the SQL account credentials.  Click Next.

DataConWizard

Select the SCCM_Server_Data database from the drop-down list, then simply click next.  The database only contains one table, and we will pull the entire table into the workbook, so no need for a specific SQL query.

DataCon2

Rename the File Name and Friendly Name of the data connection file if you wish, then click Finish.

DataCon3

In the Import Data window, make sure to choose Table and the cell range =$A$1 in the existing worksheet, then click Ok.

DataCon4

You will now see the data from the database in the worksheet:

DataTab

Finally, go to the Data ribbon again and click Connections.  Make sure your data connection is selected, the click Properties.  Check the option to Refresh data when opening the file.  This will make sure that you always get the current data from the database every time the file is opened.

DataCon5

On the Definition tab, check the box Save password, click Yes at the prompt to save without encryption, then click OK to save and close the Connection Properties.

DataCon6

Close the Workbook Connections window.

Create a Summary Chart

Now that we have the data, let’s create some nice charts to display it.

First, select all the populated cells in the Data sheet, except for the ‘ID’ column, then right-click the Data tab and choose Insert…  Choose Chart and click Ok.

You should see a chart automatically added for you.  Let’s make it look a bit nicer.  (These customizations are just my own suggestions, of course.  Do what you like :-))

SummaryChart1

Rename the Chart tab to Disk Space Summary.

Click on the Chart, then choose Change Chart Type from the Chart ribbon.  In the Column charts, select 3D Column and click Ok.

Double-click the chart title and change it to SCCM DP Disk Space Summary

Cool, looking better to my eye.

SummaryChart2

Create Additional Charts

Now let’s create some additional charts so we can compare the data on their own scales.

Go back to the Data sheet and select all the populated cells in the Server column, and all the populated cells in the WSUSUpdates column, including the headers.

WSUSUPdates

Right-click on the Data tab and choose Insert…  Choose Chart and click Ok.  Change the chart type again, this time choosing 3D Clustered Column.

Rename the chart tab to WSUSUpdates.

Job done.

WSUSUPdates2

Now do the same for each of the remaining data columns in the Data sheet, selecting the populated cells in the Server column, and the next data column, and adding charts.

There you have it.  Now you can keep your eye on those SCCM DPs, know the sizes of the key content directories, observe how they are growing over time, and make sure there is plenty of free disk space available!

Note: you could also create an SSRS report, if you prefer, with the SQL data and add the report to your existing ConfigMgr reports folder.

!Tip

If you want the most up-to-date data in your report, then you can manually trigger all the scheduled tasks to run immediately using PowerShell.  If you have PowerShell remoting available, you can run the following script which will trigger the Task on all your distribution points.

You need to enter your DP names in the $server variable, and it assumes that your scheduled tasks all have the same name on each DP.


<#

This script manually triggers the 'DriveSpaceReport' Scheduled Task on each of the DPs listed in the $servers variable

#>

$servers = "sccmsrvdp-01", `
"sccmsrvdp-02, `
"sccmsrvdp-03", `
"sccmsrvdp-04", `
"sccmsrvdp-05", `
"sccmsrvdp-06", `
"sccmsrvdp-07", `
"sccmsrvdp-08", `
"sccmsrvdp-09", `
"sccmsrvdp-10", `
"sccmsrvdp-11", `
"sccmsrvdp-12"

cls
write-host "######################################################"
write-host "Trigger the DriveSpaceReport Scheduled Task on all DPs"
write-host "######################################################"
write-host ""

# Trigger the Scheduled tasks

write-host "Get task status and trigger the task for each server" -ForegroundColor Green
write-host ""

foreach ($Server in $servers)
{
write-host "Processing $server" -ForegroundColor Yellow
Write-Host "Getting Scheduled Task Details for 'DriveSpaceReport'" -ForegroundColor Cyan
schtasks /query /S $server /TN DriveSpaceReport /FO List
Write-Host ""
Write-Host "Run the task..." -ForegroundColor Cyan
schtasks /run /S $server /TN DriveSpaceReport /I
Write-Host ""
}

# Report task status

Write-Host ""
write-host "Get updated task status for each server" -ForegroundColor Green

foreach ($Server in $servers)
{
Write-Host ""
$Task = schtasks /query /S $server /TN DriveSpaceReport /V /FO CSV | ConvertFrom-Csv
$Name = $Task.TaskName
$Status = $Task.Status
$LastRunTime = $Task.'Last Run Time'
$Result = $Task.'Last Result'
Write-host $server -ForegroundColor Cyan
Write-host "TaskName: $Name"
Write-host "Status: $Status"
write-host "Last Run Time: $LastRunTime"
Write-host "Last Result: $Result"
}

Installing and Configuring WSUS with Powershell

In setting up our SCCM 2012 infrastructure, I decided to patch our OS deployments using WSUS instead of SCCM Software Updates.  Since we have multiple distribution points in different geographical areas, I decided to install a WSUS server in each location where we do deployments.  Granted, installing and configuring WSUS is not the most technically challenging thing in the world, but when you have to do it multiple times, it begs for automation!  So I fired up my trusty Powershell ISE to see what could be done.

I wrote this script for my own environment, but it should be flexible enough to be used by anyone.  It’s tested for use on Windows Server 2012 R2 and It’s designed to run in Powershell ISE, so it doesn’t take any parameters, just set the variables as required.  Also you can change any of the WSUS configuration, such as Products and Classifications, just edit the relevant section of the script.

Download from Technet Gallery.

What does the script do?

First, we install .Net Framework 3.5 if it isn’t already installed, this is a requirement for WSUS.  Next, we download and install Microsoft Report Viewer 2008 SP1, which is required for viewing WSUS reports.  If you chose the ‘SQLExpress’ installation, we download SQL Server 2012 Express SP1 with tools and run an unattended installation using default parameters.  Then we install WSUS and run the post-installation tasks with wsusutil.exe.

Now, we do a basic configuration, which is equivalent to running the WSUS Configuration Wizard.  We set the location to sync updates from, the update language/s, run a metadata sync to get available Products and Classifications, set which Products and Classifications we want to sync, and enable the automatic sync schedule.  Then we do a full sync.

Once the sync is completed, we decline certain updates that we don’t want, such as all ‘itanium’ updates, configure and enable the Default Automatic Approval Rule, then run it so the updates will start downloading.

Most of these activities are optional and are activated using variables which you must set before you run the script, so if you want to use WID, or an existing SQL instance you can.  You can skip the configuration entirely and do it manually, or just do the bare minimum, and of course you can customise the configuration in the script.

Step by Step Walkthrough

First, we set the variables, such as the WSUS installation type, the location for Updates, things to configure etc.


###############
## Variables ##
###############

##//INSTALLATION//##

# Do you want to install .NET FRAMEWORK 3.5? If true, provide a location for the Windows OS media in the next variable
    $DotNet = $True
# Location of Windows sxs for .Net Framework 3.5 installation
    $WindowsSXS = "D:\sources\sxs"
# Do you want to download and install MS Report Viewer 2008 SP1 (required for WSUS Reports)?
    $RepViewer = $True
# WSUS Installation Type.  Enter "WID" (for WIndows Internal Database), "SQLExpress" (to download and install a local SQLExpress), or "SQLRemote" (for an existing SQL Instance).
    $WSUSType = "SQLRemote"
# If using an existing SQL server, provide the Instance name below
    $SQLInstance = "MyServer\MyInstance"
# Location to store WSUS Updates (will be created if doesn't exist)
    $WSUSDir = "C:\WSUS_Updates"
# Temporary location for installation files (will be created if doesn't exist)
    $TempDir = "C:\temp"

##//CONFIGURATION//##

# Do you want to configure WSUS (equivalent of WSUS Configuration Wizard, plus some additional options)?  If $false, no further variables apply.
# You can customise the configurations, such as Products and Classifications etc, in the "Begin Initial Configuration of WSUS" section of the script.
    $ConfigureWSUS = $True
# Do you want to decline some unwanted updates?
    $DeclineUpdates = $True
# Do you want to configure and enable the Default Approval Rule?
    $DefaultApproval = $True
# Do you want to run the Default Approval Rule after configuring?
    $RunDefaultRule = $False

We install .Net Framework 3.5 if required


# Install .Net Framework 3.5 from media
if($DotNet -eq $true)
{
write-host 'Installing .Net Framework 3.5'
Install-WindowsFeature -name NET-Framework-Core -Source $WindowsSXS
}

We install the Report Viewer from Microsoft for viewing WSUS reports. We start a bits job to download it, the we do a silent install.

# Download MS Report Viewer 2008 SP1 for WSUS reports

if ($RepViewer -eq $True)
{
write-host "Downloading Microsoft Report Viewer 2008 SP1...please wait"
$URL = "http://download.microsoft.com/download/3/a/e/3aeb7a63-ade6-48c2-9b6a-d3b6bed17fe9/ReportViewer.exe"
Start-BitsTransfer $URL $TempDir -RetryInterval 60 -RetryTimeout 180 -ErrorVariable err
if ($err)
{
write-host "Microsoft Report Viewer 2008 SP1 could not be downloaded!" -ForegroundColor Red
write-host 'Please download and install it manually to use WSUS Reports.' -ForegroundColor Red
write-host 'Continuing anyway...' -ForegroundColor Magenta
}

# Install MS Report Viewer 2008 SP1

write-host 'Installing Microsoft Report Viewer 2008 SP1...'
$setup=Start-Process "$TempDir\ReportViewer.exe" -verb RunAs -ArgumentList '/q' -Wait -PassThru
if ($setup.exitcode -eq 0)
{
write-host "Successfully installed"
}
else
{
write-host 'Microsoft Report Viewer 2008 SP1 did not install correctly.' -ForegroundColor Red
write-host 'Please download and install it manually to use WSUS Reports.' -ForegroundColor Red
write-host 'Continuing anyway...' -ForegroundColor Magenta
}
}

I prefer to use WSUS with a local SQL Express installation so I have some access to the database if I need to.  If chosen, we download and install SQL Server Express 2012 SP1 with admin tools using an unattended installation. We use the ‘ALLFEATURES_WITHDEFAULTS’ role, and add the local administrators group to the SQL sysadmin accounts.

# Download SQL 2012 Express SP1 with tools

if ($WSUSType -eq 'SQLExpress')
{
write-host "Downloading SQL 2012 Express SP1 with Tools...please wait"
Start-Sleep -Seconds 10 # wait 10 seconds in case of BITS overload error
$URL = "http://download.microsoft.com/download/5/2/9/529FEF7B-2EFB-439E-A2D1-A1533227CD69/SQLEXPRWT_x64_ENU.exe"
Start-BitsTransfer $URL $TempDir -RetryInterval 60 -RetryTimeout 180 -ErrorVariable err
if ($err)
{
write-host "Microsoft SQL 2012 Express SP1 could not be downloaded!  Please check internet availability." -ForegroundColor Red
write-host 'The script will stop now.' -ForegroundColor Red
break
}

# Install SQL 2012 Express with defaults

write-host 'Installing SQL Server 2012 SP1 Express with Tools...'
$setup=Start-Process "$TempDir\SQLEXPRWT_x64_ENU.exe" -verb RunAs -ArgumentList '/QUIETSIMPLE /IACCEPTSQLSERVERLICENSETERMS /ACTION=INSTALL /ROLE=ALLFEATURES_WITHDEFAULTS /INSTANCENAME=SQLEXPRESS /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /UPDATEENABLED=TRUE /UPDATESOURCE="MU"' -Wait -PassThru

if ($setup.exitcode -eq 0)
{
write-host "Successfully installed"
}
else
{
write-host 'SQL Server 2012 SP1 Express did not install correctly.' -ForegroundColor Red
write-host 'Please check the Summary.txt log at C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log' -ForegroundColor Red
write-host 'The script will stop now.' -ForegroundColor Red
break
}
}

Next we install WSUS

# Install WSUS (WSUS Services, SQL Database, Management tools)

if ($WSUSType -eq 'WID')
{
write-host 'Installing WSUS for WID (Windows Internal Database)'
Install-WindowsFeature -Name UpdateServices -IncludeManagementTools
}
if ($WSUSType -eq 'SQLExpress' -Or $WSUSType -eq 'SQLRemote')
{
write-host 'Installing WSUS for SQL Database'
Install-WindowsFeature -Name UpdateServices-Services,UpdateServices-DB -IncludeManagementTools
}

Then we run the post-install configuration tasks using the wsusutil.exe

# Run WSUS Post-Configuration

if ($WSUSType -eq 'WID')
{
sl "C:\Program Files\Update Services\Tools"
.\wsusutil.exe postinstall CONTENT_DIR=$WSUSDir
}
if ($WSUSType -eq 'SQLExpress')
{
sl "C:\Program Files\Update Services\Tools"
.\wsusutil.exe postinstall SQL_INSTANCE_NAME="%COMPUTERNAME%\SQLEXPRESS" CONTENT_DIR=$WSUSDir
}
if ($WSUSType -eq 'SQLRemote')
{
sl "C:\Program Files\Update Services\Tools"
.\wsusutil.exe postinstall SQL_INSTANCE_NAME=$SQLInstance CONTENT_DIR=$WSUSDir
}

Now we begin to configure WSUS. We connect to the WSUS server and get the configuration. We tell it to sync from Microsoft Update, then set the updates language to English.

# Get WSUS Server Object
$wsus = Get-WSUSServer

# Connect to WSUS server configuration
$wsusConfig = $wsus.GetConfiguration()

# Set to download updates from Microsoft Updates
Set-WsusServerSynchronization –SyncFromMU

# Set Update Languages to English and save configuration settings
$wsusConfig.AllUpdateLanguagesEnabled = $false
$wsusConfig.SetEnabledUpdateLanguages("en")
$wsusConfig.Save()

We do an initial sync to get the available products and categories from Microsoft Update

# Get WSUS Subscription and perform initial synchronization to get latest categories
$subscription = $wsus.GetSubscription()
$subscription.StartSynchronizationForCategoryOnly()
write-host 'Beginning first WSUS Sync to get available Products etc' -ForegroundColor Magenta
write-host 'Will take some time to complete'
While ($subscription.GetSynchronizationStatus() -ne 'NotProcessing') {
    Write-Host "." -NoNewline
    Start-Sleep -Seconds 5
}
write-host ' '
Write-Host "Sync is done." -ForegroundColor Green

We tell WSUS which Products we want to sync. It’s very important to get these right, otherwise you will download a lot of updates that you don’t need and fill up your disk space! Obviously you’ll want to customise these for your environment.

# Configure the Platforms that we want WSUS to receive updates
write-host 'Setting WSUS Products'
Get-WsusProduct | where-Object {
    $_.Product.Title -in (
    'Report Viewer 2005',
    'Report Viewer 2008',
    'Report Viewer 2010',
    'Visual Studio 2005',
    'Visual Studio 2008',
    'Visual Studio 2010 Tools for Office Runtime',
    'Visual Studio 2010',
    'Visual Studio 2012',
    'Visual Studio 2013',
    'Microsoft Lync 2010',
    'Microsoft SQL Server 2008 R2 - PowerPivot for Microsoft Excel 2010',
    'Dictionary Updates for Microsoft IMEs',
    'New Dictionaries for Microsoft IMEs',
    'Office 2003',
    'Office 2010',
    'Office 2013',
    'Silverlight',
    'System Center 2012 - Orchestrator',
    'Windows 7',
    'Windows 8.1 Drivers',
    'Windows 8.1 Dynamic Update',
    'Windows 8',
    'Windows Dictionary Updates',
    'Windows Server 2008 R2',
    'Windows Server 2008',
    'Windows Server 2012 R2',
    'Windows Server 2012',
    'Windows XP 64-Bit Edition Version 2003',
    'Windows XP x64 Edition',
    'Windows XP')
} | Set-WsusProduct

We do the same for the Update Classifications

# Configure the Classifications
write-host 'Setting WSUS Classifications'
Get-WsusClassification | Where-Object {
    $_.Classification.Title -in (
    'Critical Updates',
    'Definition Updates',
    'Feature Packs',
    'Security Updates',
    'Service Packs',
    'Update Rollups',
    'Updates')
} | Set-WsusClassification

I guess it’s a bug, but it seems WSUS sometimes enables the entire parent Product when adding them by script this way, so we pause the script and prompt to check in the WSUS console that the correct Products are selected before continuing.

# Prompt to check products are set correctly
write-host 'Before continuing, please open the WSUS Console, cancel the WSUS Configuration Wizard,' - -ForegroundColor Red
write-host 'Go to Options > Products and Classifications, and check that the Products are set correctly.' - -ForegroundColor Red
write-host 'Pausing script' -ForegroundColor Yellow
$Shell = New-Object -ComObject "WScript.Shell"
$Button = $Shell.Popup("Click OK to continue.", 0, "Script Paused", 0) # Using Pop-up in case script is running in ISE

We set the automatic sync schedule to once per day at midnight, then start the first full synchronisation.

# Configure Synchronizations
write-host 'Enabling WSUS Automatic Synchronisation'
$subscription.SynchronizeAutomatically=$true

# Set synchronization scheduled for midnight each night
$subscription.SynchronizeAutomaticallyTimeOfDay= (New-TimeSpan -Hours 0)
$subscription.NumberOfSynchronizationsPerDay=1
$subscription.Save()

# Kick off a synchronization
$subscription.StartSynchronization()

We monitor the progress of the sync in the Powershell console as it can take some time.

# Monitor Progress of Synchronisation

write-host 'Starting WSUS Sync, will take some time' -ForegroundColor Magenta
Start-Sleep -Seconds 60 # Wait for sync to start before monitoring
while ($subscription.GetSynchronizationProgress().ProcessedItems -ne $subscription.GetSynchronizationProgress().TotalItems) {
    Write-Progress -PercentComplete (
    $subscription.GetSynchronizationProgress().ProcessedItems*100/($subscription.GetSynchronizationProgress().TotalItems)
    ) -Activity "WSUS Sync Progress"
}
Write-Host "Sync is done." -ForegroundColor Green

After the sync is complete, we decline some updates that we don’t want. In my example, we are declining IE10 and the Microsoft Browser Choice EU updates, which we don’t want (I used the KB article number in the ‘TextIncludes’ parameter to find them), then we decline all ‘itanium’ updates because we don’t have any itanium servers. Do you?

# Decline Unwanted Updates

if ($DeclineUpdates -eq $True)
{
write-host 'Declining Unwanted Updates'
$approveState = 'Microsoft.UpdateServices.Administration.ApprovedStates' -as [type]

# Declining All Internet Explorer 10
$updateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope -Property @{
    TextIncludes = '2718695'
    ApprovedStates = $approveState::Any
}
$wsus.GetUpdates($updateScope) | ForEach {
    Write-Verbose ("Declining {0}" -f $_.Title) -Verbose
    $_.Decline()
}

# Declining Microsoft Browser Choice EU
$updateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope -Property @{
    TextIncludes = '976002'
    ApprovedStates = $approveState::Any
}
$wsus.GetUpdates($updateScope) | ForEach {
    Write-Verbose ("Declining {0}" -f $_.Title) -Verbose
    $_.Decline()
}

# Declining all Itanium Update
$updateScope = New-Object Microsoft.UpdateServices.Administration.UpdateScope -Property @{
    TextIncludes = 'itanium'
    ApprovedStates = $approveState::Any
}
$wsus.GetUpdates($updateScope) | ForEach {
    Write-Verbose ("Declining {0}" -f $_.Title) -Verbose
    $_.Decline()
}
}

Then we enable the Default Automatic Approval Rule and configure it with the classifications we want.

# Configure Default Approval Rule

if ($DefaultApproval -eq $True)
{
write-host 'Configuring default automatic approval rule'
[void][reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration")
$rule = $wsus.GetInstallApprovalRules() | Where {
    $_.Name -eq "Default Automatic Approval Rule"}
$class = $wsus.GetUpdateClassifications() | ? {$_.Title -In (
    'Critical Updates',
    'Definition Updates',
    'Feature Packs',
    'Security Updates',
    'Service Packs',
    'Update Rollups',
    'Updates')}
$class_coll = New-Object Microsoft.UpdateServices.Administration.UpdateClassificationCollection
$class_coll.AddRange($class)
$rule.SetUpdateClassifications($class_coll)
$rule.Enabled = $True
$rule.Save()
}

Finally we run the rule which will approve the updates and begin the file downloads. However, in my testing this always errors with a timeout when activated through Powershell, so I put it in a try-catch-finally block to finish the script successfully. Even if it errors, the rule is actually run as you will be able to see from the WSUS console.


# Run Default Approval Rule

if ($RunDefaultRule -eq $True)
{
write-host 'Running Default Approval Rule'
write-host ' >This step may timeout, but the rule will be applied and the script will continue' -ForegroundColor Yellow
try {
$Apply = $rule.ApplyRule()
}
catch {
write-warning $_
}
Finally {
# Cleaning Up TempDir

write-host 'Cleaning temp directory'
if (Test-Path $TempDir\ReportViewer.exe)
{Remove-Item $TempDir\ReportViewer.exe -Force}
if (Test-Path $TempDir\SQLEXPRWT_x64_ENU.exe)
{Remove-Item $TempDir\SQLEXPRWT_x64_ENU.exe -Force}
If ($Tempfolder -eq "No")
{Remove-Item $TempDir -Force}

write-host 'WSUS log files can be found here: %ProgramFiles%\Update Services\LogFiles'
write-host 'Done!' -foregroundcolor Green
}
}

Monitoring the Update File Downloads

After the Default Approval Rule has been run, you can monitor the ‘Download Status’ of the update files in the WSUS console.  But since it can take a long time, I wrote a little script that will monitor the downloads and email me once they have finished.  It must be run as administrator on the WSUS server.


$Computername = $env:COMPUTERNAME
$ToEmail = "myemailaddress@mydomain.com"
$FromEmail = "WSUS.on.$Computername@mydomain.com"
$smtpServer = "mysmtpServer"
# Polling frequency in seconds
$Seconds = "320"

cls
Write-host 'Monitoring WSUS Update File Downloads...'
write-host 'Will send email when completed.'
[reflection.assembly]::LoadWithPartialName("Microsoft.UpdateServices.Administration") | out-null
$wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::GetUpdateServer();
$updateScope = new-object Microsoft.UpdateServices.Administration.UpdateScope;
$updateScope.updateApprovalActions = [Microsoft.UpdateServices.Administration.UpdateApprovalActions]::Install
while (($wsus.GetUpdates($updateScope) | Where {$_.State -eq "NotReady"}).Count -ne 0) {
Start-Sleep -Seconds $Seconds
}
send-mailmessage -To $ToEmail -From $FromEmail -Subject "WSUS Update File Download Completed on $ComputerName" -body "Download of Update Files on $Computername has completed!" -smtpServer $smtpServer

Email Notifications

Finally, if you configure E-mail Notifications in WSUS, you may hit the lovely 5.7.1 error from Exchange:

Mailbox unavailable. The server response was: 5.7.1 Client does not have permissions to send as this sender 

This is because it tries to authenticate with its computer account.  So you have to create a new Receive Connector in Exchange to allow relaying from anonymous users with TLS-authentication to work around the problem.

You can run a command like the following to create it:


New-ReceiveConnector -Name "WSUS Relay" -Bindings 0.0.0.0:25 -RemoteIPRanges 10.x.x.1.,10.x.x.2 -AuthMechanism Tls -Enabled
 $true -PermissionGroups AnonymousUsers -Server MyEdgeServer

Incidentally, you can’t really configure E-mail Notifications with Powershell as you must set the recipient email address for it to work, and this is a read-only property that Powershell can’t change, so better to do it manually.

That’s it!  Feel free to suggest some improvements, or take the code and make something better yourself!

Most of the WSUS code I learned from these great resources, especially the work of Boe Prox

http://blogs.technet.com/b/heyscriptingguy/archive/2013/04/15/installing-wsus-on-windows-server-2012.aspx
http://learn-powershell.net/2010/11/14/wsus-administrator-module/
http://p0w3rsh3ll.wordpress.com/2013/02/05/wsus-on-windows-server-2012-core-from-scratch/
http://community.spiceworks.com/attachments/post/0006/1234/powershell_wsus.ps1
http://learn-powershell.net/2013/11/12/automatically-declining-itanium-updates-in-wsus-using-powershell/
http://poshwsus.codeplex.com/