07-28-2022
12:14 PM
- last edited on
04-02-2024
07:48 AM
by
Mercedes_O
Dears,
I am trying to make a report that would get all the 'outdated' software and shows me all the assets running the outdated softwares in one report.
I am comparing the software version with MaxVersion.SoftwareVersion but the result are not accurate.
Could you please help making this report possible?
Thanks.
08-03-2022 03:25 PM - edited 08-03-2022 03:32 PM
Hello,
You can run this report to see the current software version vs the max software version found for the same software in your inventory:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
SU1.softwareName,
S1.softwareVersion
,
(Select max(CONVERT(DECIMAL(18,12),
CASE WHEN (charindex('.',S2.softwareVersion) > 0) THEN Substring(S2.softwareVersion,0,charindex('.',S2.softwareVersion)) + '.' + REPLACE(Substring(S2.softwareVersion, charindex('.',S2.softwareVersion) + 1, len(S2.softwareVersion)-charindex('.',S2.softwareVersion)), '.','')
ELSE S2.softwareVersion END))
From tblSoftware S2
Inner Join tblSoftwareUni SU2 On SU2.SoftID = S2.SoftID
Where S2.SoftwareVersion is not NULL and S2.SoftwareVersion <> '' AND SU2.SoftID = SU1.SoftID
) As MaxVersion
,
CASE WHEN ((Select max(CONVERT(DECIMAL(18,12),
CASE WHEN (charindex('.',S2.softwareVersion) > 0) THEN Substring(S2.softwareVersion,0,charindex('.',S2.softwareVersion)) + '.' + REPLACE(Substring(S2.softwareVersion, charindex('.',S2.softwareVersion) + 1, len(S2.softwareVersion)-charindex('.',S2.softwareVersion)), '.','')
ELSE S2.softwareVersion END))
From tblSoftware S2
Inner Join tblSoftwareUni SU2 On SU2.SoftID = S2.SoftID
Where S2.SoftwareVersion is not NULL and S2.SoftwareVersion <> '' AND SU2.SoftID = SU1.SoftID
) <> CONVERT(DECIMAL(18,12),
CASE WHEN (charindex('.',S1.softwareVersion) > 0) THEN Substring(S1.softwareVersion,0,charindex('.',S1.softwareVersion)) + '.' + REPLACE(Substring(S1.softwareVersion, charindex('.',S1.softwareVersion) + 1, len(S1.softwareVersion)-charindex('.',S1.softwareVersion)), '.','')
ELSE S1.softwareVersion END)) THEN 'FALSE'
ELSE 'TRUE'
END AS 'OnLastVersion'
From tblSoftware S1
Inner Join tblSoftwareUni SU1 On SU1.SoftID = S1.SoftID
Inner Join tblAssets on tblAssets.AssetID = S1.AssetID
WHERE S1.SoftwareVersion is not NULL and S1.SoftwareVersion <> ''
Order By OnLastVersion ASC, tblAssets.AssetName
08-03-2022 02:31 PM
There is a few issues here that make this harder than it should be.
An example of something similar you can find here: https://www.lansweeper.com/report/not-latest-build-of-windows-10-audit/
This is obviously for Windows 10 builds, but it retrieves the highest build scanned by lansweeper and then shows assets that are lower than that.
08-02-2022 03:34 PM
Hi @IainCaldwell ,
Thanks for your reply. I am using MaxVersion.SoftwareVersion from Lansweeper. I have faced so many obstacles trying to get a proper comparison between the latest version we have of a software and the current installed version.
MaxVersion should give you the latest version of the software installed any asset in your company, but there is an obstacle on that as well. anyway, The below is the report I am working on. It should give you the outdated 'Chrome' versions , but unfortunately it's not accurate.
===============================
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version,
MaxVersion.SoftwareVersion As latest,
Case
When Cast(ParseName(tblSoftware.softwareVersion, 4) As int) < Cast(ParseName(MaxVersion.SoftwareVersion, 4) As int) Then 'Out of Date'
When Cast(ParseName(tblSoftware.softwareVersion, 4) As int) = Cast(ParseName(MaxVersion.SoftwareVersion, 4) As int)
And
Cast(ParseName(tblSoftware.softwareVersion, 2) As int) < Cast(ParseName(MaxVersion.SoftwareVersion, 2) As int) Then 'Out of Date'
When Cast(ParseName(tblSoftware.softwareVersion, 4) As int) = Cast(ParseName(MaxVersion.SoftwareVersion, 4) As int)
And
Cast(ParseName(tblSoftware.softwareVersion, 2) As int) = Cast(ParseName(MaxVersion.SoftwareVersion, 2) As int)
And
Cast(ParseName(tblSoftware.softwareVersion, 1) As int) < Cast(ParseName(MaxVersion.SoftwareVersion, 1) As int) Then 'Out of Date'
Else 'Up to Date'
End As [Patch Status],
tsysOS.Image As icon
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain,
(Select Top 1 tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.SoftID
Where tblSoftwareUni.SoftID = 21
Order By tblSoftware.SoftwareVersion Desc) As MaxVersion
Where tblSoftwareUni.softID = 21
Order By version,
tblAssets.AssetName
================================
Once you run the report, you will see why it's not accurate.
Thanks for your help in advance.
08-03-2022 11:41 AM
@MiDO7RB I think i'm understanding a bit better now. Unfortunately my SQL skills are a bit rusty (20 years since I was in the Oracle space....and oracle 8.17 didn't bother with ansii std SQL).
That said I think based on your query you are looking for the highest version number on your estate which effectively goes into MaxVersion to then compare all the rest of the software against.
Essentially you're trying to run a report that highlights version sprawl and divergence from 'your company's ' latest version.
I'll ask around to see if someone can have a look at your SQL, but this might be one that hopefully the wider community can help with as i assume its just about getting the right joins etc
Cheers Iain
08-02-2022 03:01 PM
@MiDO7RB Been chatting a few people about this the last couple of days and think I need a bit of help. Are you making reference to a MaxVersion currently in lansweeper (I couldn't find....but I'am fairly new) or are you suggesting we should have a field that holds the latest version numbers so that it could be compared.
Thanks for the help
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now