How to Determine SQL Server Edition

Reporting
1

(imported topic written by Shogo23)

Hi,

Is there a way for BigFix to return the edition (Standard, Developer, Enterprise) of SQL Server installed on a system? SQL does not differentiate itself in Add/Remove Programs, so the edition does not show up in the Installed Applications property. On a few of my systems, I did find the value “Edition” under HKLM\Software\Microsoft\MSSQLServer\Setup, but it does not exist on most. Microsoft’s prescribed method of detemining edition is to run a query against the database, per http://support.microsoft.com/kb/321185.

As I’m sure you know, SQL licensing costs vary considerably depending on the edition installed, which is why I need this information.

Thanks,

Don

2

(imported comment written by BenKus)

Hey Shogo,

That is a challenge… but you can use the trick of running an action to output the data you want and then retrieving it in a property:

For instance, try something like this… make a Custom Task:

Relevance:

exists local mssql database

Action:

waithidden cmd.exe /C osql -E -Q “SELECT CONVERT(CHAR(30), SERVERPROPERTY (‘edition’))” -o “{pathname of parent folder of regapp “BESClient.exe” & “\sqledition.txt”}”

Now make a Custom Analysis with the relevance:

exists file (pathname of parent folder of regapp “BESClient.exe” & “\sqledition.txt”)

and add a property to the analysis with the relevance:

lines whose (it as lowercase contains “edition”) of file (pathname of parent folder of regapp “BESClient.exe” & “\sqledition.txt”)

Now run this task on your SQL Servers and activate this analysis and you should get your results…

I didn’t test any of this, but hopefully it works for you…

Ben

3

(imported comment written by SystemAdmin)

Ben Kus

waithidden cmd.exe /C osql -E -Q “SELECT CONVERT(CHAR(30), SERVERPROPERTY (‘edition’))” -o “{pathname of parent folder of regapp “BESClient.exe” & “\sqledition.txt”}”

Wouldn’t it be nice if you could backquote a system command within relevance to have it return the output immediately, so you wouldn’t have to create an action\property pair… :slight_smile:

I guess that comment belongs in the wishlist section. heh

4

(imported comment written by jessewk)

As Ben notes in comment #6 here (http://forum.bigfix.com/viewtopic.php?id=822) any type of action execution from relevance is explicitly prohibited by our design. The reasons are based both in security and efficency.

If you have further questions after reading Ben’s comment, I encourage you to start a new thread where we can discuss this issue. This topic comes up somewhat frequently and it would probably be good to have a central thread to reference.

Regards,

Jesse