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.

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

Schedule and Monitor ConfigMgr Package Distributions with PowerShell

Update! (22-Nov-2014) Updated the script to include a prompt for which distribution point group to use and a couple of minor improvements

Have you ever wanted to have the capability of scheduling a package distribution in ConfigMgr to occur at a specific date / time? Or perhaps you want to monitor your package distributions an receive an email when it has completed? Well now you can!

As a ConfigMgr administrator, I sometimes want to schedule my package distributions to take place outside of office hours, especially with very large packages like windows images.  I don’t want to consume a lot of bandwidth during working hours, so I prefer to start them at a time when the bandwidth I consume has the least impact on the sites I am distributing from and to. Sure I can use bandwidth limiting on the distribution point, but I don’t want to restrict bandwidth for all my distributions during working hours, only the really big ones, so out of hours is a better option.

Additionally I want to monitor my distributions to see when they have completed and how long they take.  There is a nice tool in the ConfigMgr 2012 R2 toolkit called ‘Distribution Point Job Queue Manager’, but there is no facility to receive a notification when a distribution has completed. Instead you must continually refresh it, which I can’t do if I’m scheduling a distribution out of hours.

To meet this need, I prepared a PowerShell script available from the Technet Gallery that will do either of the following things:

  1. Schedule the distribution of a new ConfigMgr package (equivalent to ‘Distribute Content‘ in the ConfigMgr console), monitor it, and send an email when the distributions have finished.

  2. Schedule the update of a package content to a distribution point group (equivalent to ‘Update Distribution Points‘ in the ConfigMgr Console), monitor it, and send an email when the distributions have finished.

  3. Monitor an already-running distribution and send an email when the distributions have finished.

The script will work for any package type, eg standard package, driver package, task sequence packages, software update packages, application content packages, OS images, boot images, OS installer packages.

It works by creating a ‘scheduled job’ in PowerShell, which will start at the time you specify.  The scheduled job is a PowerShell script that will start the package distribution, monitor the status of the distributions in WMI, then send an email when all the active distributions for that package are complete.  It will also email you if a distribution fails, although ConfigMgr will retry a failed distribution up to 99 times, so it will continue to monitor it for completion.

Update Distribution Points Example

Let’s take a look at an example.   In this case I’ve updated the source files for a deployment type called ‘Uninstall Java‘ in my ‘Java‘ application, and I want to update the distribution points with the new content.

Let’s run the script.  The first thing it asks for is what I want to do.  If I’ve created a new package or application and I want to distribute it to my distribution point group, I choose option [1].  If I’ve updated source files for an existing package or application and I want to update the distribution points with the new content, I choose [2].  If I’ve already started a package distribution and I simply want to monitor it, I choose [3].  In this case, i’m choosing [2].

1

Now it asks me for an email address to send the distribution monitor email notifications to.

2

Next I can enter the date and time that I want the package distribution to start.  You can enter enter any date and/or time format that your regional settings will understand.  For example, I can enter 22:00 to start my distribution today at 10pm.  Or I can enter 25/12/14  15:00 to start my distribution at 3pm on Christmas day in the UK time zone.  Pretty sure that won’t impact the network!  If I want to distribute the package now, I simply hit Enter, and it will be scheduled in 3 minutes from now.

3

Next it asks me for the name of the package or application I want to distribute.  It will then check the ConfigMgr WMI to validate whether a package or application exists with that name, and what type of package it is.

4

Since it is an application I must also enter which deployment type I want to distribute content for:

5

Great, now my distribution job has been scheduled and will run at the set time.

6

Let’s see what email notifications we get.  First, we get a notification that the distribution has started:

email1

Sometimes a distribution to one or more of the distribution points may fail the first time.  If it does, ConfigMgr will retry it up to 99 more times before giving up.  The script will continue to monitor the distribution, but we will get a notification that one or more the distributions has failed, and will be retried.  This is good to know as the distribution will likely take longer than usual because of a failure.

email2

Once the distribution has completed on all distribution points, we get an email summarizing the distribution.

email3Package Distribution Monitor Example

Let’s look at another example.  in this case, I’ve already distributed content for a package called ‘Retain Corporate Client‘, and I want to monitor the distribution for its completion.

In this case, I’m asked similar questions to before, but then it will tell me what package distributions are currently active, and ask me which one I want to monitor.  There is only one at the moment, and I enter the Package ID that it gives me.  Then my monitor job is scheduled to run in 2 minutes from now.

7

I get much the same emails as before:

email4

email5

Configuring the Script

To run the script, you should enter the following email variables at the top of the script.  These will probably not change so you can just enter them one time and that’s it.

Capture

The script must also be run as administrator.  This is required to create scheduled jobs.  If you are not running it as administrator, you will be notified.

Enjoy 🙂

 

How to Quickly Get Overall Compliance for ConfigMgr Software Update Deployments

If you have access to your ConfigMgr SQL database, you can quickly get a summary for the overall compliance of software updates deployments using the following PowerShell script, which you can run on your local machine.

It returns the results into the PowerShell GridView, allowing you to filter by deployment name, start time, deadline etc.

Enter your SQL server database and instance name at the top of the script.

Capture


<#

This script gets overall compliance summary data for software updates deployments from the ConfigMgr SQL database

#>

# Database info
$dataSource = “mysqlserver\INST_SCCM”
$database = “CM_ABC”

# 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()

# Getting Software Updates Compliance Data
Write-host "Getting Software Updates Overall Compliance Data"
$query = "SELECT CI.AssignmentID `
,CI.AssignmentName `
,Description `
,CollectionID `
,StartTime `
,EnforcementDeadline `
,OverrideServiceWindows`
,RebootOutsideOfServiceWindows `
,NumCompliant*100/NumTotal as 'PercentCompliant' `
,NumTotal `
,NumHealthy`
,NumCompliant `
,NumNonCompliant `
,NumUnknown `
,NumFailed `
,NumPending `
,NumHealthyCompliant `
,NumHealthyFailed `
FROM v_UpdateDeploymentClientSummary UDCS `
inner join v_CIAssignment CI on UDCS.AssignmentID = CI.AssignmentID ORDER BY AssignmentName Desc"
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()

$table = new-object “System.Data.DataTable”
$table.Load($result)
$table | Out-GridView -Title "Software Updates Overall Compliance per Deployment"


# Close the connection
$connection.Close()

Getting all Members of all Collections in a Folder in ConfigMgr

In our SCCM 2012 environment, we have a subfolder of device collections that are used for Software Updates.  I wanted to get a list of all the members of the different collections in that folder.  I saw a recent post by Kaido Järvemets on how to easily list folder objects using Powershell, so I decided to implement that code into a script that exports all the collections and their members in a ConfigMgr folder you specify, into a text file.

You need to get the ContainerNodeID from WMI as Kaido describes in his post, then add that and your site code to the script.

I want all the collections in the ‘Software Updates’ folder:

Updates

 

I run the script…

PS

Then I get a text file containing all the collections with their members. 🙂


$SiteCode = "PS1"
$FolderID = 16777221 # Use the WMI tool to get the FolderID

Write-Host "Getting Folder Name of Folder ID $FolderID"
$FolderName = Get-WmiObject -Namespace "ROOT\SMS\Site_$SiteCode" `
-Query "select * from SMS_ObjectContainerNode where ContainerNodeID='$FolderID'" | Select Name
$FolderName = $FolderName.Name
Write-Host "Getting Collections from '$FolderName'"
$CollectionsInSpecficFolder = Get-WmiObject -Namespace "ROOT\SMS\Site_$SiteCode" `
-Query "select * from SMS_Collection where CollectionID is
in(select InstanceKey from SMS_ObjectContainerItem where ObjectType='5000'
and ContainerNodeID='$FolderID') and CollectionType='2'"

$Collections = $CollectionsInSpecficFolder.Name

Write-host "Getting collection members for:"

Foreach ($Collection in $Collections) {

$Collection
$Col = Get-CMDeviceCollection -Name $Collection | Select Name, Comment, CollectionID, MemberCount
$Members = Get-CMDevice -CollectionName $Collection | Select Name | Sort Name
$Col,"**Members** ",$Members | ft -AutoSize | Out-File -FilePath C:\Scripts\Staging\SoftwareUpdatesMembers.txt -Append

}
Invoke-Item C:\Scripts\Staging\SoftwareUpdatesMembers.txt