Notification

Icon
Error

Software version report issue

Posted: Friday, February 14, 2020 5:50:02 PM(UTC)
RobertB

RobertB

Member Original PosterPosts: 28
0
Like
Checking for a version (or greater) version of Googe Chrome than 80.0.3987.106

My query:.

Quote:
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?
RC62N
#1RC62N Member Posts: 429  
posted: 2/14/2020 6:25:58 PM(UTC)
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.
RobertB
#2RobertB Member Original PosterPosts: 28  
posted: 2/14/2020 6:44:39 PM(UTC)
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. :)
RC62N
#3RC62N Member Posts: 429  
posted: 2/14/2020 8:05:37 PM(UTC)
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,
Code:
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.
RobertB
#4RobertB Member Original PosterPosts: 28  
posted: 2/14/2020 9:10:23 PM(UTC)
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.

RC62N
#5RC62N Member Posts: 429  
posted: 2/14/2020 10:05:45 PM(UTC)
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
Code:
80.0.3987.106
80.0.3987.87
where "1" is clearly < "8", you would be comparing (I'm inserting decimals for illustration)
Code:
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").
RobertB
#6RobertB Member Original PosterPosts: 28  
posted: 2/14/2020 10:33:35 PM(UTC)
Originally Posted by: RC62N Go to Quoted Post
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
Code:
80.0.3987.106
80.0.3987.87
where "1" is clearly < "8", you would be comparing (I'm inserting decimals for illustration)
Code:
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
RC62N
#7RC62N Member Posts: 429  
posted: 2/14/2020 10:45:10 PM(UTC)
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.
RobertB
#8RobertB Member Original PosterPosts: 28  
posted: 2/14/2020 10:57:26 PM(UTC)
Originally Posted by: RC62N Go to Quoted Post
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!
RC62N
#9RC62N Member Posts: 429  
posted: 2/17/2020 4:07:20 PM(UTC)
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.)

Active Discussions

Lansweeper SQL Triggers
by  Kyle Zimmerman   Go to last post Go to first unread
Last post: 3/28/2020 9:43:26 PM(UTC)
Lansweeper Access LS Knowledge base from outside network
by  Hagobian  
Go to last post Go to first unread
Last post: 3/28/2020 12:46:44 AM(UTC)
Lansweeper Wake on Lan wol.exe issue
by  Socal_s197   Go to last post Go to first unread
Last post: 3/27/2020 10:05:42 PM(UTC)
Lansweeper Scanning Queue locked
by  KevinA-REJIS  
Go to last post Go to first unread
Last post: 3/27/2020 8:48:45 PM(UTC)
Lansweeper Asset Radar requirements
by  bbeavis   Go to last post Go to first unread
Last post: 3/27/2020 6:58:20 PM(UTC)
Lansweeper LSAgent Cloud Relay error
by  nlertn  
Go to last post Go to first unread
Last post: 3/27/2020 6:35:55 PM(UTC)
Lansweeper Scanning IP Range Subnet
by  WeatherDave   Go to last post Go to first unread
Last post: 3/27/2020 5:39:17 PM(UTC)