SQL Server Product and Version Reporting with SCCM

, , , , , , , , , ,

SQL licensing is always a pain but this report should make it a little easier…

Report release history

Previous report version

Also published on my blog

This is the second iteration of my SQL version report. When I look back on my previous work I always cringe and this was no exception. A while back, I received a request to add the SQL key to the report, so I began examining the old code. Horrified by the things that I found laying dormant there, I scrapped everything and started anew.

The report is brand new with a lot more info, smaller database footprint and much better coding.


  • Do not modify or revemove the previous version until you verify that this version has all the data you need!
  • Use a test environment for validation!
  • Back-up your configuration.mof file before any changes!
  • Test the configuration.mof using mofcomp.exe on a test machine first!

This version is compatible with the previous version, they can live side by side.

Hardware inventory extension needs to be done on the top of your hierarchy.



  • Test environment
  • Downloads (Right click →Download linked file)
# HWI Extension
HWI EXT SQL Server Products.mof
# HWI Definitions
HWI DEF SQL Server Products.mof
# SSRS Report
SW SQL Server Products.rdl
  • SQL Stored Procedure →‘Create the SQL Stored Procedure’ section.

HWI Extension

The extension needs to be added to the configuration.mof file in \Inboxes\clifiles.src\hinv\

  • Look for the section below at the end of the configuration.mof file. The extension needs to be added between the “Added extensions start/end” headers.
  • Uncomment the “Old SQL extension cleanup” section to remove the old extension classes from the clients repository if needed.
  • Use a test environment for validation as described in the ‘Test and Validation’ header after the Installation section.
// Added extensions start 
// Added extensions end 

Always use a test environment before any changes in production!

Never create any extensions outside of the “Added extensions start/end” headers.
Try to have consistent formatting inside these headers.
Never modify anything outside these headers.
Watch for other previous extensions and use clear delimitation between them.

  • Apply changes in production
## Compile file on the CAS/PSS
Compiling the configuration.mof file in the hinv folder on the CAS/PSS, will trigger the distribution and compilation on all machines in your environment on the next machine policy evaluation.
mofcomp.exe \Inboxes\clifiles.src\hinv\Configuration.mof

Implement HWI extension in production

HWI Definitions

You need to add the new class definitions to the Default Client Settings

  • Import definitions.

Click on Import and select the HWI DEF SQL Server Products.mof file

Review the classes and click on Import.

Make sure the new extension classes are enabled and click OK.

DO NOT DELETE the old extension definitions if you still want to use the old report!

Test and Validation


Use mofcomp.exe to check if configuration.mof was correctly modified, and implement the changes.

## Check syntax
mofcomp.exe -check \Configuration.mof
## Compile file 
Compiling the configuration.mof file in the hinv folder on the CAS/PSS, will trigger the distribution and compilation on all machines in your environment on the next machine policy evaluation.
mofcomp.exe \Configuration.mof

Compling the configuration.mof is done on a test environment here!

Saving and compiling the configuration.mof file in the hinv folder on the CAS/PSS, will trigger the distribution and compilation on all machines in your environment on the next machine policy evaluation.


Use PowerShell to check if the new classes have been created in WMI

## Check if the new classes are present in WMI
/* The machine must have at least one version of SQL installed in order for these classes to be created */
#  Get SQL 2017 class
Get-CimClass -ClassName SQL_2017_Property
#  Get SQL 2014 class
Get-CimClass -ClassName SQL_2014_Property
#  Get SQL 2012 class
Get-CimClass -ClassName SQL_2012_Property
#  Get SQL 2008 class
Get-CimClass -ClassName SQL_2008_Property
#  Get SQL Legacy class
Get-CimClass -ClassName SQL_Legacy_Property
#  Get SQL ProductID class
Get-CimClass -ClassName SQL_ProductID


Use SSMS (SQL Server Management Studio) to check if the views are created in the CM database

Import the SSRS Report

Upload Report to SSRS

  • Start Internet Explorer and navigate to http:///Reports
  • Choose a path and upload the previously downloaded report file.

Configure Imported Report

Create the SQL Stored Procedure

The usp_PivotWithDynamicColumns is needed in order to maximize code reuse and have a more sane and sanitized data source.

  • Copy paste the code below in SSMS
  • Change the in the USE statement to match your Site Code.
  • Click Execute to add the usp_PivotWithDynamicColumns stored procedure to your database.

You might need additional DB access to install the support function!
Allow some time for the policy to be downloaded or force a policy refresh.

Allow some time for the data to be gathered or force a HWI collection.
This report was created with SQL 2017 Reporting Services, you might need to remove some report elements if you use an older version.


Report preview


HWI Extension

For reference only, you can download the file in the ‘Prerequisites’section.

HWI Definitions

For reference only, you can download the file in the ‘Prerequisites’section.

SQL Query

For reference only, the report includes this query.

VB Support Function

For reference only, the report includes this function.

Credit to Jakob Bindslet and Chrissy LeMaire.

Use Github for 🐛 reporting, or 🌈 and🦄 requests

Finding Empty Device Collections in SCCM

, , , , , , , ,

Today’s script is going to be almost a one-liner, but a very useful one.  I had found SQL queries that would do this same thing, but I frequently find myself working on a computer that does not have the SQL management software installed on it, and this query doesn’t run cleanly through the SCCM Management software.  However, I’m always on a computer with PowerShell and since this runs through WMI, you don’t even need to connect to the SCCM site code drive.
Having the right amount of device collections in SCCM is a bit of a balancing act. You don’t want so few collections that you don’t have a good way to sort out your clients logically, but you don’t want so many that you’ve gunked up your database with entries you’re never actually going to use. On top of that, some of the companies I’ve contracted with have had many collections that never held a single client. This script will go through and find those empty collections for you.   If you just want the PS1 file, click HERE.

Step 1: Initializing a few variables
Typically, if I’m working with SCCM through WMI, I like to setup a few variables in my shell right away to make my life easier and save some keystrokes throughout the day.
$siteServer = “PriServer1″ # whatever the name of your primary server is
$namespace = “root/SMS/site_TST” # root/SMS/site_  whatever your site code is

If you’re constantly making WMI calls to SCCM, being able to just enter those variables instead of typing it all out can make things easier. At least, that’s what I’ve found.

Step 2: Get your SCCM Device Collections
This script is going to be done on a single line, but I’ll break it out into chunks first. The first thing we need to do is pull all of the device collections from SCCM.
Get-WmiObject -Namespace $namespace -ComputerName $siteServer -Class SMS_Collection -filter ‘CollectionType =2’
If you wanted to pull user collections, you would change ‘CollectionType = 2’ to ‘CollectionType = 1’
If you want to get both sets of collections, just leave out the -filter portion entirely.

Step 3: Check Those Collections for Members
There are a couple ways you could do this. You could pipe your collections to a ForEach loop with an if statement in it. Alternatively, I choose to just pipe the first cmdlet into a where statement that does it for me.
| where {$colID = $_.CollectionID;(Get-WmiObject -Namespace $namespace -ComputerName $siteServer -class SMS_FullCollectionMembership -filter “CollectionID=’$colID'”).count -eq 0}

What this is doing is setting a variable called “$colID” to be the collection ID number from the collection passed through the pipe.  Then, we’re going to do a WMI query to the SMS_FullCollectionMembership class, which is a class that maintains a list of every device/user to collection association. We’re adding a filter to only show collection IDs that match the one we want, counting up all the members, and if that number equals 0, we know the collection has nothing in it.

Step 4: Profit
If you just stop there, you have a set of list-formatted data that, while not pretty, does contain all the information and objects you need to do work. It’s not the easiest thing to read, but it does give you a very solid information dump about the empty collection that looks something like this:

device collections


Chances are, if you’re generating this report for a manager, customer, etc, they’re going to want it formatted a bit nicer, and they’ll probably want it in an Excel spreadsheet.  The easiest way to do this is:
| select Name,CollectionID | export-csv C:usersMyUserDocumentsEmptyCollections.csv -noTypeInformation

 Alternatively, if you get the go-ahead to remove these device collections, instead of piping to a select/export-csv statement, you can pipe to remove-wmiobject instead.
For the love of god, run it with -whatif first.  

Thanks for reading, and if you have any questions, feel free to post them in the comments. 

SSRS – Client Health Dashboard

, , , , ,
Client Health Dashboard
In previous posts I was speaking about soon to be releasing a series of SSRS reports based on troubleshooting. The first in the series I am trying to have is a focus on Client Health Overview. This dashboard will later be include drill down functionality to multiple other reports as soon as I can finish making and validating them. This dashboard is something that I put together for my current customer to get a brief overview of client health. There will be a few more items added to the home page at a later time which will be focused on count vulnerable/unpatched systems
Currently we can see in this report Active clients, MP information, Client version counts, OS version counts. There are even a few top level items you should keep an eye on like duplicate systems, mac addresses, systems running out of space, or why clients failed to install/re-evaluate. These will all be further expaneded on when the full client health troubleshooting series wraps up over the next few weeks.
SSRS Client Health Dashboard

Link to the Report: https://gallery.technet.microsoft.com/SSRS-Client-Health-6bfb794f

Some code used from: Greg Ramsey and Eswor Koneti

Future Dashboards w/ drilldowns coming soon.

– Client Health (will be finished soon)

  • This will also include drill downs into several other reports

– Software Deployment (For Packages / For Apps)

  • This will also include how to troubleshoot just like this other report
    SSRS Client Health Dashboard
– Infrastructure health (Primary/DP/SQL/DP etc health focused)
  • This will also include how to troubleshoot just like this other report
  • This will also include drill downs into several other reports
– Windows Migration Summary
SSRS Client Health Dashboard


– Collection evaluation