Software version report issue

[RobertBasil]

Checking for a version (or greater) version of Googe Chrome than 80.0.3987.106

My query:.

Select Top 1000000 tblAssets.AssetName,
tblSoftware.softwareVersion
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID And
tblSoftwareUni.softwareName Like '%Google Chrome%' And
tblSoftware.softwareVersion >= '80.0.3987.106' And tblAssetCustom.State = 1

But the above also shows Google Chrome version 80.0.3987.87 because after the 3rd period 8 is greater than 1.

Any suggestions?

[RCorbeil]

If you're using SQL Server 2016, it appears that Microsoft has added a string_split() function you might be able to use. There are third parties out there who have created their own equivalent string-splitting functions that you could add to your kit and use. (That's just one Googled example.)

[RCorbeil]

Fair enough, and you're definitely right about your scenario. All I can think of offhand would be to expand the slice-and-dice substring extraction to accommodate more than three decimals, but it's already unwieldy handling only three.

Questioner levels here vary from total noob upward, so I was reading your messages at face value. No offense meant in assuming the basics weren't understood.

Good luck.

[RobertBasil]

RC62N wrote:
Fair enough, and you're definitely right about your scenario. All I can think of offhand would be to expand the slice-and-dice substring extraction to accommodate more than three decimals, but it's already unwieldy handling only three.

Questioner levels here vary from total noob upward, so I was reading your messages at face value. No offense meant in assuming the basics weren't understood.

Good luck.

Thank you VERY much for trying to help me with a solution. It's much appreciated!

[RCorbeil]

SQL Server can't understand that 106 > 87 because it's not seeing numbers; it's comparing the text "106" to the text "87". For all intents and purposes, you're asking it to compare "BAG" to "IH". The version "number" is a text string, so that's how the comparison works: it's a left-to-right text comparison, so right out of the gate "1" < "8".

This is why I suggested zero-padding the sliced-and-diced version "number". Instead of comparing the strings

80.0.3987.106
80.0.3987.87

where "1" is clearly < "8", you would be comparing (I'm inserting decimals for illustration)

00080.00000.03987.00106
00080.00000.03987.00087

In that case, the first three pieces would be identical, but by the time the third character of the last piece is compared, "0" (of "087") is clearly less than "1" (of "106").

[RobertBasil]

RC62N wrote:
SQL Server can't understand that 106 > 87 because it's not seeing numbers; it's comparing the text "106" to the text "87". For all intents and purposes, you're asking it to compare "BAG" to "IH". The version "number" is a text string, so that's how the comparison works: it's a left-to-right text comparison, so right out of the gate "1" < "8".

This is why I suggested zero-padding the sliced-and-diced version "number". Instead of comparing the strings

80.0.3987.106
80.0.3987.87

where "1" is clearly < "8", you would be comparing (I'm inserting decimals for illustration)

00080.00000.03987.00106
00080.00000.03987.00087

In that case, the first three pieces would be identical, but by the time the third character of the last piece is compared, "0" (of "087") is clearly less than "1" (of "106").

Don't get me wrong, I understand why it's doing it and how to work on a solution. But a lot of the testing and education software we use can have up to 8 decimal places in the version and can change how many decimal points it has for each version. (I know, I know, don't get me started on how stupid that is) so it would take forever to try an keep up with not only the version number, but the amount of decimal places that version number uses upon each "update" of the software.

Just for example, one piece of testing software we use and it's last 4 versions over the past school year...

2.434.6.8.5
2.434.7
2.434.7.5.12.3
2.435.1.15.65

Using your code if the version number happens to be shorter on the next version released the code won't work.

Most of the software updates on it's own, we just want to try and keep track of those endpoints that did not update automatically so we can update those systems by pushing a new version out to them. It's software we are required by the state to use (Education) so not only does the version number change, but it's maximum length changes almost every time as well. It would so much easier if we could just read it as a full number.

I wonder how PDQ Inventory does it on the back end. You can put a version number in for a collection and choose lower or higher and they have no problems with the amount of decimal places... you can even use Alpha characters and they can tell those alphabetically as well and they use an SQLite database

[RobertBasil]

I guess I just don't understand why it's so hard for an SQL query to understand that 80.0.3987.106 is a larger number than 80.0.3987.87

I know this is not LanSweepers fault, just frustrated.

[RCorbeil]

Unfortunately there's no consistency to the format of version numbers.

If you know you're going to be dealing with version numbers in the A.B.C.D format and you know the maximum length (maximum possible value) of each element, you could always zero-pad each piece and concatenate the result before doing your comparison. e.g. referring to the slice-and-dice, if the individual version number pieces can be assumed to never exceed 99,999,

SELECT Right('00000'+VersionA,5) + Right('00000'+VersionB,5) + Right('00000'+VersionC,5) + Right('00000'+VersionD,5)

would produce "00080000000398700106". It's not as efficient as a simple numeric comparison, but you could do comparisons on that padded-out string.

The assumption isn't possible if you're going to assess all your software, but if you're only looking at select titles that allow you to make assumptions about the version "number" format, it's an option.

[RobertBasil]

Thanks for the response, but holy crap. That's going to be a nightmare for us with over 300 pieces of software to try and track.

Looks like I'll have to shorten the tracking by major release versions only. 🙂

[RCorbeil]

The version "number" is stored as a string, so you're going to end up with an ASCIIbetical comparison. See if you can use the version slice-and-dice in this thread.