Report Request - List assets with old software when newer versions are found

[Tythesly]

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!

[miek_g]

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]

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 · Accepted solution]

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