Accurately inventory and report on Microsoft SQL Server Instances with SCCM 2012

It is ironic that Configuration Manager provides such basic inventory capabilities for products such as Microsoft SQL Server and Microsoft Exchange.

This solution addresses the limitations of attempting SQL Server inventory with the native inventory information available.

Specifically this solution provides the following information for SQL Server versions, tested as far back as SQL 2005, and provides the following information for all SQL Instances detected:-

  • Collation
  • Edition
  • EditionType
  • FeatureList
  • InstallPath
  • InstanceName
  • Language
  • PatchLevel
  • ProductCode
  • ProductID
  • ServicePack
  • Version

An example of the inventory data is shown below:-

SQL Inventory 1

An example of the provided report is shown below:-

SQL Inventory 2

A high level overview of the solution is listed below:-

1) The Configuration Item (script) is edited and the registry key path containing the SQL information is added / modified as required (Please see the note above).

Note: This solution works in tandem with the registry collection solution found here. This must be implemented first.

2) The Baseline is assigned to a group of devices

3) The baseline executes and creates registry keys for each instance detected in a single registry location and stores all relevant instance inventory information strings.

4) The single registry location (and all child keys and values) is collected as part of the registry collection process and made available for reporting.

The solution zip file contains 2 elements:-

  • The Baseline Cab – See the compliance baseline import section below
  • The script file – The raw PowerShell code should any alterations be required

The zip file can be download here.

Step 1 – Configuration Baseline Import

1. Under Assets and Compliance. Right click Configuration Items and Select Import. Select Add and browse for and select the SQL Inventory.cab file contained within the zip file above.

SQL Inventory 3

2. Once the import is completed. Right click the SQL Inventory Configuration Item and edit the SQL Inventory script.

SQL Inventory 4

3. Modify the ROOT_PATH variable. 

Note: This should be a location being collected by the Registry Inventory solution discussed above.

SQL Inventory 5

Step 2 – Deploy the Configuration Baseline

1. Right click the SQL Discovery Configuration Baseline and deploy this to a collection of your choosing.

SQL Inventory 6

Note: You should always trial anything new in the lab and ensure you check the contents of any files carefully.

Step 3 – Baseline Evaluation and Hardware Inventory

1. During the next baseline evaluation the script will execute and search for and store all instance information in the nominated collection registry key location.

The Registry collection evaluation will run and store all registry information which is collected by hardware inventory and available for reporting.

SQL Inventory 7

Step 4- Importing the SQL Reports

SQL Inventory 2

1. Please download the Reports here.

2. Follow this tutorial on how to perform the import.

Version Updates:-

  1. N/A

License Information:-

Copyright 2016 Esebenza Limited. This program is distributed under the terms of the GNU General Public License.

Esebenza provides all tutorials, code and articles as is with no warranty or recourse to support. Should you experience any issues during deployment Esebenza can not be held liable.