SQL version relevance

Content Authoring
1

I’m trying to get the SQL version such as SQL Server 2014, 2016, 2017, etc by querying the currentversion in the registry. I’m having trouble getting it to work as it seems to keep returning . Can anyone help me fix it. I’m sure it can be written more efficiently too so if you can see a better way please let me know. I’m very novice with relevance queries. Here’s what I have so far that isn’t working. Thanks

if(exists value "CurrentVersion" of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion" of native registry)
then (
  if((it starts with "14.") of value "CurrentVersion" of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion" of native registry)
  then "SQL Server 2017" 
  else if((it starts with "13.") of value "CurrentVersion" of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion" of native registry)
  then "SQL Server 2016" 
  else if((it starts with "12.") of value "CurrentVersion" of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion" of native registry)
  then "SQL Server 2014" 
  else if((it starts with "11.") of value "CurrentVersion" of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion" of native registry)
  then "SQL Server 2012"
  else if((it starts with "10.50.") of value "CurrentVersion" of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion" of native registry)
  then "SQL Server 2008 R2" 
  else if ((it starts with "10.0.") of value "CurrentVersion" of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion" of native registry)
  then "SQL Server 2008" 
  else if((it starts with "9.") of value "CurrentVersion" of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion" of native registry)
  then "SQL Server 2005" 
  else if((it starts with "8.") of value "CurrentVersion" of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion" of native registry)
  then "SQL Server 2000" 
  else "UNKNOWN"
)
else ""
2

Have you seen this ?

https://bigfix.me/analysis/details/2994632

For future portability, I’d probably start with something like

if (exists values "CurrentVersion" of keys "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion" of native registry as string as version) then items 1 of ("10.0","SQL Server 2008";"10.50","SQL Server 2008 R2";"11","SQL Server 2012") whose (item 0 of it as version = value "CurrentVersion" of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion" of native registry as string as version) else ""

I don’t have one handy to check, but my guess is your original problem is that you need to cast the registry value “as string” before you could use “it starts with” against the result. i.e.

if((it as string starts with "8.") of value "CurrentVersion" of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion" of native registry)
then "SQL Server 2000" 
else "UNKNOWN"
3

That link is very useful, thanks!

You’re right, I just needed to cast it with “as string”. I like your version much better using the array with “items” and trying to expand on that. I notice it reports back “none” if there is a version that is not in the array such as version 12, 13, and 14. I know in the future I will run into the same problem when new versions come out but instead of showing “none”, can I make it default to say “UNKNOWN” so its more clear that the version hasn’t been accounted for? “none” can be confused with SQL not being installed so I want to make it clear for future updates. I’m sure it’s easy but my relevance skills aren’t advanced enough yet.

4

(I was afraid you’d ask that).

Yeah, you can do that, but it’s not nearly as clean. My first thought was you could wrap it in another comparison, so it would look something like

if (version exists) then (if (version matches my list) then (the match from my list) else “unknown version”) else “not installed”
…but this means having to parse through the list twice, and I don’t like having to duplicate a long list twice; too much chance for the two lists getting out of sync.

Then I thought of something better - we can change the plural “items 1 of” to a singular; if there’s not a matching number, the comparison would throw an error message “Singular expression refers to nonexistant object”. We can catch that error with the pipe ("|") operator to indicate “we have SQL but don’t recognize the version”.

q: if (exists values "CurrentVersion" of keys "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion" of native registry as string as version) then (item 1 of ("10.0","SQL Server 2008";"10.50","SQL Server 2008 R2";"11","SQL Server 2012") whose (item 0 of it as version = value "CurrentVersion" of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion" of native registry as string as version)| "<Unknown Version>") else "<Not Installed>"
A: Unknown Version
T: 0.206 ms
I: singular string

We can also, in that “Unknown Version” block, return the raw version number from the registry which might be helpful in updating the array values -

q: if (exists values "CurrentVersion" of keys "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion" of native registry as string as version) then (item 1 of ("10.0","SQL Server 2008";"10.50","SQL Server 2008 R2";"11","SQL Server 2012") whose (item 0 of it as version = value "CurrentVersion" of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion" of native registry as string as version)| "Unknown Version - " & value "CurrentVersion" of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion" of native registry as string) else "<Not Installed>"
A: Unknown Version - 12.0
T: 0.251 ms
I: singular string

That can be written just a little more cleanly, so that we don’t reference the registry path twice. In this query, I instead make a tuple of tuples, where the structure looks like
(registry version, (“10.0”, “SQL Server 2008”)
(registry version, (“11”, “SQL Server 2012”)
so I can reference “item 0 of it”, “item 0 of item 1 of it”, and “item 1 of item 1 of it”

q: if (exists values "CurrentVersion" of keys "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion" of native registry as string as version) then (item 1 of item 1 of (it,("10.0","SQL Server 2008";"10.50","SQL Server 2008 R2";"11","SQL Server 2012")) whose (item 0 of it as version = item 0 of item 1 of it as version) | ("Unknown Version - " & it as string)) of  (value "CurrentVersion" of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion" of native registry as string as version) else "<Not Installed>"
A: SQL Server 2008
T: 0.185 ms
I: singular string

or (when I put in an unrecognized version)

q: if (exists values "CurrentVersion" of keys "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion" of native registry as string as version) then (item 1 of item 1 of (it,("10.0","SQL Server 2008";"10.50","SQL Server 2008 R2";"11","SQL Server 2012")) whose (item 0 of it as version = item 0 of item 1 of it as version) | ("Unknown Version - " & it as string)) of  (value "CurrentVersion" of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion" of native registry as string as version) else "<Not Installed>"
A: Unknown Version - 12.0
T: 0.171 ms
I: singular string
4 Likes
5

Thank you so much. That is genius. You must be really good at puzzles. I’m learning alot from your examples and explanations. This should be included in the analysis https://bigfix.me/analysis/details/2994632

6

I have a similar use case to this that I am stuck on. I need to get the SQL Server Reporting Services version across different versions of SQL server installed.

Here are some of the locations where the version is stored:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSRS10_50.MSSQLSERVER

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSRS10.MSSQLSERVER\MSSQLServer\CurrentVersion

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSRS13.MSSQLSERVER\MSSQLServer\CurrentVersion

My question is what is the approach to obtain the CURRENT VERSION in a consolidated property analysis if the path is different across the versions?

7

Check these examples:

This should be basically what you are after:

keys "MSSQLServer\CurrentVersion" of keys of keys "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server" of (x64 registries; x32 registries)

This is equivalent of HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\*\MSSQLServer\CurrentVersion

1 Like
8

Thanks for the speedy reply! This seems to work with the exception of “Error: Singular expression refers to non-unique object.” being included in the output as well.

9

What is the relevance you are using? my relevance doesn’t have any singulars.

10

value "CurrentVersion" of keys "MSSQLServer\CurrentVersion" of keys of keys "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server" of (x64 registries; x32 registries)

11

make, “value” into “values”:

(it as string) of values "CurrentVersion" of keys "MSSQLServer\CurrentVersion" of keys of keys "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server" of (x64 registries; x32 registries)
12

We are getting closer. I am getting all versions now it is finding:

13.0.1601.5
13.0.1601.5
13.0.1601.5
13.0.1601.5
13.0.1601.5
13.0.1601.5


Evaluation time: 1.059 ms

Perhaps I need to be a bit more specific with the key or identify something unique about the location to only pull the SQL Server Reporting Services version? I noticed each one of the keys starts with “MSRS”. Perhaps we can filter for this specific version by that?

13

Just add this to the front:

 Unique values of

Yes, if that is the only one you want then you’d also filter the list for only that.

 Keys whose(name of it as lowercase starts with “msrs”)

But without the smart quotes

1 Like
14

Worked like a charm. Thank you! Here is the final relevance:

(it as string) of values "CurrentVersion" of keys "MSSQLServer\CurrentVersion" of keys whose(name of it as lowercase starts with "msrs") of keys "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server" of (x64 registries; x32 registries)

1 Like
15

Yay!

You might still want to put:

Unique values of

On the front, even if you think there should only be one result, because sometimes there are more

1 Like
16

Done! Thank you so much!! Final Relevance:

Unique values of (it as string) of values "CurrentVersion" of keys "MSSQLServer\CurrentVersion" of keys whose(name of it as lowercase starts with "msrs") of keys "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server" of (x64 registries; x32 registries)

2 Likes