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