Windows 10 Feature Update Readiness PowerBI Report (MEMCM version)

Following on from my previous post where I shared a PowerBI report that provides information on Windows 10 feature update blocks using Update Compliance and Desktop Analytics, in this post I will share another report that exposes similar data, but this time built from custom hardware inventory data in MEMCM.

Outside of the Windows setup process, feature update compatibility is assessed on a Windows 10 device by the ‘Microsoft Compatibility Appraiser’ – a scheduled task that runs daily and the results of which form part of the telemetry data that gets uploaded to Microsoft from the device. The specifics of this process are still somewhat shrouded in mystery, but thanks to the dedication of people like Adam Gross we can lift the lid a little bit on understanding compatibility assessment results. I highly recommend reading his blog if your interested in a more in-depth understanding.

Some compatibility data is stored in the registry under HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion\AppCompatFlags, and in particular the subkey TargetVersionUpgradeExperienceIndicators contains useful compatibility information for different W10 releases such as the GatedBlockIds, otherwise known as the Safeguard Hold Ids, some of which are published by Microsoft on their Windows 10 Known Issues pages. Since the release of Windows 10 2004, many devices have been prevented from receiving a W10 feature update by these Safeguard holds, so reporting on them can be useful to identify which devices are affected by which blocks, and which devices are not affected and are candidates for upgrade.

By inventorying these registry keys with MEMCM I built the PowerBI report shown above where you can view data such as:

  • Which Safeguard holds are affecting which devices
  • Rank of model count
  • Rank of OS version count
  • The upgrade experience indicators (UE)
  • UE red block reasons

Note that this data alone doesn’t replace a solution like Desktop Analytics which can help identify devices with potential app or driver compatibility issues, but it’s certainly helpful with the Safeguard holds.

You can also use this data to build collections in MEMCM containing devices that are affected by a Safeguard hold. Because this is based on inventory data, when a Safeguard hold is released by Microsoft those devices will move naturally out those collections.

Understanding the data

Because of the lack of any public documentation around the compatibility appraiser process, we have to take (hopefully!) intelligent guesses as to what the data means.

Under the TargetVersionUpgradeExperienceIndicators registry key for example, you may find subkeys for 19H1, 20H1, 21H1 or even older Windows 10 versions. I haven’t found any keys for *H2 releases though, and I can only assume it’s because the Safeguard holds for a H1 release are the same for the H2 release. From the Windows 10 Known Issues documentation this seems to be the case.

There is also a UNV subkey – I assume that means Universal and contains data that applies across any feature update.

Under the *H1 keys (I suppose I should call it a branch, really) we can try to understand some of the main keys such as:

  • FailedPrereqs – I haven’t seen any devices yet that actually failed the appraiser’s prerequisites, but I assume the details would be logged here if they were.
  • AppraiserVersion, SdbVer, Version, DateVer* – I assume these indicate the version of the compatibility appraiser database used for the assessment
  • DataExp*, DataRel* – these seem to indicate the release and expiry dates for the Appraiser database so my assumption is a new one will be downloaded at or before expiry
  • GatedBlock* – the Id key in particular gives the Safeguard Hold Id/s that are blocking the device from upgrade
  • Perf – this appears to be a general assessment of the performance of the device. A low performing device will likely take longer to upgrade
  • UpgEx* – these seem to be a traffic-light rating for the ‘upgrade experience’. The UpgExU seems to stand for Upgrade Experience Usage – I don’t know what the difference between the two is. Green is good, right, so a green device is going to be a good upgrade experience, yellow or orange not so great, red is a blocker. I don’t know exactly what defines each colour other than that…
  • RedReason – if you’ve got a red device, it’s blocked from upgrade by something – but this isn’t related to Safeguard holds as far as I can tell. It seems to be related to the keys under HKLM:\SOFTWARE\Microsoft\Windows NT\CurrentVersion\AppCompatFlags\CompatMarkers\*H1, such as BlockedByBios, BlockedByCpu for example. The only one I’ve seen in practice is the SystemDriveTooFull block.

Configure Custom Hardware Inventory

Alright, so first we need to configure hardware inventory in MEMCM to include the registry keys we want to use. You can use the RegKey2Mof utility, or you can download the files below to update your Configuration.mof file and your Client Settings / hardware inventory classes. I’ll assume you are familiar with that process.

Configuration.mof.additions

20H1_AppCompat.mof

21H1_AppCompat.mof

If you choose to use different names for the created classes, you’ll need to update the PowerBI report as it uses those names.

Download the PowerBI Report

Download the PBI template from here:

Windows 10 Feature Updates Readiness

Open opening, you’ll need to add the SQL server and database name for your MEMCM database:

You won’t see any data until devices have started sending hardware inventory that includes the custom classes.

Note that I have included pages for both 20H1 and 21H1, but the latter is just a placeholder for now as no actual compatibility data will be available until that version is released, or close to.

MEMCM collections

You can also build collections like those shown above by adding a query rule and using the names created by the custom classes – in this case TwentyHOne and TwentyOneHOne. Use the value option to find which GatedBlockIds are present in your environment.

Hope it helps!

PowerBI Reports for Windows 10 Feature Update Compliance

This morning I saw an interesting tweet from Sandy Zeng with a Log Analytics workbook she’d created for W10 feature updates based on Update Compliance data. I’d been meaning to create a similar report for that myself in PowerBI for some time, so I took inspiration from her tweet and got to work on something!

Microsoft’s Update Compliance solution can be used to report on software updates and feature updates status across your estate from Windows telemetry data. If you use Desktop Analytics, you can even combine the data for richer reporting.

Log Analytics allows exporting of queries in Power Query M formula language, which can be imported into PowerBI to create some nice reports.

Here’s a screenshot of what I ended up with. You can filter the data to view devices with Safeguard holds, for example, which since Windows 10 2004 has been a show stopper for many wanting to upgrade…

There are pages for 2004 and 20H2, as well as a page listing some of the known Safeguard hold IDs that have been publicly disclosed by Microsoft.

You can download this report for your own use with the links below. Note I have created two reports – the first assumes you have both Update Compliance and Desktop Analytics using the same Log Analytics workspace. If this is not the case for you, download the second report which doesn’t link to DA data and just uses Update Compliance. The only data I’ve included from DA is the make and model since these can be helpful in analysing devices affected by Safeguard holds.

Windows 10 Feature Update Compliance

Windows 10 Feature Update Compliance (no DA)

To create your own report, you’ll need the latest version of PowerBI desktop installed, with preview support for dynamic M query parameters.

Open PowerBI and go to File > Options and settings > Options > Preview features and enable Dynamic M Query Parameters.

Restart PowerBI and open the downloaded PBI template. Upon opening, you’ll be prompted for the Log Analytics workspace ID. You can find this on the Overview pane of your workspace in the Azure portal.

The reports contain data with a timespan of the last 48 hours, but you can change this if you want by editing the queries in the Advanced editor, and changing the value “P2D”.

You also might want to play around with the LastScan filter so you only get devices with a recent scan date, and avoid duplicates.

Hope it’s helpful!

Creating ADR Deployments in SCCM with PowerShell

Today I needed to create a number of deployments for Software Update Automatic Deployment Rules in SCCM, so I turned to PowerShell and used the New-CMAutoDeploymentRuleDeployment cmdlet available in the ConfigurationManager module. It works well enough, however there are a couple of options that the cmdlet cannot set, namely:

  • If software updates are not available on distribution point in current, neighbour or site boundary groups, download content from Microsoft Updates
  • If any update in this deployment requires a system restart, run updates deployment evaluation cycle after restart

Turns out that these can easily be set though by manipulating the XML deployment template in the object returned by the cmdlet. You can actually set all the deployment properties that way if you wanted, so long as you know the parameters and values from the deployment template XML.

Here is an example that creates the ADR deployments for an array of collections and also sets the two options above:

# ADR name
$ADRName = "Windows 10 Updates"

# Collections to create deployments for
$Collections = @(
    'SUP - Pilot - ABC - All'
    'SUP - Pilot - XYZ - All'
    'SUP - Production - ABC - All'
    'SUP - Production - XYZ - All'

)

# Import ConfigMgr Module
Import-Module $env:SMS_ADMIN_UI_PATH.Replace('i386','ConfigurationManager.psd1')
$SiteCode = (Get-PSDrive -PSProvider CMSITE).Name
Set-Location ("$SiteCode" + ":")

# Get the ADR
$ADR = Get-CMAutoDeploymentRule -Name $ADRName

# Create the deployments
Foreach ($Collection in $Collections)
{
    # Create the deployment
    $Params = @{
        CollectionName = $Collection
        EnableDeployment = $true
        SendWakeupPacket = $false
        VerboseLevel = 'OnlySuccessAndErrorMessages'
        UseUtc = $true
        AvailableTime = 2
        AvailableTimeUnit = 'Days'
        DeadlineImmediately = $true
        UserNotification = 'DisplaySoftwareCenterOnly'
        AllowSoftwareInstallationOutsideMaintenanceWindow = $true
        AllowRestart = $false
        SuppressRestartServer = $true
        SuppressRestartWorkstation = $true
        WriteFilterHandling = $true
        NoInstallOnRemote = $false 
        NoInstallOnUnprotected = $false
        UseBranchCache = $true
    }
    $null = $ADR | New-CMAutoDeploymentRuleDeployment @Params

    # Update the deployment with some additional params not available in the cmdlet
    $ADRDeployment = Get-CMAutoDeploymentRuleDeployment -Name $ADRName -Fast | where {$_.CollectionName -eq $Collection}
    [xml]$DT = $ADRDeployment.DeploymentTemplate
    # If software updates are not available on distribution point in current, neighbour or site boundary groups, download content from Microsoft Updates
    $DT.DeploymentCreationActionXML.AllowWUMU = "true" 
    # If any update in this deployment requires a system restart, run updates deployment evaluation cycle after restart
    If ($DT.DeploymentCreationActionXML.RequirePostRebootFullScan -eq $null)
    {
        $NewChild = $DT.CreateElement("RequirePostRebootFullScan")
        [void]$DT.SelectSingleNode("DeploymentCreationActionXML").AppendChild($NewChild)
    }
    $DT.DeploymentCreationActionXML.RequirePostRebootFullScan = "Checked" 
    $ADRDeployment.DeploymentTemplate = $DT.OuterXml
    $ADRDeployment.Put()
}

ConfigMgr OS Upgrade TS W10 1709 Does Not Care About Windows Edition

Today I ran a ConfigMgr OS Upgrade task sequence configured to use the Enterprise edition of Windows 10 1709 on a workstation that had Windows 10 Pro 1703 installed. Since the VLC media for 1709 contains the various editions in different indexes, you are supposed to choose the relevant one in the Upgrade Operating System step.

OSUpgrade

Of course, I expected the TS to fail because the configured edition is different to the edition that TS was being run on – but to my surprise, it didn’t care and the TS succeeded! Windows 10 Pro 1703 was upgraded to Windows 10 Pro 1709.

Not sure if that’s a bug or a feature, but it’s actually quite convenient!

Find the Compliance State for Software Updates on Remote Computers with PowerShell

Since the recent WannaCrypt ransomware attacks, many organisations have wanted to get the patch status of their systems to see if they are protected. Several reports and SQL queries for ConfigMgr were quickly posted online to help enterprises identify at-risk machines. But what for those who don’t use ConfigMgr, or what if you want to get the real-time status for a patch on a particular system instead of relying on deployment results or hardware inventory data?

For this, I wrote a couple of PowerShell scripts that will connect to a remote system, or group of systems, and check the status of any number of patches based on information from WMI. The scripts will use the Win32_QuickFixEngineering class on all targeted systems, as well as the SCCM WMI classes for those that are using ConfigMgr.

There are two scripts, one is single-threaded which is fine if you want to just check the local system, or a couple of remote systems.  If you want to check many remote systems at the same time, then use the multi-threaded version which will give you significantly quicker results.

Both scripts work the same way, but bear in mind the multi-threaded version will use more system resources, especially CPU, so set the throttle-limit (number of simultaneous threads) to something sensible for your CPU. The default limit is 32. The multi-threaded version uses my [BackgroundJob] custom class which will only work in PowerShell 5 +, and you will need to run the code for that class in your PowerShell session first.

To use the scripts, simply pass the computer name/s and Article ID/s (KB number) for the patch.

To run against the local machine:


Get-PatchStatus -ArticleID 4019264

Against a remote machine:


Get-PatchStatus -ComputerName PC001 -ArticleID 4019264

Against several remote machines:


Get-PatchStatus -ComputerName PC001,PC002,PC003,SRV001,SRV002 -ArticleID 4019264

Using several Article IDs


Get-PatchStatus -ComputerName PC001,PC002,PC003 -ArticleID @(4019264,4019265,4016871)

Set the throttle limit on the multi-threaded version, and using verbose output:


Get-PatchStatus -ComputerName $Computers -ArticleID $ArticleIDs -ThrottleLimit 64 -Verbose

Result

result

You might want to output to GridView if you have a lot of results for better filtering.

Note that if no results are returned in the (SCCM) or (QFE) columns then the patch is not installed, or if there was an error connecting to the remote system, this will be returned in the ‘Comments’ column.

The Scripts

Single-threaded version

# Get-PatchStatus.ps1
# Gets the status of software update/s on remote system/s
#
# The is the SINGLE-THREADED version
#
# Author: Trevor Jones
# May-2017
[CmdletBinding()]
Param
(
[Parameter(Mandatory=$false,
ValueFromPipelineByPropertyName=$true,
ValueFromPipeline=$true
)]
[string[]]$ComputerName = $env:COMPUTERNAME,
[Parameter(Mandatory=$true,
ValueFromPipelineByPropertyName=$true,
ValueFromPipeline=$true
)]
[array]$ArticleID
)
Begin
{
# Create a datatable to hold the results
$Table = New-Object System.Data.DataTable
$Table.Columns.AddRange(@("ComputerName","Article ID","SCCM Installed Status","SCCM Pending Status","WMI InstalledOn Date","Comments"))
}
Process
{
# Process each computer
Foreach ($Computer in $ComputerName)
{
Try
{
# Make a cim session
Write-Verbose "[$Computer] Creating a Cim session"
$CimSession = New-CimSession ComputerName $Computer OperationTimeoutSec 5 ErrorAction Stop
}
Catch
{
$Comments = $_.Exception.Message
}
# Report any error
If ($Comments)
{
[void]$Table.Rows.Add($Computer,$null,$null,$null,$null,$Comments)
Remove-Variable Name Comments Force
}
Else
{
# Process each KB
Foreach ($KB in $ArticleID)
{
# Check SCCM WMI
Write-Verbose "[$Computer] Checking CCM WMI…"
Try
{
$CCMUpdate = Get-CimInstance CimSession $CimSession Query "SELECT * FROM CCM_UpdateStatus where Article=$KB" Namespace 'root\ccm\SoftwareUpdates\UpdatesStore' ErrorAction Stop | Select First 1 | Select ExpandProperty Status
}
Catch {}
# Check Win32 WMI
Write-Verbose "[$Computer] Checking Win32 WMI…"
Try
{
$WMIUpdate = Get-CimInstance CimSession $CimSession Query "SELECT * FROM Win32_QuickFixEngineering where HotFixID='KB$KB'" Namespace 'root\cimv2' ErrorAction Stop | Select ExpandProperty InstalledOn
}
Catch {}
# If relevant, check if the update is in a pending state
If ($CCMUpdate -ne "Installed" -or !$WMIUpdate)
{
Write-Verbose "[$Computer] Checking CCM Pending Status…"
Try
{
$CCMUpdatePending = Get-CimInstance CimSession $CimSession Query "SELECT * FROM CCM_SoftwareUpdate where ArticleID=$KB" Namespace 'ROOT\ccm\ClientSDK' ErrorAction Stop
}
Catch {}
If ($CCMUpdatePending)
{
# Convert the EvaluationState value
Switch ($CCMUpdatePending.EvaluationState)
{
0 {$PendingState = "None"}
1 {$PendingState = "Available"}
2 {$PendingState = "Submitted"}
3 {$PendingState = "Detecting"}
4 {$PendingState = "PreDownload"}
5 {$PendingState = "Downloading"}
6 {$PendingState = "WaitInstall"}
7 {$PendingState = "Installing"}
8 {$PendingState = "PendingSoftReboot"}
9 {$PendingState = "PendingHardReboot"}
10 {$PendingState = "WaitReboot"}
11 {$PendingState = "Verifying"}
12 {$PendingState = "InstallComplete"}
13 {$PendingState = "Error"}
14 {$PendingState = "WaitServiceWindow"}
15 {$PendingState = "WaitUserLogon"}
16 {$PendingState = "WaitUserLogoff"}
17 {$PendingState = "WaitJobUserLogon"}
18 {$PendingState = "WaitUserReconnect"}
19 {$PendingState = "PendingUserLogoff"}
20 {$PendingState = "PendingUpdate"}
21 {$PendingState = "WaitingRetry"}
22 {$PendingState = "WaitPresModeOff"}
23 {$PendingState = "WaitForOrchestration"}
}
}
}
# Add the results to the table
If ($PendingState)
{
[void]$Table.Rows.Add($Computer,$KB,$CCMUpdate,$PendingState,$WMIUpdate)
Remove-Variable Name PendingState Force
}
Else
{
[void]$Table.Rows.Add($Computer,$KB,$CCMUpdate,$null,$WMIUpdate)
If ($CCMUpdate)
{
Remove-Variable Name CCMUpdate Force
}
If ($WMIUpdate)
{
Remove-Variable Name WMIUpdate Force
}
}
}
}
# Close the cim session
If ($CimSession)
{
Remove-CimSession CimSession $CimSession
}
}
}
End
{
# Return the results
Return $Table
}

view raw
Get-PatchStatus.ps1
hosted with ❤ by GitHub

Multi-threaded version

# Get-PatchStatus.ps1
# Gets the status of software update/s on remote system/s
#
# The is the MULTI-THREADED version
#
# IMPORTANT: Requires my [BackgroundJob] custom class, available here: https://smsagent.wordpress.com/posh-5-custom-classes/background-job/
#
# Author: Trevor Jones
# May-2017
[CmdletBinding()]
Param
(
[Parameter(Mandatory=$false,
ValueFromPipelineByPropertyName=$true,
ValueFromPipeline=$true
)]
[string[]]$ComputerName = $env:COMPUTERNAME,
[Parameter(Mandatory=$true,
ValueFromPipelineByPropertyName=$true,
ValueFromPipeline=$true
)]
[array]$ArticleID,
[Parameter(Mandatory=$false
)]
[int]$ThrottleLimit = 32
)
Begin
{
# Create some arrays
$Jobs = @()
$Results = @()
# Start a timer
$Timer = New-Object System.Diagnostics.Stopwatch
$Timer.Start()
}
Process
{
# Process each computer
Foreach ($Computer in $ComputerName)
{
$Code = {
Param($Computer,$ArticleID)
# Create a datatable to hold the results
$TempTable = New-Object System.Data.DataTable
$TempTable.Columns.AddRange(@("ComputerName","Article ID","SCCM Installed Status","SCCM Pending Status","WMI InstalledOn Date","Comments"))
Try
{
# Make a cim session
$CimSession = New-CimSession ComputerName $Computer OperationTimeoutSec 5 ErrorAction Stop
}
Catch
{
$Comments = $_.Exception.Message
}
# Report any error
If ($Comments)
{
[void]$TempTable.Rows.Add($Computer,$null,$null,$null,$null,$Comments)
Remove-Variable Name Comments Force
}
Else
{
# Process each KB
Foreach ($KB in $ArticleID)
{
# Check SCCM WMI
Try
{
$CCMUpdate = Get-CimInstance CimSession $CimSession Query "SELECT * FROM CCM_UpdateStatus where Article=$KB" Namespace 'root\ccm\SoftwareUpdates\UpdatesStore' ErrorAction Stop | Select First 1 | Select ExpandProperty Status
}
Catch {}
# Check Win32 WMI
Try
{
$WMIUpdate = Get-CimInstance CimSession $CimSession Query "SELECT * FROM Win32_QuickFixEngineering where HotFixID='KB$KB'" Namespace 'root\cimv2' ErrorAction Stop | Select ExpandProperty InstalledOn
}
Catch {}
# If relevant, check if the update is in a pending state
If ($CCMUpdate -ne "Installed" -or !$WMIUpdate)
{
Try
{
$CCMUpdatePending = Get-CimInstance CimSession $CimSession Query "SELECT * FROM CCM_SoftwareUpdate where ArticleID=$KB" Namespace 'ROOT\ccm\ClientSDK' ErrorAction Stop
}
Catch {}
If ($CCMUpdatePending)
{
# Convert the EvaluationState value
Switch ($CCMUpdatePending.EvaluationState)
{
0 {$PendingState = "None"}
1 {$PendingState = "Available"}
2 {$PendingState = "Submitted"}
3 {$PendingState = "Detecting"}
4 {$PendingState = "PreDownload"}
5 {$PendingState = "Downloading"}
6 {$PendingState = "WaitInstall"}
7 {$PendingState = "Installing"}
8 {$PendingState = "PendingSoftReboot"}
9 {$PendingState = "PendingHardReboot"}
10 {$PendingState = "WaitReboot"}
11 {$PendingState = "Verifying"}
12 {$PendingState = "InstallComplete"}
13 {$PendingState = "Error"}
14 {$PendingState = "WaitServiceWindow"}
15 {$PendingState = "WaitUserLogon"}
16 {$PendingState = "WaitUserLogoff"}
17 {$PendingState = "WaitJobUserLogon"}
18 {$PendingState = "WaitUserReconnect"}
19 {$PendingState = "PendingUserLogoff"}
20 {$PendingState = "PendingUpdate"}
21 {$PendingState = "WaitingRetry"}
22 {$PendingState = "WaitPresModeOff"}
23 {$PendingState = "WaitForOrchestration"}
}
}
}
# Add the results to the table
If ($PendingState)
{
[void]$TempTable.Rows.Add($Computer,$KB,$CCMUpdate,$PendingState,$WMIUpdate,$Comments)
Remove-Variable Name PendingState Force
}
Else
{
[void]$TempTable.Rows.Add($Computer,$KB,$CCMUpdate,$null,$WMIUpdate,$Comments)
If ($CCMUpdate)
{
Remove-Variable Name CCMUpdate Force
}
If ($WMIUpdate)
{
Remove-Variable Name WMIUpdate Force
}
}
}
}
# Close the cim session
If ($CimSession)
{
Remove-CimSession CimSession $CimSession
}
# Return the results
Return $TempTable
}
$Job = [BackgroundJob]::new($Code,@($Computer,$ArticleID))
$Jobs += $Job
if (($Jobs.GetStatus() | where {$_.State -eq "Running"}).Count -ge $ThrottleLimit)
{
Do {Start-Sleep Seconds 1}
Until (($Jobs.GetStatus() | where {$_.State -eq "Running"}).Count -lt $ThrottleLimit)
}
Write-Verbose "[$Computer] Starting job"
$Job.Start()
}
}
End
{
# Wait until all jobs have completed
Do {}
Until ($Jobs.GetStatus().State -notcontains "Running")
# Gather results and cleanup runspaces
$Jobs | foreach {
[void]$_.Receive()
$Results += $_.Result
$_.Remove()
}
$Timer.Stop()
Write-Verbose "Completed in $($Timer.Elapsed.Minutes) minutes and $($Timer.Elapsed.Seconds) seconds."
# Return the results
Return $Results
}

view raw
Get-PatchStatus.ps1
hosted with ❤ by GitHub

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)

Calculate the Size of Multiple Packages in ConfigMgr with PowerShell

Have you ever wanted to select a group of applications or packages in ConfigMgr and find out the total size of the content files for all those packages?  Or maybe you have your packages organised into folders in the ConfigMgr console, and you want to find out the total content size of all the packages in that folder?  Well with PowerShell you can!

I wrote this script to help me in calculating the content size for multiple packages of any type I choose – applications, standard packages, driver packages, boot images etc, so that I know how much data I am sending across the wire when distributing packages to a new distribution point. If your network bandwidth to a distribution point is limited, then it is helpful to know how much data you are sending so you know whether you need to set rate limits, or distribute out of hours etc.

This script uses PowerShell’s handy Out-GridView to display the list of available packages for the package types found in the Software Library (you choose) and allow you to select multiple packages. It will then calculate the total size of all the packages you selected.  If you use the -Verbose option, it will also report the size of each individual package.

For Applications, Standard Packages and Driver Packages, which are often organised into sub-folders in the ConfigMgr console, you can also specify the folder name, and the script will return the total content size of all packages in that folder.

The following package types are supported:

  • Applications (Specific console folder supported)
  • Standard Packages (Specific console folder supported)
  • Driver Packages (Specific console folder supported)
  • Software Update Packages
  • OS Image Packages
  • Boot Image Packages

Get the script

Download from the Technet Gallery here.

Configure the script

The script has two parameters that you might want to set the defaults for, otherwise you will need to enter them each time:

  • $SiteServer – the name of the site server you are running the script on
  • $SiteCode – your Site code

Edit the following part of the script:


[Parameter(
Mandatory=$False,
HelpMessage="The Site Server name"
)]
[string]$SiteServer="mysccmserver",

[Parameter(
Mandatory=$False,
HelpMessage="The Site Code"
)]
[string]$SiteCode="ABC"

The script also has comment-based help.

Examples

Get the total content size of all Adobe Flash Player Applications

I run the script with the -Applications switch:

Get-CMSelectedSoftwareContentSizes.ps1 -Applications

Select the Adobe Flash Player applications and click OK:

1

Total content size is returned:

2

Get the total size of all Intel packages and also report the size of each package

I run the script with the -Packages and the -Verbose switches:

Get-CMSelectedSoftwareContentSizes.ps1 -Packages -Verbose

Select the Intel packages and click OK:

3

Total size and individual sizes are returned:

4

Get the total size of all Dell driver packages in the “Dell System Cab Driver Packages” console folder and report the size of each package

In the ConfigMgr console, I have Dell driver packages organised into a subfolder:

5

I run the script and use the -DriverPackages, -FolderName and -Verbose switches:

Get-CMSelectedSoftwareContentSizes.ps1 -DriverPackages -FolderName “Dell System Cab Driver Packages” -Verbose

The individual size and total size of all driver packages in that console folder is returned:

7

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.

Deploying KB3025945 Using the ConfigMgr Package Model

We recently had some reports of IE9 crashing after installing a MS patch KB3008923 released December 9th, 2014.  A few days ago on January 22nd, 2015 MS released an update that fixes the issue, KB3025945.  Unfortunately at the time of writing, the update is not available on WSUS, so it must be manually downloaded and deployed.  I decided to deploy the patch using ConfigMgr to the affected machines.  Of course, you could use SCUP to deploy the patch using the Software Updates model, but I decided to use the standard Package and Program model instead, with some Powerhelp ;).  Here’s a step-by-step guide for deploying the patch.  I’m using ConfigMgr 2012 R2 CU3.

Create a Collection

First, we need to identify all the machines that may be affected by this issue.  According to the KB, it only affects machines that have the KB3008923 installed, Internet Explorer 9, and the following operating systems: Windows 7 SP1, Windows 2008 SP2 and Windows 2008 R2.

Let’s use a little Powershell to create the collection and add the query rule that will filter for affected machines.  I’m using a limiting collection called ‘All SCCM 2012 Clients’, and I’m using Software Inventory to determine the IE version installed and the Win32_QuickFixEngineering WMI class to determine installed patches, so make sure that’s all enabled in your client settings (Software Inventory, Hardware Inventory classes).


# Create the collection
New-CMDeviceCollection -LimitingCollectionName "All SCCM 2012 Clients" -Name "KB3025945" `
-Comment "Manual deployment of patch KB3025945 to all Win 7 SP1, W2K8 R2 SP1 and W2K8 SP2 machines with KB3008923 and IE9 installed" `
-RefreshType Periodic

# Add the query rule
Add-CMDeviceCollectionQueryMembershipRule -CollectionName "KB3025945" -QueryExpression `
"select SMS_R_System.Name, SMS_G_System_OPERATING_SYSTEM.Caption, SMS_G_System_OPERATING_SYSTEM.Version
from SMS_R_System
inner join SMS_G_System_QUICK_FIX_ENGINEERING on SMS_G_System_QUICK_FIX_ENGINEERING.ResourceID = SMS_R_System.ResourceId
inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId
inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_SoftwareFile.FilePath like ""%\\Program Files\\Internet Explorer\\""
and SMS_G_System_SoftwareFile.FileName like ""iexplore.exe""
and SMS_G_System_SoftwareFile.FileVersion like ""9.%"" and
SMS_G_System_QUICK_FIX_ENGINEERING.HotFixID = ""KB3008923"" and
SMS_G_System_OPERATING_SYSTEM.Caption in (
""Microsoft Windows 7 Enterprise"",
""Microsoft Windows 7 Enterprise K"",
""Microsoft Windows 7 Enterprise N"",
""Microsoft Windows 7 Professional"",
""Microsoft Windows 7 Ultimate"",
""Microsoft Windows Server 2008 R2 Enterprise"",
""Microsoft Windows Server 2008 R2 Standard"",
""Microsoft® Windows Server® 2008 Enterprise"",
""Microsoft® Windows Server® 2008 Standard""
)
and SMS_G_System_OPERATING_SYSTEM.Version in (""6.0.6002"",""6.1.7601"")" `
-RuleName "KB3008923 / IE9"

Download the Patches

Great, now we have the collection, let’s download the patches from the Microsoft Update Catalog and save them to our SCCM package source.  There are 5 patches available for the different OS’s, so I’m gonna download them all.

Capture

Create the Packages, Programs and Deployments with Powershell

Lets make life easy again and use the native ConfigMgr Powershell cmdlets to do the work for us.

First off, I’m setting the Package and Program property variables as these will be common to all the packages.  Be sure to add your distribution point group name.  Further down in the script, from line 36, we need to set the package source location ($source) for each of the packages we will create for the different OS’s.  If you are happy with the Package name ($Pkg) you can leave it as is, and the command-lines are populated for you.  Since the installers are msu files, we will use WUSA.exe to install them silently with the /quiet and /norestart switches.  We will deploy the packages to the collection we created, simply named ‘KB3025945’.

I’m choosing to create a Required deployment that will begin as soon as possible, and I’m suppressing any notifications as I prefer to deploy silently in most cases, but you can of course change these settings in the script.

Run the script, and it will create the packages, programs, deployments, and distribute the package content.


<#
----------------
Deploy KB3025945
----------------

This script creates packages and programs for KB3025945, distributes the packages and deploys them.

Set the variables below as required
#>
## Set Package / Program Properties

# Required amount of disk space
$DiskSpace = "100"
# Units for Disk space requirement, eg KB, MB or GB
$DiskUnit = "MB"
# Maximum allowed run time
$Duration = "16"
# Program can run, eg "WhetherOrNotUserIsLoggedOn", "OnlyWhenNoUserIsLoggedOn", "OnlyWhenUserIsLoggedOn"
$RunType = "WhetherOrNotUserIsLoggedOn"
# Run mode, eg "RunWithAdministrativeRights" or "RunWithUserRights"
$RunMode = "RunWithAdministrativeRights"
# Allow user to interact?
$UserInteraction = $False
# Allow program to be installed from task sequence?
$EnableTS = $True
# Distribution Point Group Name
$DPG = "All Distribution Points"
# Set OS versions as array
$OSes = "Win7","Win7x64","W2K8","W2K8R2","W2K8x64"
## Set error action
$ErrorActionPreference = "Stop"
foreach ($OS in $OSes)
{

# Set Package source location and file names
if ($OS -eq "Win7")
{
$Pkg = "KB3025945 - Windows 7 SP1 x86"
$Source = "\\sccmserver-01\SoftwareUpdates\Out-of-Band Patches\KB3025945\Update for Windows 7 (KB3025945)"
$CommandLine = "wusa.exe X86-all-ie9-windows6.1-kb3025945-x86_ba77aa242aa4a0991c8879f9a04aaedd4c4b67c0.msu /quiet /norestart"
}
if ($OS -eq "Win7x64")
{
$Pkg = "KB3025945 - Windows 7 SP1 x64"
$Source = "\\sccmserver-01\SoftwareUpdates\Out-of-Band Patches\KB3025945\Update for Windows 7 for x64-based Systems (KB3025945)"
$CommandLine = "wusa.exe AMD64-all-ie9-windows6.1-kb3025945-x64_6566d74c6f14e6d4b120d2b07d91711f2e4c7dc9.msu /quiet /norestart"
}
if ($OS -eq "W2K8")
{
$Pkg = "KB3025945 - Windows Server 2008 x86 SP2"
$Source = "\\sccmserver-01\SoftwareUpdates\Out-of-Band Patches\KB3025945\Update for Windows Server 2008 (KB3025945)"
$CommandLine = "wusa.exe X86-all-ie9-windows6.0-kb3025945-x86_4a6f65fe4b418deb3e9626ba86234c3db718f85f.msu /quiet /norestart"
}
if ($OS -eq "W2K8R2")
{
$Pkg = "KB3025945 - Windows Server 2008 R2 SP1"
$Source = "\\sccmserver-01\SoftwareUpdates\Out-of-Band Patches\KB3025945\Update for Windows Server 2008 R2 x64 Edition (KB3025945)"
$CommandLine = "wusa.exe AMD64-all-ie9-windows6.1-kb3025945-x64_6566d74c6f14e6d4b120d2b07d91711f2e4c7dc9.msu /quiet /norestart"
}
if ($OS -eq "W2K8x64")
{
$Pkg = "KB3025945 - Windows Server 2008 x64 SP2"
$Source = "\\sccmserver-01\SoftwareUpdates\Out-of-Band Patches\KB3025945\Update for Windows Server 2008 x64 Edition (KB3025945)"
$CommandLine = "wusa.exe AMD64-all-ie9-windows6.0-kb3025945-x64_5cb8756529a63243805301e4e26f375bb6819fc1.msu /quiet /norestart"
}

## Create the Package ##

write-host "Creating Package: $Pkg" -foregroundcolor Green
try
{
$Package = New-CMPackage -Name $Pkg -Path $Source
}
catch { write-host "Could not create new package.`n$($_.Exception.Message)" -ForegroundColor Red; break }

## Create a Program

write-host 'Adding Program'
try
{
$Program = New-CMProgram `
-PackageName $Pkg `
-StandardProgramName $Pkg `
-CommandLine $CommandLine `
-DiskSpaceRequirement $DiskSpace `
-DiskSpaceUnit $DiskUnit `
-Duration $Duration `
-ProgramRunType $RunType `
-RunMode $RunMode `
-UserInteraction $UserInteraction
}
catch { write-host "Could not add program.`n$($_.Exception.Message)" -ForegroundColor Red; break }

## Update Program

write-host 'Updating Program'
try
{
$ProgramUpdate = Set-CMProgram `
-Name $Pkg `
-ProgramName $Pkg `
-StandardProgram `
-EnableTaskSequence $EnableTS
}
catch { write-host "Could not update program.`n$($_.Exception.Message)" -ForegroundColor Red; break }

## Distribute Content to DPs ##

write-host 'Distributing Content to DPs'
try
{
Start-CMContentDistribution -PackageName $Pkg -DistributionPointGroupName $DPG
}
catch { write-host "Could not distribute package.`n$($_.Exception.Message)" -ForegroundColor Red; break }


## Deploy the package

write-host "Deploying the package...please wait"
try
{
Start-CMPackageDeployment -CollectionName "KB3025945" `
-PackageName $pkg `
-ProgramName $pkg `
-StandardProgram `
-DeployPurpose Required `
-FastNetworkOption DownloadContentFromDistributionPointAndRunLocally `
-SlowNetworkOption DownloadContentFromDistributionPointAndLocally `
-SoftwareInstallation $true `
-SystemRestart $false `
-ScheduleEvent AsSoonAsPossible `
-RerunBehavior RerunIfFailedPreviousAttempt
}
catch { write-host "Could not deploy the package.`n$($_.Exception.Message)" -ForegroundColor Red; break }


}
write-host 'Done!' -ForegroundColor Yellow

Set the OS Requirement on the Programs

Great, now I have everything done…well, nearly everything…

Capture2

Capture4

Capture5

A limitation of the native Powershell cmdlets is that we can’t set the OS requirement on the Programs, so we need to do that in the console.  In the Properties of each program, set the relevant OS:

Capture3

That’s it!  Installation logging will appear in the WindowsUpdate.log on the client.  Since we did not enforce a reboot the clients will install the patch, but it will remain in the ‘reboot pending’ state until a reboot has been performed.  You can monitor your deployment using the standard methods in the ConfigMgr console or the built-in reports.