Create Collections for SCCM Software Update Installation Failures by Error Code

Recently I published a blog about creating collections for SCCM client installation failures by error code. In this post, I will do the same for Software Update installation failures.

If you’re lucky enough not to have any errors installing software updates with SCCM, then this post isn’t for you, but if you do experience installation failures it can be helpful to collate machines with the same error into collections so you can easily target them for remediation using the SCCM Scripts feature for example, or just for visibility and reporting.

To find which software update installation errors you are experiencing in your environment, you can run the following SQL query against the SCCM database. This will find systems in the “Error” or “Unknown” enforcement states for software update deployments and group them by the enforcement error code.

Select Count(ResourceID),LastEnforcementErrorCode
from vSMS_SUMDeploymentStatusPerAsset 
where StatusType in (4,5)
and LastEnforcementErrorCode is not null
Group by LastEnforcementErrorCode

Next is a PowerShell script that will create collections for each error code. You need to specify the error codes in the Error Code translation table in the script. I’ve included some common error codes for software updates and their friendly descriptions – add or remove error codes according to your own environment. To translate error codes to friendly descriptions, see here. Run the script on a site server or anywhere with the SCCM console installed.

I’ve split the collections between those with an “error” enforcement state and those with “unknown” as you may wish to handle them separately, and placed the collections for each state in different sub-folders.

You may wish to be more targeted in the WQL query for the collection rule, targeting only certain collections or deployments etc. For example, you can add a ‘where’ clause for SUM.CollectionName to target particular collections, or SUM.AssignmentName to target specific SUG deployments.

Here’s what the end result will look like. The error description is added to the Comment field, so just add that in the console view.

Find the Full Windows Build Number with PowerShell

Much to my surprise I discovered that the full build number for a Windows OS is not stored in WMI in the usual Win32_OperatingSystem class.

In Windows 10 at least, the full build number containing the “UBR”, or essentially the CU patch level of the build, is a useful piece of information.

Open Settings > System > About on a Windows 10 box, and you’ll find the OS Build value, in my case 15063.183


If I run the usual WMI query to get the build number I just get 15063:


Same if I query the environment:


To find the full number I have to query the registry in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion branch.

So I put together a PowerShell script that can be used to get the Windows version for a local or remote computer (or group of computers) which includes the Edition, Version and full OS Build values.

Query the local system like this:


Or query remote computers:

Get-WindowsVersion -ComputerName PC001

Get-WindowsVersion -ComputerName @("PC001","PC002","SRV001","SRV002")



The script

Creating Simple Charts in WPF with PowerShell

Windows Presentation Foundation (WPF) is great for creating GUI applications, but it does not natively contain any charting controls.  There are a number of products that can be used to create charts in WPF, including the WPF toolkit and the Microsoft Chart Controls for .Net, but good-old Windows Forms does this natively.

WPF has does have a WindowsFormsHost control, but there are a number of potential issues with hosting Windows Forms in a WPF application, and it not recommended practice.

After some playing around however, I found it is possible to add a Windows Forms chart simply by displaying it as an image.  Furthermore, it is also possible to save the image to a memory stream in binary format, which means it does not need to be saved to disk as a file, but can simply be stored and read from memory in binary form.

Below is a simple example of a Windows Forms pie chart added as an image to a WPF window using PowerShell.  It calculates the used and available RAM in the local system, creates a pie chart from the data, saves it as in image in binary form, then adds it as the source to an image control in the WPF window.  Pretty cool 🙂


# Add required assemblies
Add-Type -AssemblyName PresentationFramework,System.Windows.Forms,System.Windows.Forms.DataVisualization

# Create WPF window
[xml]$xaml = @"
<Window          xmlns=""         xmlns:x=""         Title="Chart Example" Height="350" Width="420">
        <Image x:Name="image" HorizontalAlignment="Left" Height="auto" VerticalAlignment="Top" Width="auto"/>


# Add window and it's named elements to a hash table
$script:hash = @{}
$hash.Window = [Windows.Markup.XamlReader]::Load((New-Object -TypeName System.Xml.XmlNodeReader -ArgumentList $xaml))
$xaml.SelectNodes("//*[@*[contains(translate(name(.),'n','N'),'Name')]]") | ForEach-Object -Process {
    $hash.$($_.Name) = $hash.Window.FindName($_.Name)

# Function to create a Windows Forms pie chart
# Modified from
Function Create-PieChart() {

    #Create our chart object
    $Chart = New-object System.Windows.Forms.DataVisualization.Charting.Chart
    $Chart.Width = 430
    $Chart.Height = 330
    $Chart.Left = 10
    $Chart.Top = 10

    #Create a chartarea to draw on and add this to the chart
    $ChartArea = New-Object System.Windows.Forms.DataVisualization.Charting.ChartArea

    #Add a datapoint for each value specified in the parameter hash table
    $Params.GetEnumerator() | foreach {
        $datapoint = new-object System.Windows.Forms.DataVisualization.Charting.DataPoint(0, $_.Value.Value)
        $datapoint.AxisLabel = "$($_.Value.Header)" + "(" + $($_.Value.Value) + " GB)"

    $Chart.Series["Data"].ChartType = [System.Windows.Forms.DataVisualization.Charting.SeriesChartType]::Pie
    $Chart.Series["Data"]["PieLabelStyle"] = "Outside"
    $Chart.Series["Data"]["PieLineColor"] = "Black"
    $Chart.Series["Data"]["PieDrawingStyle"] = "Concave"
    ($Chart.Series["Data"].Points.FindMaxByValue())["Exploded"] = $true

    #Set the title of the Chart
    $Title = new-object System.Windows.Forms.DataVisualization.Charting.Title
    $Chart.Titles[0].Text = "RAM Usage Chart ($($env:COMPUTERNAME))"

    #Save the chart to a memory stream, then to the hash table as a byte array
    $Stream = New-Object System.IO.MemoryStream
    $Hash.Stream = $Stream.GetBuffer()

# Add an event to display the chart when the window is opened
    # Create a hash table to store values
    $Params = @{}
    # Get local RAM usage from WMI
    $RAM = (Get-CimInstance -ClassName Win32_OperatingSystem -Property TotalVisibleMemorySize,FreePhysicalMemory)
    # Add Free RAM to a hash table
    $Params.FreeRam = @{}
    $Params.FreeRam.Header = "Free RAM"
    $Params.FreeRam.Value = [math]::Round(($RAM.FreePhysicalMemory / 1MB),2)
    # Add used RAM to a hash table
    $Params.UsedRam = @{}
    $Params.UsedRam.Header = "Used RAM"
    $Params.UsedRam.Value = [math]::Round((($RAM.TotalVisibleMemorySize / 1MB) - ($RAM.FreePhysicalMemory / 1MB)),2)
    # Create the Chart
    Create-PieChart $Params
    # Set the image source
    $Hash.image.Source = $hash.Stream

# Display window
$null = $hash.Window.ShowDialog()

Creating WPF GUI Applications with Pure PowerShell

When creating a GUI application in PowerShell, I usually use Visual Studio, or Blend for Visual Studio, to design a WPF application, then copy and run the XAML code in PowerShell.  Designing in VS is generally easier and quicker and creates less code, but it is also perfectly possible to create a WPF GUI using pure PowerShell, which is more akin to the Windows Forms method of GUI creation.  For more complex applications that’s not the ideal way because it takes longer and creates a lot more code, but for simple applications, or if you are used to designing something in Windows Forms, why not give it a go?  You simply need to create a window from the [System.Windows] .Net namespace, then add some controls from the [System.Windows.Controls] namespace, and you’re away!

Here’s a simple example application that displays the running processes on your machine, the list of services, and a little game “Click the Fruit” as a bonus 😉


# Add required assembly
Add-Type -AssemblyName PresentationFramework

# Create a Window
$Window = New-Object Windows.Window
$Window.Height = "670"
$Window.Width = "700"
$Window.Title = "PowerShell WPF Window"

# Create a grid container with 2 rows, one for the buttons, one for the datagrid
$Grid =  New-Object Windows.Controls.Grid
$Row1 = New-Object Windows.Controls.RowDefinition
$Row2 = New-Object Windows.Controls.RowDefinition
$Row1.Height = "70"
$Row2.Height = "100*"

# Create a button to get running Processes
$Button_Processes = New-Object Windows.Controls.Button
$Button_Processes.Height = "50"
$Button_Processes.Width = "150"
$Button_Processes.Margin = "10,10,10,10"
$Button_Processes.HorizontalAlignment = "Left"
$Button_Processes.VerticalAlignment = "Top"
$Button_Processes.Content = "Get Processes"
$Button_Processes.Background = "Aquamarine"

# Create a button to get services
$Button_Services = New-Object Windows.Controls.Button
$Button_Services.Height = "50"
$Button_Services.Width = "150"
$Button_Services.Margin = "180,10,10,10"
$Button_Services.HorizontalAlignment = "Left"
$Button_Services.VerticalAlignment = "Top"
$Button_Services.Content = "Get Services"
$Button_Services.Background = "Aquamarine"

# Create a button to play Click the fruit
$Button_Cool = New-Object Windows.Controls.Button
$Button_Cool.Height = "50"
$Button_Cool.Width = "150"
$Button_Cool.Margin = "350,10,10,10"
$Button_Cool.HorizontalAlignment = "Left"
$Button_Cool.VerticalAlignment = "Top"
$Button_Cool.Content = "Play 'Click the Fruit'"
$Button_Cool.Background = "Aquamarine"

# Create a datagrid
$DataGrid = New-Object Windows.Controls.DataGrid
$DataGrid.MinHeight = "100"
$DataGrid.MinWidth = "100"
$DataGrid.Margin = "10,0,10,10"
$DataGrid.HorizontalAlignment = "Stretch"
$DataGrid.VerticalAlignment = "Stretch"
$DataGrid.VerticalScrollBarVisibility = "Auto"
$DataGrid.GridLinesVisibility = "none"
$DataGrid.IsReadOnly = $true

# Add the elements to the relevant parent control
$window.Content = $Grid

# Add an event on the Get Processes button
    $Processes = Get-Process | Select ProcessName,HandleCount,NonpagedSystemMemorySize,PrivateMemorySize,WorkingSet,UserProcessorTime,Id
    $DataGrid.ItemsSource = $Processes

# Add an event on the Get Services button
    $Services = Get-Service | Select Name,ServiceName,Status,StartType
    $DataGrid.ItemsSource = $Services

# Add an event to play Click the fruit

    $Fruit = @{
        Apples = "Green"
        Bananas = "Yellow"
        Oranges = "Orange"
        Plums = "Maroon"

    $Fruit.GetEnumerator() | Foreach {
        # Create a transparent window at a random location on the screen
        $NewWindow = New-Object Windows.Window
        $NewWindow.SizeToContent = "WidthAndHeight"
        $NewWindow.AllowsTransparency = $true
        $NewWindow.WindowStyle = "none"
        $NewWindow.Background = "Transparent"
        $NewWindow.WindowStartupLocation = "Manual"
        $Height = Get-Random -Maximum (([System.Windows.SystemParameters]::PrimaryScreenHeight) - 100)
        $Width = Get-Random -Maximum (([System.Windows.SystemParameters]::PrimaryScreenWidth) - 100)
        $NewWindow.Left = $Width
        $NewWindow.Top = $Height

        # Add a label control for the fruit
        $NewLabel = New-Object Windows.Controls.Label
        $NewLabel.Height = "150"
        $NewLabel.Width = "400"
        $NewLabel.Content = $_.Name
        $NewLabel.FontSize = "100"
        $NewLabel.FontWeight = "Bold"
        $NewLabel.Foreground = $_.Value
        $NewLabel.Background = "Transparent"
        $NewLabel.Opacity = "100"

        # Add an event to close the window when clicked

        # Add an event to change the cursor to a hand when the mouse goes over the window
        $this.Cursor = "Hand"

        # Display the window
        $NewWindow.Content = $NewLabel

# Show the window
if (!$psISE)
    # Hide PS console window
    $windowcode = '[DllImport("user32.dll")] public static extern bool ShowWindowAsync(IntPtr hWnd, int nCmdShow);'
    $asyncwindow = Add-Type -MemberDefinition $windowcode -name Win32ShowWindowAsync -namespace Win32Functions -PassThru
    $null = $asyncwindow::ShowWindowAsync((Get-Process -PID $pid).MainWindowHandle, 0) 

    # Run as an application in it's own context
    $app = [Windows.Application]::new()

Fixed: ConfigMgr Task Sequence Monitor :)

Good news 🙂  After several people reported that the ConfigMgr Task Sequence Monitor app would close immediately after opening, I investigated and found a small bug in the code that seems to affect only certain configurations.  Today I released version 1.2.1 which addresses this issue, so please download the app again from the following link:

Thanks to everyone who reported the bug!


New book! Deploy, Manage and Update Java Runtime with Configuration Manager and PowerShell. Available on Amazon.

I’m pleased to announce the release of my first IT Solution Guide – Deploy, Manage and Update Java Runtime Environment in the Enterprise with System Center Configuration Manager and PowerShell.  (Wow that’s a mouthful!)


This step-by-step guide demonstrates how Configuration Manager can be used together with PowerShell to create a solution for managing the Java Runtime Environment in your organisation.  I cover:

  • Using the Application model to deploy Java with some custom scripting to uninstall previous versions and keep your registry clean
  • Using Compliance Settings to control how Java is configured on your client machines, for example locking settings in the Java Control Panel and ensuring the configuration is not changed
  • Using PowerShell to automate the task of deploying a new Java version
  • Reporting on your Java deployment/s, using either the built-in tools or using the provided custom deployment report created using Microsoft Excel with a SQL Server database connection

The solution would be particularly useful for enterprises that are security-conscious, and want to be able to control which Java version is installed and how it is configured on their Windows clients.  It would also be useful for IT administrators that need to deploy new versions of Java frequently and want to simplify the update process.  The framework of the solution could be customized and used to deploy and manage other applications that require frequent updates.

Available now worldwide on Amazon in Paperback and Kindle editions.   CreateSpace


Creating Dynamic Reports for Configuration Manager with Microsoft Excel

Today I reached 10,000 hits on my blog, very cool, and what better way to celebrate than with a new post 🙂  Thanks to everyone who has stopped by, I hope that you have found something helpful and continue to do so!

In today’s post, I want to look at how to create dynamic reports for ConfigMgr using Microsoft Excel.  Excel has the ability to create and store a SQL server connection, so we can use it to talk directly with the ConfigMgr database and pull data into our worksheets, where we can summarize, combine, perform functions, display charts etc.  In addition, the SQL connection can be refreshed meaning that the report can always have the most recent data from the database. Excel is a very handy tool to use to create reports from the simple to the complex, and is a great option for use with Configuration Manager.

What about SSRS?

The built-in reporting capability of Configuration Manager is of course very good.  The SQL Server Reporting Services reports are all predefined for you and allow you to get some very useful information.  You can also create custom reports, subscriptions, export data and share reports with interested parties.  But I find there are still some limitations that can make Excel an attractive alternative when creating custom reports.  For one thing, finding the data you want can sometimes mean drilling through more than one report and selecting various options.  And then I often find that the data I want is not readily available in a single report and I have to create a custom report to get it.  But creating custom reports with SSRS is a bit of a slow and cumbersome process.

Excel has some advantages here worth considering:

  • You can create multiple worksheets and use multiple SQL queries to return your results into a single workbook
  • Creating charts to visually display the data is quick and easy
  • Data can be persisted to create a ‘snapshot’ report, or dynamically refreshed to keep the report always-up-to-date
  • You can leverage the power of both SQL language and Excel formulas to manipulate your data

I have used Excel for reporting to great effect, and if time permits I will share some of the reports I’ve created in future posts, including a spreadsheet to dynamically monitor SCCM OS Deployments and a single report that will get the current deployment status of any ConfigMgr Application.

In this post, I will focus on the fundamentals and create a simple report that will give me a dynamic inventory of my servers with various data such as make, model, OS version, IP address, MAC address, RAM, disk space, serial tag etc


The first thing to do is to decide which account will be used to get access to the ConfigMgr database.  If you are creating a report that is for your own use only, then there is no need to use specific account credentials, you can use the built-in Windows authentication option (assuming you have access to the database with your account).  If your report will be made available to other users, then those users will need at least read access (db_datareader) to the SQL database so the SQL query that runs in the background has permission to do so in their user context.  There is, however, a viable alternative if you don’t want to assign SQL permissions to all the users who need to view the report – you can create a local SQL account that has db_datareader access to the database, and embed these credentials in the report.  There is a security consideration here, as the local SQL credentials will be stored in clear text within the workbook, but since the permission level is read-only that should not be a major concern, and this is the method I will use in this post.

Create a Data Connection

Open Microsoft Excel (I’m using 2013, but it’s almost the same in 2010) and go to the Data ribbon. In the Get External Data section, click From Other Sources, then From SQL Server.
In the Data Connection Wizard window, enter the name of your SQL server and instance that hosts the ConfigMgr database, and enter the SQL credentials to connect to it.
dataconn1Select the ConfigMgr database in the drop-down list.  At this point we cannot enter a specific SQL query, so we have to pull in any random table for now, then we’ll update the connection later.

dataconn2Click Next.  You can change the File Name and Friendly Name of the data connection to make it more obvious as to what data it is getting, and to distinguish it from other data connections you may create in the workbook.


Click Finish.

Edit the Connection Properties

At the Import Data window, click on Properties.

dataconn4In the Connection Properties window, on the Usage tab, make sure that Enable background refresh is selected, and also Refresh data when opening the file.

This allows us to run the SQL queries in the data connections automatically every time we open the Workbook, and also perform a manual refresh when desired, from the Data ribbon. Optionally, you can also refresh the data regularly using the Refresh every .. minutes option.

dataconn5On the Definition tab, check the option Save password.  You will be prompted to allow the saving of the password unencrypted in the Excel file.  Click Yes.

dataconn6In the Command Type field, change to SQL, then enter your SQL query in the Command text field.  In this example, I’m using the following query which gives me an inventory of all my servers (change the sys.Name0 like ‘%srv%’ statement in the where clause to suit your environment):

select sys.Name0 as 'Server Name',
 case when comp.DomainRole0 = 3 then 'Server'
 when comp.DomainRole0 = 4 then 'Domain Controller'
 when comp.DomainRole0 = 5 then 'Primary Domain Controller'
 End as 'Server Type',
comp.Manufacturer0 as 'Manufacturer', comp.Model0 as 'Model', comp.Roles0 as Roles, os.Caption0 as 'OS', os.CSDVersion0 as 'SP Level',
comp.SystemType0 as 'Architecture',
os.Description0 as Description, nic.IPAddress0 as 'IP Address', nic.MACAddress0 as 'MAC Address', cpu.Name0 as 'CPU', comp.NumberOfProcessors0 as 'No. of Processors',
CAST(CAST(comp.TotalPhysicalMemory0 AS NUMERIC) / 1024 / 1024 as DECIMAL(10,2)) as 'Total Physical Memory (GB)',
disk.Partitions0 as 'No. of Disk Partitions (Primary Disk)', CAST(CAST(disk.Size0 as NUMERIC) / 1024 AS DECIMAL(10, 2)) as 'Total Disk Capacity (GB) (Primary Disk)',
bios.SerialNumber0 as 'Serial Tag', os.InstallDate0 as 'OS Install Date', os.LastBootupTime0 as 'Last Bootup Time',
sys.Last_Logon_Timestamp0 as 'Last Logon Timestamp', sys.User_Name0 as 'Last Logged On User'
from v_R_System sys
inner join v_GS_Operating_System os on sys.ResourceID = os.ResourceID
inner join v_GS_Computer_System comp on sys.ResourceID = comp.ResourceID
inner join v_GS_PC_BIOS bios on sys.ResourceID = bios.ResourceID
inner join v_GS_Network_Adapter_Configuration nic on sys.ResourceID = nic.ResourceID
inner join v_GS_Processor cpu on sys.ResourceID = cpu.ResourceID
inner join v_GS_Disk disk on sys.ResourceID = disk.ResourceID
where os.ProductType0 in ('2','3') and nic.IPAddress0 is not null and cpu.DeviceID0 = 'CPU0' and sys.Name0 like '%srv%' and disk.MediaType0 = 'Fixed hard disk media' and disk.DeviceID0 = '\\.\PHYSICALDRIVE0'
order by 'Server Name'

You can expand the window if you have a large query and need to see more of it.


Click OK, and you will be prompted that the connection has now changed in the Workbook, click Yes to accept.

Back in the Import Data window, make sure that Table is selected and the cell range =$A$1 in the Existing worksheet, then click OK.


The query will  run and pull the data into the worksheet.  Now I have a nice inventory of my servers:


You can see that it places a filter on all the column headers, making it easy to filter your data.

Edit the SQL Query

If you need to edit your SQL query, you can do so from the Data ribbon.  Click Connections, select the data connection and click Properties.

con3You can now go to the Definition tab and edit your SQL query as required.

Microsoft Query

An alternative way to get data from ConfigMgr into Excel is to use Microsoft Query.  This is a kind of ‘gui’ way to construct a query and may be useful if you are not familiar with creating SQL queries directly, although I find it easier to create the queries myself!

We won’t cover Microsoft Query in this post, but to get started with it, simply click on the Data ribbon in Excel, and choose Get External Data > From Other Sources > Microsoft Query.

Querying the SQL Database

Your report will only be as good as your SQL querying skills, your Excel skills and your knowledge of the ConfigMgr database of course, but on the latter Microsoft has provided some nice documentation (here) to help you find your way around the available SQL views.  In most cases you will query the views not the tables as they are designed for that, so take a look through the Microsoft documentation to help you identify where to find the data you need.  If you are familiar with querying WMI for ConfigMgr, then you should already find the database friendly as many of the views have similar names to the classes used in WMI.

Complex Reports

This was a simple example of a report that is not much different than running a WQL query in ConfigMgr directly.  However, you can begin to understand the potential here if you create additional worksheets with their own data connections, and you have multiple SQL queries pulling data into one report.  You can then create a summary sheet, for example, that summarises data from each of the worksheets, and populates graphs for a visually pleasing display.  You can store this report in any network share and control access by NTFS permissions, or make it available in a document repository such as Sharepoint.  The next time someone asks you for some data from ConfigMgr, you can simply send them a link.

Happy reporting!