Verify SQL version via Compliance Settings in ConfigMgr 2012

This time I will do a short post about verifying the SQL version(s) via Compliance Settings. I think everybody knows the inventory post for SQL version by Sherry Kissinger, but what if you simply want to know if all your devices are compliant with the company standard (of for example SQL Server 2012 SP1 CU5). Well, this blog post will provide a simple answer to that question, by providing a SQL query –type Configuration Item.

Configuration Item

The configuration is actually quite simple, as I can simply take advantage of the SELECT @@VERSION query statement. One small thing to take into account, is the fact that the configuration item requires a column to be specified and this queries doesn’t use/ create a named column. The easiest way to work with this is a simple adjustment of the query statement to SELECT @@VERSION AS Version. Now version can be used as the column name. To complete the configuration of the item, the following settings can be used (on the left the general settings and on the right the compliance rule settings).

General Compliance rule
Name: Version
SQL Server instance: All instances
Database: master
Column: Version
Transact-SQL statement: Select @@VERSION AS Version
The setting must comply with the following rule: Version Begins with Microsoft SQL Server 2012 (SP1) – 11.0.3373.0 (X64)
SQLVersion SQLVersion_CR

Results

Now lets take a look at the compliancy results of a server running SQL Server 2012 SP1. Of course it’s not really exiting to look at a compliant server, so I picked a non-compliant server with SQL Server 2012 SP1 (and KB2793634). The nice thing about a non-compliant server is that the results are more detailed. It will show the configuration, the expression and the current value. Note that this is the client-side report. SQLVersion_Result

Share

Leave a Comment