SQL Version Detection and Reporting with SCCM

Feb 8, 2016 · 4 min read

SQL version reporting with SCCM done right…

By default, the SQL information that can be gathered with SCCM is incomplete. The version, service pack and cumulative update information are available in WMI, but are not stored in the cimv2 namespace. In order to be able to gather the relevant SQL information, a hardware inventory extension is required.

Notes
ALWAYS BACK-UP YOUR CONFIGURATION.MOF FILE BEFORE ANY CHANGES!
TEST THE CONFIGURATION.MOF USING MOFCOMP.EXE ON A TEST MACHINE FIRST!
HWI EXTENSION NEEDS TO BE DONE ON THE TOP OF YOUR HIERARCHY, CAS IF YOU USE ONE, PSS IF YOU DON’T.

Add SQL extensions to the Configuration.mof file

You can find the the Configuration.mof file in <CMInstallLocation>\Inboxes\clifiles.src\hinv\

Look for the following section at the end of the Configuration.mof file:

//========================
// Added extensions start 
//========================//========================
// Added extensions end 
//========================

Inside this section paste the following extension:

SQL Information extension

SCCM HWI Extension for SQL Version Detection to paste in the Configuration.mof file

Notes
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.

Add Custom Classes to Hardware Inventory

SQL Information Definitions

Code posted only for reference, you can download the file below.

Code posted just for reference, you can download the file below

Test and Implement HWI extension

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

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.

Either use a test environment or compile the file on a test machine first. After the file complies successfully you can check the WMI to see if the classes were created.

Compile configuration.mof

## Check syntax
mofcomp.exe -check {Configuration.mof Location}\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.
*/mofcomp.exe {Configuration.mof Location}\Configuration.mof

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

Check WMI for the new classes

## 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#  Get SQL 2014 class
Get-CimClass -ClassName SQL_2014#  Get SQL 2012 class
Get-CimClass -ClassName SQL_2012#  Get SQL 2008 class
Get-CimClass -ClassName SQL_2008#  Get SQL Legacy class
Get-CimClass -ClassName SQL_2000_And_2005

Implement changes on the CAS/PSS

Compile the configuration.mof file

## 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.
*/mofcomp.exe <CMInstallLocation>\Inboxes\clifiles.src\hinv\Configuration.mof

Import the new HWI definitions

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

Download Definitions

## SQL classes definition file
HWI DEF SQL Classes.mof

Import definitions

Click on Import and select the ***HWI DEF SQL Classes.mof*** file

Make sure the SQL Classes are enabled and click OK

Import the SSRS Report

Download the report file

## SQL version information report (Octavian Cordos)
SW SQL Server Products.sql

Upload reports to SSRS

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

Configure imported reports

Replace the DataSource in the reports.

Notes
Allow some time for the policy to be downloaded of force a policy refresh to get the new WMI classes.
Allow some time for the data to be gathered or force a HWI collection.
This report was created with SQL 2014 Reporting Services, you might need to remove some report elements if you use an older version.

Report Query

For reference only, since the report includes this query.

Report query posted just for reference, you can download the Report above.

Report Preview

Please, Subscribe and Clap for this article! It makes a difference. Thanks!

Use Github for 🐛 reporting, or 🌈 and🦄 requests