Notification

Icon
Error

Firefox 67.0.3 zero-day vulnerability

Posted: Wednesday, June 19, 2019 8:35:33 AM(UTC)
Esben.D

Esben.D

Member Administration Original PosterPosts: 1,768
4
Like
Mozilla just released Firefox version 67.0.3 and Firefox ESR 60.7.1 in response to a zero-day vulnerability (CVE-2019-11707) found by Google's Project Zero.
You can find more information about the vulnerability in the blog post.

The report below will list all machines which are not on the latest Firefox or Firefox ESR version (67.0.3 or 60.7.1)

Code:
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename As AssetType,
  tblAssets.Username,
  tblAssets.Userdomain,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  Case
    When tblSoftware.softwareVersion >= '67.0.3' And
      tblSoftwareUni.softwareName Like '%firefox%' Then 'Up to date'
    When tblSoftware.softwareVersion >= '60.7.1' And
      tblSoftwareUni.softwareName Like '%firefox%ESR%' Then 'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblSoftware.Lastchanged,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
Case
    When tblSoftware.softwareVersion >= '67.0.3' AND tblSoftwareUni.softwareName Like '%firefox%' Then '#d4f4be'
    When tblSoftware.softwareVersion >= '60.7.1' AND tblSoftwareUni.softwareName Like '%firefox%ESR%' Then '#d4f4be'
    Else '#ffadad'
End As backgroundcolor
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 '%firefox%' 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,
  Case
    When tblMacApplications.Version >= '67.0.3' And
      tblSoftwareUni.softwareName Like '%firefox%' Then 'Up to date'
    When tblMacApplications.Version >= '60.7.1' And
      tblSoftwareUni.softwareName Like '%firefox%ESR%' Then 'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tblSoftwareUni.softwareName As Software,
  tblMacApplications.Version As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblMacOSInfo.SystemVersion As OS,
  tblAssets.SP,
  tblMacApplications.LastChanged,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Case
    When tblMacApplications.Version >= '67.0.3' AND tblSoftwareUni.softwareName Like '%firefox%' Then '#d4f4be'
    When tblMacApplications.Version >= '60.7.1%' AND tblSoftwareUni.softwareName Like '%firefox%ESR%' Then '#d4f4be'
    Else '#ffadad'
End As backgroundcolor
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 '%firefox%' 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,
  Case
    When tblLinuxSoftware.Version >= '67.0.3' And tblSoftwareUni.softwareName
      Like '%firefox%' Then 'Up to date'
    When tblLinuxSoftware.Version >= '60.7.1' And
      tblSoftwareUni.softwareName Like '%firefox%ESR%' Then 'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tblSoftwareUni.softwareName As Software,
  tblLinuxSoftware.Version As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblLinuxSystem.OSRelease As OS,
  tblAssets.SP,
  tblLinuxSoftware.LastChanged,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
    Case
    When tblLinuxSoftware.Version >= '67.0.3%' AND tblSoftwareUni.softwareName Like '%firefox%' Then '#d4f4be'
    When tblLinuxSoftware.Version >= '60.7.1%' AND tblSoftwareUni.softwareName Like '%firefox%ESR%' Then '#d4f4be'
    Else '#ffadad'
End As backgroundcolor
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 '%firefox%' And tblSoftwareUni.softwareName Not Like '%locale%') And tblState.Statename =
  'Active'
Order By Domain,
  AssetName,
  Software
B Claeys
#1B Claeys Member Posts: 2  
posted: 6/19/2019 2:19:05 PM(UTC)
Thanks for the report!

Just one question: How can I change the report so it only shows the 'Out of date' assets?

stanislav.tsenkov
#2stanislav.tsenkov Member Posts: 2  
posted: 6/19/2019 3:09:01 PM(UTC)
This one will show only Out of date.



Originally Posted by: B Claeys Go to Quoted Post
Thanks for the report!

Just one question: How can I change the report so it only shows the 'Out of date' assets?



Quote:
Select Top 1000000 *
From (Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
Case
When tblSoftware.softwareVersion Like '67.0.3' And
tblSoftwareUni.softwareName Like '%firefox%' Then 'Up to date'
When tblSoftware.softwareVersion Like '60.7.1' And
tblSoftwareUni.softwareName Like '%firefox%ESR%' Then 'Up to date'
Else 'Out of date'
End As Patch_Status,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblSoftware.Lastchanged,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When tblSoftware.softwareVersion Like '67.0.3' And
tblSoftwareUni.softwareName Like '%firefox%' Then '#d4f4be'
When tblSoftware.softwareVersion Like '60.7.1' And
tblSoftwareUni.softwareName Like '%firefox%ESR%' Then '#d4f4be'
Else '#ffadad'
End As backgroundcolor
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 '%firefox%' 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,
Case
When tblMacApplications.Version Like '67.0.3' And
tblSoftwareUni.softwareName Like '%firefox%' Then 'Up to date'
When tblMacApplications.Version Like '60.7.1' And
tblSoftwareUni.softwareName Like '%firefox%ESR%' Then 'Up to date'
Else 'Out of date'
End As Patch_Status,
tblSoftwareUni.softwareName As Software,
tblMacApplications.Version As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblMacOSInfo.SystemVersion As OS,
tblAssets.SP,
tblMacApplications.LastChanged,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When tblMacApplications.Version Like '67.0.3' And
tblSoftwareUni.softwareName Like '%firefox%' Then '#d4f4be'
When tblMacApplications.Version Like '60.7.1%' And
tblSoftwareUni.softwareName Like '%firefox%ESR%' Then '#d4f4be'
Else '#ffadad'
End As backgroundcolor
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 '%firefox%' 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,
Case
When tblLinuxSoftware.Version Like '67.0.3' And
tblSoftwareUni.softwareName Like '%firefox%' Then 'Up to date'
When tblLinuxSoftware.Version Like '60.7.1' And
tblSoftwareUni.softwareName Like '%firefox%ESR%' Then 'Up to date'
Else 'Out of date'
End As Patch_Status,
tblSoftwareUni.softwareName As Software,
tblLinuxSoftware.Version As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblLinuxSystem.OSRelease As OS,
tblAssets.SP,
tblLinuxSoftware.LastChanged,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When tblLinuxSoftware.Version Like '67.0.3%' And
tblSoftwareUni.softwareName Like '%firefox%' Then '#d4f4be'
When tblLinuxSoftware.Version Like '60.7.1%' And
tblSoftwareUni.softwareName Like '%firefox%ESR%' Then '#d4f4be'
Else '#ffadad'
End As backgroundcolor
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 '%firefox%' And
tblSoftwareUni.softwareName Not Like '%locale%' And tblState.Statename =
'Active'
Order By Domain,
AssetName,
Software) a
Where a.Patch_Status = 'Out of date'
Order By a.Domain,
a.AssetName,
a.Software
B Claeys
#3B Claeys Member Posts: 2  
posted: 6/19/2019 3:13:30 PM(UTC)
Originally Posted by: stanislav.tsenkov Go to Quoted Post
This one will show only Out of date.



Originally Posted by: B Claeys Go to Quoted Post
Thanks for the report!

Just one question: How can I change the report so it only shows the 'Out of date' assets?



Quote:
Select Top 1000000 *
From (Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
Case
When tblSoftware.softwareVersion Like '67.0.3' And
tblSoftwareUni.softwareName Like '%firefox%' Then 'Up to date'
When tblSoftware.softwareVersion Like '60.7.1' And
tblSoftwareUni.softwareName Like '%firefox%ESR%' Then 'Up to date'
Else 'Out of date'
End As Patch_Status,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblSoftware.Lastchanged,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When tblSoftware.softwareVersion Like '67.0.3' And
tblSoftwareUni.softwareName Like '%firefox%' Then '#d4f4be'
When tblSoftware.softwareVersion Like '60.7.1' And
tblSoftwareUni.softwareName Like '%firefox%ESR%' Then '#d4f4be'
Else '#ffadad'
End As backgroundcolor
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 '%firefox%' 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,
Case
When tblMacApplications.Version Like '67.0.3' And
tblSoftwareUni.softwareName Like '%firefox%' Then 'Up to date'
When tblMacApplications.Version Like '60.7.1' And
tblSoftwareUni.softwareName Like '%firefox%ESR%' Then 'Up to date'
Else 'Out of date'
End As Patch_Status,
tblSoftwareUni.softwareName As Software,
tblMacApplications.Version As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblMacOSInfo.SystemVersion As OS,
tblAssets.SP,
tblMacApplications.LastChanged,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When tblMacApplications.Version Like '67.0.3' And
tblSoftwareUni.softwareName Like '%firefox%' Then '#d4f4be'
When tblMacApplications.Version Like '60.7.1%' And
tblSoftwareUni.softwareName Like '%firefox%ESR%' Then '#d4f4be'
Else '#ffadad'
End As backgroundcolor
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 '%firefox%' 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,
Case
When tblLinuxSoftware.Version Like '67.0.3' And
tblSoftwareUni.softwareName Like '%firefox%' Then 'Up to date'
When tblLinuxSoftware.Version Like '60.7.1' And
tblSoftwareUni.softwareName Like '%firefox%ESR%' Then 'Up to date'
Else 'Out of date'
End As Patch_Status,
tblSoftwareUni.softwareName As Software,
tblLinuxSoftware.Version As Version,
tblSoftwareUni.SoftwarePublisher As Publisher,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblLinuxSystem.OSRelease As OS,
tblAssets.SP,
tblLinuxSoftware.LastChanged,
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When tblLinuxSoftware.Version Like '67.0.3%' And
tblSoftwareUni.softwareName Like '%firefox%' Then '#d4f4be'
When tblLinuxSoftware.Version Like '60.7.1%' And
tblSoftwareUni.softwareName Like '%firefox%ESR%' Then '#d4f4be'
Else '#ffadad'
End As backgroundcolor
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 '%firefox%' And
tblSoftwareUni.softwareName Not Like '%locale%' And tblState.Statename =
'Active'
Order By Domain,
AssetName,
Software) a
Where a.Patch_Status = 'Out of date'
Order By a.Domain,
a.AssetName,
a.Software


Thanks, it works
ojohansen
#4ojohansen Member Posts: 2  
posted: 6/21/2019 9:27:45 AM(UTC)
Mozilla has already released Firefox version 67.0.4. WOuld it be possible to adjust the report to greenlight versions above 67.0.3?
jwood.mls
#5jwood.mls Member Posts: 40  
posted: 6/24/2019 10:26:56 PM(UTC)
Originally Posted by: ojohansen Go to Quoted Post
Mozilla has already released Firefox version 67.0.4. WOuld it be possible to adjust the report to greenlight versions above 67.0.3?


Came here to say the same thing. Seems like the report is limiting by saying only one versions is considered up-to-date in relation to this vulnerability, when presumably anything newer would also have the issue fixed.

Esben.D
#6Esben.D Member Administration Original PosterPosts: 1,768  
posted: 6/25/2019 8:26:17 AM(UTC)
Originally Posted by: jwood.mls Go to Quoted Post
Originally Posted by: ojohansen Go to Quoted Post
Mozilla has already released Firefox version 67.0.4. WOuld it be possible to adjust the report to greenlight versions above 67.0.3?


Came here to say the same thing. Seems like the report is limiting by saying only one versions is considered up-to-date in relation to this vulnerability, when presumably anything newer would also have the issue fixed.



It checks for the specific version mentioned in the query. If you want to update it all you need to do is replace the version numbers to the most recent ones.

You can also compare version numbers, which should work in this case:
Code:
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename As AssetType,
  tblAssets.Username,
  tblAssets.Userdomain,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  Case
    When tblSoftware.softwareVersion >= '67.0.3' And
      tblSoftwareUni.softwareName Like '%firefox%' Then 'Up to date'
    When tblSoftware.softwareVersion >= '60.7.1' And
      tblSoftwareUni.softwareName Like '%firefox%ESR%' Then 'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblSoftware.Lastchanged,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
Case
    When tblSoftware.softwareVersion >= '67.0.3' AND tblSoftwareUni.softwareName Like '%firefox%' Then '#d4f4be'
    When tblSoftware.softwareVersion >= '60.7.1' AND tblSoftwareUni.softwareName Like '%firefox%ESR%' Then '#d4f4be'
    Else '#ffadad'
End As backgroundcolor
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 '%firefox%' 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,
  Case
    When tblMacApplications.Version >= '67.0.3' And
      tblSoftwareUni.softwareName Like '%firefox%' Then 'Up to date'
    When tblMacApplications.Version >= '60.7.1' And
      tblSoftwareUni.softwareName Like '%firefox%ESR%' Then 'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tblSoftwareUni.softwareName As Software,
  tblMacApplications.Version As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblMacOSInfo.SystemVersion As OS,
  tblAssets.SP,
  tblMacApplications.LastChanged,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Case
    When tblMacApplications.Version >= '67.0.3' AND tblSoftwareUni.softwareName Like '%firefox%' Then '#d4f4be'
    When tblMacApplications.Version >= '60.7.1%' AND tblSoftwareUni.softwareName Like '%firefox%ESR%' Then '#d4f4be'
    Else '#ffadad'
End As backgroundcolor
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 '%firefox%' 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,
  Case
    When tblLinuxSoftware.Version >= '67.0.3' And tblSoftwareUni.softwareName
      Like '%firefox%' Then 'Up to date'
    When tblLinuxSoftware.Version >= '60.7.1' And
      tblSoftwareUni.softwareName Like '%firefox%ESR%' Then 'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tblSoftwareUni.softwareName As Software,
  tblLinuxSoftware.Version As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblLinuxSystem.OSRelease As OS,
  tblAssets.SP,
  tblLinuxSoftware.LastChanged,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
    Case
    When tblLinuxSoftware.Version >= '67.0.3%' AND tblSoftwareUni.softwareName Like '%firefox%' Then '#d4f4be'
    When tblLinuxSoftware.Version >= '60.7.1%' AND tblSoftwareUni.softwareName Like '%firefox%ESR%' Then '#d4f4be'
    Else '#ffadad'
End As backgroundcolor
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 '%firefox%' And tblSoftwareUni.softwareName Not Like '%locale%') And tblState.Statename =
  'Active'
Order By Domain,
  AssetName,
  Software
jwood.mls
#7jwood.mls Member Posts: 40  
posted: 6/25/2019 2:29:47 PM(UTC)
Could also change "like" to >=, correct? At least for this vulnerability.
Esben.D
#8Esben.D Member Administration Original PosterPosts: 1,768  
posted: 6/25/2019 3:34:44 PM(UTC)
Originally Posted by: jwood.mls Go to Quoted Post
Could also change "like" to >=, correct? At least for this vulnerability.


That's what I did. I also updated the original report with this change. Generally, I am hesitant to do that since comparing strings in SQL isn't really reliable. But in this case it should work fine.

Active Discussions

Lansweeper how to add windows and office licenses manually.
by  cesar.ti   Go to last post Go to first unread
Last post: 8/23/2019 7:32:10 PM(UTC)
Lansweeper User roles and permissions
by  RKCar  
Go to last post Go to first unread
Last post: 8/23/2019 6:23:38 PM(UTC)
Lansweeper Deployment condition
by  RKCar   Go to last post Go to first unread
Last post: 8/23/2019 4:07:30 PM(UTC)
Lansweeper HP Printer not showing correctly
by  JacobH  
Go to last post Go to first unread
Last post: 8/23/2019 1:32:54 PM(UTC)
Lansweeper Moving Assets in Location not Saving
by  brodiemac   Go to last post Go to first unread
Last post: 8/23/2019 12:23:04 PM(UTC)
Lansweeper Scanning includes IOS devices even though I exclude them??
by  steveb  
Go to last post Go to first unread
Last post: 8/22/2019 11:05:33 PM(UTC)
Lansweeper lsagent not scanning/sending when using relay
by  Esben.D   Go to last post Go to first unread
Last post: 8/19/2019 12:31:02 PM(UTC)
Lansweeper Check if Netbios is disabled over TCP/IP
by  TaherMD  
Go to last post Go to first unread
Last post: 8/19/2019 6:00:51 AM(UTC)