Outdated softwares Report

[MiDO7RB]

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.

[Gilian]

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

[Esben_D]

There is a few issues here that make this harder than it should be.

1. Using Top 1 in SQL will simply take the first record, there is no guarantee that record will be the highest version.
2. even if you use the MAX statement (which should work I think), versions are stored in text fields, so theoretically, some mistakes can occur because SQL will be looking at the values as text characters, not numbers.

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.

[MiDO7RB]

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.

[IainCaldwell]

@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

[IainCaldwell]

@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