Notification

Icon
Error

Combined Vulnerability Report Q1 2019

Posted: Tuesday, May 28, 2019 1:37:10 PM(UTC)
Esben.D

Esben.D

Member Administration Original PosterPosts: 2,185
4
Like
This report will provide an overview of the vulnerability updates we released in Q1 of 2019. If your asset has software on it which had a vulnerability we reported on in Q1, it will display whether that software is up-to-date (enough to fix the vulenrability) or not.

This report checks for the following vulnerabilities:
  • PuTTY
  • Winrar
  • Google Chrome
  • LibreOffice
Code:
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.PuTTY,
  s2.Winrar,
  s3.Chrome,
  s4.LibreOffice,
  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 < '0.71.0.0' Then 'Outdated'
          Else 'Up to date'
        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 s1 On
    s1.AssetID = tblAssets.AssetID
  Left Join (Select Top 1000000 tblAssets.AssetID,
        Case
          When tblSoftware.softwareVersion < '5.70' Then 'Outdated'
          Else 'Up to date'
        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 s2 On
    s2.AssetID = tblAssets.AssetID
  Left Join (Select Top 1000000 tblAssets.AssetID,
        Case
          When tblSoftware.softwareVersion Like '74%' Then
            'Up to date'
          Else 'Outdated'
        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%'
      Union
      Select Top 1000000 tblAssets.AssetID,
        Case
          When tblMacApplications.Version Like '74%' Then
            'Up to date'
          Else 'Outdated'
        End As Chrome
      From tblAssets
        Inner Join tblMacApplications On tblAssets.AssetID =
          tblMacApplications.AssetID
        Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
          tblMacApplications.softid
        Inner Join tblMacOSInfo On tblMacOSInfo.AssetID = tblAssets.AssetID
      Where tblSoftwareUni.softwareName Like '%Google Chrome%'
      Union
      Select Top 1000000 tblAssets.AssetID,
        Case
          When tblLinuxSoftware.Version Like '74%' Then
            'Up to date'
          Else 'Outdated'
        End As Chrome
      From tblAssets
        Inner Join tblLinuxSoftware On
          tblAssets.AssetID = tblLinuxSoftware.AssetID
        Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
          tblLinuxSoftware.SoftwareUniID
        Inner Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
      Where tblSoftwareUni.softwareName Like '%google-chrome%') As s3 On
    s3.AssetID = tblAssets.AssetID
  Left Join (Select Top 1000000 tblAssets.AssetID,
        tblAssets.AssetName,
        tblAssets.Domain,
        tsysAssetTypes.AssetTypename As AssetType,
        tblAssets.Username,
        tblAssets.Userdomain,
        tsysAssetTypes.AssetTypeIcon10 As icon,
        tblAssets.IPAddress,
        tsysIPLocations.IPLocation,
        tblAssetCustom.Manufacturer,
        tblAssetCustom.Model,
        tsysOS.OSname As OS,
        tblAssets.SP,
        tblAssets.Lastseen,
        tblAssets.Lasttried,
        tblSoftwareUni.softwareName As Software,
        tblSoftware.softwareVersion As Version,
        tblSoftwareUni.SoftwarePublisher As Publisher,
        tblSoftware.Lastchanged,
        Case
          When tblSoftwareUni.softwareName Like '%libreoffice%' And
            tblSoftware.softwareVersion <= '6.1.4%' Then 'Outdated'
          Else 'Up to date'
        End As LibreOffice
      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
        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%' And
        tblState.Statename = 'Active'
      Union
      Select Top 1000000 tblAssets.AssetID,
        tblAssets.AssetName,
        tblAssets.Domain,
        tsysAssetTypes.AssetTypename As AssetType,
        tblAssets.Username,
        tblAssets.Userdomain,
        tsysAssetTypes.AssetTypeIcon10 As icon,
        tblAssets.IPAddress,
        tsysIPLocations.IPLocation,
        tblAssetCustom.Manufacturer,
        tblAssetCustom.Model,
        tblLinuxSystem.OSRelease As OS,
        tblAssets.SP,
        tblAssets.Lastseen,
        tblAssets.Lasttried,
        tblSoftwareUni.softwareName As Software,
        tblLinuxSoftware.Version As Version,
        tblSoftwareUni.SoftwarePublisher As Publisher,
        tblLinuxSoftware.LastChanged,
        Case
          When tblSoftwareUni.softwareName Like '%libreoffice%' And
            tblLinuxSoftware.Version <= '%6.0.7%' Then 'Outdated'
          Else 'Up to date'
        End As Libreoffice
      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
        Inner Join tblLinuxSoftware On
          tblAssets.AssetID = tblLinuxSoftware.AssetID
        Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
          tblLinuxSoftware.SoftwareUniID
        Inner Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
      Where tblSoftwareUni.softwareName Like '%LibreOffice%' And
        tblState.Statename = 'Active'
      Union
      Select Top 1000000 tblAssets.AssetID,
        tblAssets.AssetName,
        tblAssets.Domain,
        tsysAssetTypes.AssetTypename As AssetType,
        tblAssets.Username,
        tblAssets.Userdomain,
        tsysAssetTypes.AssetTypeIcon10 As icon,
        tblAssets.IPAddress,
        tsysIPLocations.IPLocation,
        tblAssetCustom.Manufacturer,
        tblAssetCustom.Model,
        tblMacOSInfo.SystemVersion As OS,
        tblAssets.SP,
        tblAssets.Lastseen,
        tblAssets.Lasttried,
        tblSoftwareUni.softwareName As Software,
        tblMacApplications.Version As Version,
        tblSoftwareUni.SoftwarePublisher As Publisher,
        tblMacApplications.LastChanged,
        Case
          When tblSoftwareUni.softwareName Like '%libreoffice%' And
            tblMacApplications.Version <= '6.1.4%' Then 'Outdated'
          Else 'Up to date'
        End As Libreoffice
      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
        Inner Join tblMacApplications On tblAssets.AssetID =
          tblMacApplications.AssetID
        Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
          tblMacApplications.softid
        Inner Join tblMacOSInfo On tblMacOSInfo.AssetID = tblAssets.AssetID
      Where tblSoftwareUni.softwareName Like '%LibreOffice%' And
        tblState.Statename = 'Active') As s4 On s4.AssetID = tblAssets.AssetID
Where tsysAssetTypes.AssetTypename In ('Windows', 'Linux', 'Mac') And
  tblState.Statename = 'Active'
Order by tblAssets.Domain,
tblAssets.AssetName
miek_g
#1miek_g Member Posts: 19  
posted: 11/1/2019 4:43:21 PM(UTC)
Thanks for the awesome Q1 update!

I have done a spot of updating and adding on, so I though others may benefit.
please let me know if I should, start a new post for these.
updated original software versions
added Firefox 70, VLC 3.08, TeamViewer 14, FortiClient VPN 6.2.1.0831, Java 8.0.231

==========================================================================================

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.PuTTY,
s2.Winrar,
s3.Chrome,
s4.LibreOffice,
s5.Firefox,
s6.VLC,
s7.TeamViewer,
s8.FortiClient,
s9.Java,
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 < '0.73.0.0' Then 'Outdated'
Else 'Up to date'
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 s1 On
s1.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion < '5.71' Then 'Outdated'
Else 'Up to date'
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 s2 On
s2.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion Like '78%' Then 'Up to date'
Else 'Outdated'
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%'
Union
Select Top 1000000 tblAssets.AssetID,
Case
When tblMacApplications.Version Like '78%' Then 'Up to date'
Else 'Outdated'
End As Chrome
From tblAssets
Inner Join tblMacApplications On tblAssets.AssetID =
tblMacApplications.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblMacApplications.softid
Inner Join tblMacOSInfo On tblMacOSInfo.AssetID = tblAssets.AssetID
Where tblSoftwareUni.softwareName Like '%Google Chrome%'
Union
Select Top 1000000 tblAssets.AssetID,
Case
When tblLinuxSoftware.Version Like '78%' Then 'Up to date'
Else 'Outdated'
End As Chrome
From tblAssets
Inner Join tblLinuxSoftware On
tblAssets.AssetID = tblLinuxSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblLinuxSoftware.SoftwareUniID
Inner Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Where tblSoftwareUni.softwareName Like '%google-chrome%') As s3 On
s3.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tblSoftware.Lastchanged,
Case
When tblSoftwareUni.softwareName Like '%libreoffice%' And
tblSoftware.softwareVersion <= '6.2.8%' Then 'Outdated'
Else 'Up to date'
End As LibreOffice
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
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%' And
tblState.Statename = 'Active'
Union
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblLinuxSystem.OSRelease As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.softwareName As Software,
tblLinuxSoftware.Version As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tblLinuxSoftware.LastChanged,
Case
When tblSoftwareUni.softwareName Like '%libreoffice%' And
tblLinuxSoftware.Version <= '%6.2.8%' Then 'Outdated'
Else 'Up to date'
End As Libreoffice
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
Inner Join tblLinuxSoftware On
tblAssets.AssetID = tblLinuxSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblLinuxSoftware.SoftwareUniID
Inner Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Where tblSoftwareUni.softwareName Like '%LibreOffice%' And
tblState.Statename = 'Active'
Union
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblMacOSInfo.SystemVersion As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblSoftwareUni.softwareName As Software,
tblMacApplications.Version As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tblMacApplications.LastChanged,
Case
When tblSoftwareUni.softwareName Like '%libreoffice%' And
tblMacApplications.Version <= '6.2.8%' Then 'Outdated'
Else 'Up to date'
End As Libreoffice
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
Inner Join tblMacApplications On tblAssets.AssetID =
tblMacApplications.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblMacApplications.softid
Inner Join tblMacOSInfo On tblMacOSInfo.AssetID = tblAssets.AssetID
Where tblSoftwareUni.softwareName Like '%LibreOffice%' And
tblState.Statename = 'Active') As s4 On s4.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion Like '69%' Then 'Up to date'
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%'
Union
Select Top 1000000 tblAssets.AssetID,
Case
When tblMacApplications.Version Like '69%' Then 'Up to date'
Else 'Outdated'
End As Firefox
From tblAssets
Inner Join tblMacApplications On tblAssets.AssetID =
tblMacApplications.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblMacApplications.softid
Inner Join tblMacOSInfo On tblMacOSInfo.AssetID = tblAssets.AssetID
Where tblSoftwareUni.softwareName Like '%firefox%'
Union
Select Top 1000000 tblAssets.AssetID,
Case
When tblLinuxSoftware.Version Like '69%' Then 'Up to date'
Else 'Outdated'
End As Firefox
From tblAssets
Inner Join tblLinuxSoftware On
tblAssets.AssetID = tblLinuxSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblLinuxSoftware.SoftwareUniID
Inner Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Where tblSoftwareUni.softwareName Like '%firefox%') As s5 On
s5.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion Like '3.0.8' Then 'Up to date'
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%'
Union
Select Top 1000000 tblAssets.AssetID,
Case
When tblMacApplications.Version Like '3.0.8' Then 'Up to date'
Else 'Outdated'
End As VLC
From tblAssets
Inner Join tblMacApplications On tblAssets.AssetID =
tblMacApplications.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblMacApplications.softid
Inner Join tblMacOSInfo On tblMacOSInfo.AssetID = tblAssets.AssetID
Where tblSoftwareUni.softwareName Like '%VLC%'
Union
Select Top 1000000 tblAssets.AssetID,
Case
When tblLinuxSoftware.Version Like '3.0.8' Then 'Up to date'
Else 'Outdated'
End As VLC
From tblAssets
Inner Join tblLinuxSoftware On
tblAssets.AssetID = tblLinuxSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblLinuxSoftware.SoftwareUniID
Inner Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Where tblSoftwareUni.softwareName Like '%VLC%') As s6 On
s6.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion < '14%' Then 'Outdated'
Else 'Up to date'
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 s7 On
s7.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion < '6.2.1.0831' Then 'Outdated'
Else 'Up to date'
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 s8 On
s8.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion < '8.0.231%' Then 'Outdated'
Else 'Up to date'
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 s9 On
s9.AssetID = tblAssets.AssetID
Where tsysAssetTypes.AssetTypename In ('Windows', 'Linux', 'Mac') And
tblState.Statename = 'Active'
Order By tblAssets.Domain,
tblAssets.AssetName
jmql
#2jmql Member Posts: 4  
posted: 6/5/2020 1:46:25 PM(UTC)
Is there a way to update this report, so that only machines with a vulnerability show? IE: if all columns show safe, or have a blank entry, don't include them in the report?

Thanks.
Leslie Stroobant
#3Leslie Stroobant Member Posts: 16  
posted: 6/5/2020 3:27:42 PM(UTC)
Pity that this report shows all assets, instead of only the active ones.
I won't be updating my sold or broken assets.

Thanks for the report nevertheless though!
BKolodny
#4BKolodny Member Posts: 1  
posted: 7/30/2021 5:29:43 PM(UTC)
Originally Posted by: jmql Go to Quoted Post
Is there a way to update this report, so that only machines with a vulnerability show? IE: if all columns show safe, or have a blank entry, don't include them in the report?

Thanks.


The way to do that is to wrap it in another select statement. Please note that this is working off of the extended report that was shared by miek_g.

Prepend to the query:
SELECT * FROM
(
Append to the end of the query:
) AS A
WHERE (A.Putty is not null and A.PuTTY != 'Up to date') or
(A.Winrar is not null and A.Winrar != 'Up to date') or
(A.Chrome is not null and A.Chrome != 'Up to date') or
(A.LibreOffice is not null and A.LibreOffice != 'Up to date') or
(A.Firefox is not null and A.Firefox != 'Up to date') or
(A.VLC is not null and A.VLC != 'Up to date') or
(A.TeamViewer is not null and A.TeamViewer != 'Up to date') or
(A.FortiClient is not null and A.FortiClient != 'Up to date') or
(A.Java is not null and A.Java != 'Up to date')
miek_g
#5miek_g Member Posts: 19  
posted: 1/24/2022 3:50:44 PM(UTC)
does anyone know a way to make the software version show instead of 'Outdated'
I have changed the 'Up to date' to ':)' as it makes the report more visibly pleasing to board members :)

using McAfee agent as an example

Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion < '5.7.5%' Then 'CVE-2021-31854'
Else ':)'
End As McAfee
I would like

Left Join (Select Top 1000000 tblAssets.AssetID,
Case
When tblSoftware.softwareVersion < '5.7.5%' Then '5.7.4.399'
Else ':)'
End As McAfee

cheers



Hendrik.VE
#6Hendrik.VE Member Posts: 101  
posted: 1/24/2022 5:05:24 PM(UTC)
Code:
 Case
          When tblSoftware.softwareVersion < '5.7.5%' Then
            tblSoftware.softwareVersion
          Else ':)'
        End As 'McAfee'
miek_g
#7miek_g Member Posts: 19  
posted: 5/30/2022 8:09:18 PM(UTC)
Originally Posted by: Hendrik.VE Go to Quoted Post
Code:
 Case
          When tblSoftware.softwareVersion < '5.7.5%' Then
            tblSoftware.softwareVersion
          Else ':)'
        End As 'McAfee'


thanks Hendrik.VE

building on that I am now hoping to expand to include

When tblSoftware.softwareVersion <= '5.6.%' Then 'CVE-2022-22780'
When tblSoftware.softwareVersion < '5.7.5%' Then tblSoftware.softwareVersion
Else ':)'

Active Discussions

Installer CVE-2022-30190
by  moss5000   Go to last post Go to first unread
Last post: 6/14/2022 4:07:13 PM(UTC)
Installer Deploy APC PowerChute agent to computers
by  chenegh  
Go to last post Go to first unread
Last post: 5/23/2022 5:59:34 PM(UTC)
Installer MsiUninstaller All Java with loop - no script needed
by  Florian_Eigsi   Go to last post Go to first unread
Last post: 5/17/2022 7:21:09 AM(UTC)
Installer Script - Reset Local Admin Password
by  cNone  
Go to last post Go to first unread
Last post: 4/28/2022 10:49:40 AM(UTC)
Installer LsAgent for Windows (updated with conditions)
by  initram   Go to last post Go to first unread
Last post: 4/7/2022 7:29:51 PM(UTC)
Installer Deploy a registry key
by  Don  
Go to last post Go to first unread
Last post: 3/23/2022 6:06:39 PM(UTC)
Installer Pearson Exam Player Installer
by  Steven.C   Go to last post Go to first unread
Last post: 3/8/2022 11:31:22 AM(UTC)
Installer HDD vs SSD PowerShell Script Deployment
by  Esben.D  
Go to last post Go to first unread
Last post: 2/25/2022 3:45:40 PM(UTC)