SQL Server Product and Version Reporting with SCCM
SQL licensing is always a pain but this report should make it a little easier…
🚨🚨 IMPORTANT NOTICE🚨🚨
🦄 An updated version can be found at MEM.Zone 🦄
🚨🚨 IMPORTANT NOTICE🚨🚨
Originaly published on sccmf12twice.com
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.
Recommendations
- 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!
Notes
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.
Installation
Prerequisites
- 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 <CMInstallLocation>\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
//========================
Notes
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 <CMInstallLocation>\Inboxes\clifiles.src\hinv\Configuration.mof
HWI Definitions
You need to add the new class definitions to the Default Client Settings
- Import definitions.
Notes
DO NOT DELETE the old extension definitions if you still want to use the old report!
Test and Validation
Configuration.mof
Use mofcomp.exe to check if configuration.mof was correctly modified, and implement the changes.
## Check syntax
mofcomp.exe -check <Configuration.mof_Directory>\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_Directory>\Configuration.mof
Notes
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.
WMI
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 2019 class
Get-CimClass -ClassName SQL_2019_Property# 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
Database
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://<YOUR_REPORT_SERVER_FQDN>/Reports
- Choose a path and upload the previously downloaded report file.
Configure Imported Report
- Replace the DataSource in the 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 <SITE_CODE> in the USE statement to match your Site Code.
- Click Execute to add the usp_PivotWithDynamicColumns stored procedure to your database.
Notes
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.
Preview
Code
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.
Notes
Credit to Jakob Bindslet and Chrissy LeMaire.
Please, Subscribe and Clap for this article! It makes a difference. Thanks!
Use Github for 🐛 reporting, or 🌈 and🦄 requests