cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Tythesly
Engaged Sweeper III
Is it possible for somebody to make a report that would list computers with old versions of software if newer versions for that same software are found?

Right now I have a bunch of separate reports that tell me what computers have old versions of software. Only issue is that I have to manually check the softwares website to see if anything has been updated yet and if it has then manually edit the report to reflect the new version number. This is kind tedious and I would like to automate it a bit where I don't need to manually update it.

Example would be everybody has version 15.14 of 7-zip but if one person updates it to the latest version 19 then the report would list all the computers with the old version.

Would be awesome if the report shows Asset Name - Software - Old version number - New version number as well as the rest of the usual headers.

Hopefully I have explained it well enough for you all to understand and thanks in advance!
1 ACCEPTED SOLUTION
Andy_Sismey
Champion Sweeper III
Hi Tyler M.

Have a look at this report by RC62N Using MAX for the software version :- https://www.lansweeper.com/forum/yaf_postst19399findunread_Max-Software-Version-and-Deploy-to-Only-Outdated.aspx#post62681

I have amended the report to show just assets where Google is not the latest version found :


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version,
Case
When tblSoftware.softwareVersion < MaxVersion.softwareVersion Then
'Out of Date'
Else 'Up to Date'
End As [Patch Status],
Case
When tblSoftware.softwareVersion < MaxVersion.softwareVersion Then '#ffadad'
Else '#d4f4be'
End As backgroundcolor,
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.softwareName Like 'Google Chrome%' And
tblSoftwareUni.SoftwarePublisher Like 'Google%'
Order By tblSoftware.softwareVersion Desc) As MaxVersion
Where tblSoftwareUni.softwareName Like '%chrome%' And Case
When tblSoftware.softwareVersion < MaxVersion.softwareVersion Then
'Out of Date'
Else 'Up to Date'
End = 'Out Of Date'
Order By version,
tblAssets.AssetName

View solution in original post

3 REPLIES 3
miek_g
Engaged Sweeper III
Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
s1.ReaderDC,
s2.EEA,
s3.FortiClient,
s4.Foxit,
s5.Chrome,
s6.Java,
s7.LibreOffice,
s8.McAfee,
s9.OneDrive,
s10.Firefox,
s11.PuTTY,
s12.Radmin,
s13.Splashtop,
s14.TeamViewer,
s15.Ultraviewer,
s16.VLC,
s17.Winrar,
s18.Zoom,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion < '21.001.2014%' Then 'Outdated'
Else ':)'
End As ReaderDC
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like '%Acrobat Reader DC%') As s1 On
s1.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion < '7.3' Then 'EOL'
Else ':)'
End As EEA
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like '%Eset%') As s2 On
s2.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion < '6.4.2%' Then 'Outdated'
Else ':)'
End As FortiClient
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like '%FortiClient%') As s3 On
s3.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion < '10.1.3.375%' Then 'Outdated'
Else ':)'
End As Foxit
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like '%Foxit%') As s4 On
s4.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion < '89.0.4389%' Then 'Outdated'
Else ':)'
End As Chrome
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like 'Google Chrome') As s5 On
s5.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion < '8.0.28%' Then 'Outdated'
Else ':)'
End As Java
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like '%java%') As s6 On
s6.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftwareUni.softwareName Like '%libreoffice%' And
tblSoftware.softwareVersion <= '7.1.1' Then 'Outdated'
Else ':)'
End As LibreOffice
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like '%libreoffice%') As s7 On
s7.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion < '5.6.4%' Then 'Outdated'
Else ':)'
End As McAfee
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like '%McAfee Agent%') As s8 On
s8.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion < '21.052%' Then 'Outdated'
Else ':)'
End As OneDrive
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like '%OneDrive%') As s9 On
s9.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion Like '87%' Then ':)'
Else 'Outdated'
End As Firefox
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like '%firefox%') As s10 On
s10.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion < '0.74' Then 'Outdated'
Else ':)'
End As PuTTY
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like '%putty%') As s11 On
s11.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion < '3.5%' Then 'Outdated'
Else ':)'
End As Radmin
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like '%Radmin%') As s12 On
s12.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion < '3.4.4%' Then 'Outdated'
Else ':)'
End As Splashtop
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like '%Splashtop Streamer%') As s13 On
s13.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion < '15.16.8' Then 'Outdated'
Else ':)'
End As TeamViewer
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like '%TeamViewer%') As s14 On
s14.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion Like '6.2.0.2020%' Then ':)'
Else 'Outdated'
End As Ultraviewer
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like '%Ultraviewer%') As s15 On
s15.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion Like '3.0.12' Then ':)'
Else 'Outdated'
End As VLC
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like '%VLC%') As s16 On
s16.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion < '6.0.1' Then 'Outdated'
Else ':)'
End As Winrar
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like '%winrar%') As s17 On
s17.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion < '5.5.12494%' Then 'Outdated'
Else ':)'
End As Zoom
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblSoftwareUni.softwareName Like '%Zoom%') As s18 On
s18.AssetID = tblAssets.AssetID
Where tsysAssetTypes.AssetTypename In ('Windows', 'Linux', 'Mac') And
tblState.Statename = 'Active'
Order By tblAssets.Domain,
tblAssets.AssetName
miek_g
Engaged Sweeper III
Hi Tylar,

this report is a good start from Esben.D

https://www.lansweeper.com/forum/yaf_postst17533_Combined-Vulnerability-Report-Q1-2019.aspx#post58790

just tweak it for your use.

I will post a copy of mine shortly, errors in one of the lines 😞
Andy_Sismey
Champion Sweeper III
Hi Tyler M.

Have a look at this report by RC62N Using MAX for the software version :- https://www.lansweeper.com/forum/yaf_postst19399findunread_Max-Software-Version-and-Deploy-to-Only-Outdated.aspx#post62681

I have amended the report to show just assets where Google is not the latest version found :


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version,
Case
When tblSoftware.softwareVersion < MaxVersion.softwareVersion Then
'Out of Date'
Else 'Up to Date'
End As [Patch Status],
Case
When tblSoftware.softwareVersion < MaxVersion.softwareVersion Then '#ffadad'
Else '#d4f4be'
End As backgroundcolor,
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.softwareName Like 'Google Chrome%' And
tblSoftwareUni.SoftwarePublisher Like 'Google%'
Order By tblSoftware.softwareVersion Desc) As MaxVersion
Where tblSoftwareUni.softwareName Like '%chrome%' And Case
When tblSoftware.softwareVersion < MaxVersion.softwareVersion Then
'Out of Date'
Else 'Up to Date'
End = 'Out Of Date'
Order By version,
tblAssets.AssetName