Notification

Icon
Error

Google Chrome Zero-day vulnerability

Posted: Thursday, March 7, 2019 11:40:29 AM(UTC)
Esben.D

Esben.D

Member Administration Original PosterPosts: 1,768
3
Like
A new Google Chrome vulnerability has been discovered. Take a look at the details on our blog post.

You can find the deployment package for Chrome here.

The report below gives an overview of all active Windows, Linux and Mac assets that have Google Chrome installed. It's color coded so you can easily identified assets that do not have Google Chrome version 72.0.3626.121.

To run this report in Lansweeper, you can follow the instructions here.

Code:
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,
    Case
    When tblSoftware.softwareVersion = '72.0.3626.121' Then 'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblSoftware.Lastchanged,
  Case
    When tblSoftware.softwareVersion <> '72.0.3626.121' Then '#ffadad'
    Else '#d4f4be'
  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 '%Google Chrome%' 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,
    Case
    When tblMacApplications.Version = '72.0.3626.121' Then 'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName As Software,
  tblMacApplications.Version As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblMacApplications.LastChanged,
  Case
    When tblMacApplications.Version <> '72.0.3626.121' Then '#ffadad'
    Else '#d4f4be'
  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 '%Google Chrome%' 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,
      Case
    When tblLinuxSoftware.Version = '72.0.3626.121-1' Then 'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName As Software,
  tblLinuxSoftware.Version As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblLinuxSoftware.LastChanged,
  Case
    When tblLinuxSoftware.Version <> '72.0.3626.121-1' Then '#ffadad'
    Else '#d4f4be'
  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 '%google-chrome%' And tblState.Statename =
  'Active'
Order By Domain,
  AssetName,
  Software
Thorre
#1Thorre Member Posts: 2  
posted: 3/7/2019 4:13:16 PM(UTC)
Hi Esben,

thanks for the nice and helpful report.
I already let it run but something is weird. Version 72.03626.81 which is < then 72.03626.121 is colored in green and I expected red.
An idea why?

Cheers
Thorsten
doone128
#2doone128 Member Posts: 18  
posted: 3/7/2019 4:25:57 PM(UTC)
Thanks. This is great.

However, could you please add - as per the Windows Updates reports - an 'up to date' and 'out of date' column so that we can easily filter the results?

Many thanks!
Leslie Stroobant
#3Leslie Stroobant Member Posts: 14  
posted: 3/7/2019 4:31:10 PM(UTC)
Originally Posted by: Thorre Go to Quoted Post
Hi Esben,

thanks for the nice and helpful report.
I already let it run but something is weird. Version 72.03626.81 which is < then 72.03626.121 is colored in green and I expected red.
An idea why?

Cheers
Thorsten


This is just a guess, but perhaps this is more an alphabetical sorting than a numerical one. Really not sure though.
robisonr
#4robisonr Member Posts: 2  
posted: 3/7/2019 4:33:15 PM(UTC)
Originally Posted by: Thorre Go to Quoted Post
Hi Esben,

thanks for the nice and helpful report.
I already let it run but something is weird. Version 72.03626.81 which is < then 72.03626.121 is colored in green and I expected red.
An idea why?

Cheers
Thorsten


There is something weird with the way > and < works with Chrome version numbers. I've had success with switching to casting the version numbers and then comparing

Replace:
When tblSoftware.softwareVersion < '72.0.3626.121'

With:
When Cast('/' + Replace(tblSoftware.softwareVersion, '.', '/') + '/' As hierarchyid) < Cast('/72/0/3626/121/' As hierarchyid)
Esben.D
#5Esben.D Member Administration Original PosterPosts: 1,768  
posted: 3/7/2019 4:35:29 PM(UTC)
Originally Posted by: Thorre Go to Quoted Post
Hi Esben,

thanks for the nice and helpful report.
I already let it run but something is weird. Version 72.03626.81 which is < then 72.03626.121 is colored in green and I expected red.
An idea why?

Cheers
Thorsten


Yeah, its the unfortunate string comparison in SQL Brick wall I'll change it.

The new version will only display Chrome versions with 72.03626.121 as green. But If a newer version of Chrome is released, you'll have to update the report with the new, latest version number.

Originally Posted by: doone128 Go to Quoted Post
Thanks. This is great.

However, could you please add - as per the Windows Updates reports - an 'up to date' and 'out of date' column so that we can easily filter the results?

Many thanks!

Done Angel
Jay818
#6Jay818 Member Posts: 1  
posted: 3/7/2019 4:51:27 PM(UTC)
SQL is using string comparison. You can use CAST to explicitly compare the version numerically. Here is my revision of the query:

Code:
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 Cast(Replace(tblSoftware.softwareVersion, '.', '') As BIGINT) <
      7203626121 Then '#ffadad'
    Else '#d4f4be'
  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 '%Google Chrome%' 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 Cast(Replace(tblMacApplications.Version, '.', '') As BIGINT) <
      7203626121 Then '#ffadad'
    Else '#d4f4be'
  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 '%Google Chrome%' 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 Cast(Replace(tblLinuxSoftware.Version, '.', '') As BIGINT) < 7203626121
    Then '#ffadad'
    Else '#d4f4be'
  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 '%google-chrome%' And
  tblState.Statename = 'Active'
Order By Domain,
  AssetName,
  Software
robisonr
#7robisonr Member Posts: 2  
posted: 3/7/2019 5:00:26 PM(UTC)
Originally Posted by: Jay818 Go to Quoted Post
SQL is using string comparison. You can use CAST to explicitly compare the version numerically. Here is my revision of the query


This works for this instance but if you're comparing version numbers like 72.0.3626.121 and 72.0.3627.90 if will fail due to the second number being less digits overall even though it's a newer version.

We solved this with casting and heirarchyID:

Code:
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,
  Case
    When Cast('/' + Replace(tblSoftware.softwareVersion, '.', '/') +
      '/' As hierarchyid) < Cast('/72/0/3626/121/' As hierarchyid) Then
      'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblSoftware.Lastchanged,
  Case
    When Cast('/' + Replace(tblSoftware.softwareVersion, '.', '/') +
      '/' As hierarchyid) < Cast('/72/0/3626/121/' As hierarchyid) Then
      '#ffadad'
    Else '#d4f4be'
  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 '%Google Chrome%' 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,
  Case
    When Cast('/' + Replace(tblMacApplications.Version, '.', '/') +
      '/' As hierarchyid) < Cast('/72/0/3626/121/' As hierarchyid) Then
      'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName As Software,
  tblMacApplications.Version As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblMacApplications.LastChanged,
  Case
    When Cast('/' + Replace(tblMacApplications.Version, '.', '/') +
      '/' As hierarchyid) < Cast('/72/0/3626/121/' As hierarchyid) Then
      '#ffadad'
    Else '#d4f4be'
  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 '%Google Chrome%' 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,
  Case
    When Cast('/' + Replace(tblLinuxSoftware.Version, '.', '/') +
      '/' As hierarchyid) < Cast('/72/0/3626/121/' As hierarchyid) Then
      'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName As Software,
  tblLinuxSoftware.Version As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblLinuxSoftware.LastChanged,
  Case
    When Cast('/' + Replace(tblLinuxSoftware.Version, '.', '/') +
      '/' As hierarchyid) < Cast('/72/0/3626/121/' As hierarchyid) Then
      '#ffadad'
    Else '#d4f4be'
  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 '%google-chrome%' And
  tblState.Statename = 'Active'
Order By Domain,
  AssetName,
  Software
Esben.D
#8Esben.D Member Administration Original PosterPosts: 1,768  
posted: 3/7/2019 5:50:37 PM(UTC)
Originally Posted by: robisonr Go to Quoted Post


This works for this instance but if you're comparing version numbers like 72.0.3626.121 and 72.0.3627.90 if will fail due to the second number being less digits overall even though it's a newer version.

We solved this with casting and heirarchyID


I'm not familiar with heirarchyid, but reading up on it a bit it seems that it can indeed be a solution. Unfortunately, it doesn't work with an SQL Compact database. For SQL server users, this will indeed be a more future proof report.

FYI I did get an error with your version in SSMS:

Quote:
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "hierarchyid":
Microsoft.SqlServer.Types.HierarchyIdException: 24001: SqlHierarchyId.Parse failed because the input string '/72/0/3626/121-1/' is not a valid string representation of a SqlHierarchyId node.
Microsoft.SqlServer.Types.HierarchyIdException:
at Microsoft.SqlServer.Types.OrdPath.InitFromString(String chDottedString)
at Microsoft.SqlServer.Types.SqlHierarchyId.Parse(SqlString input)
.
joe_user
#9joe_user Member Posts: 11  
posted: 3/7/2019 6:29:08 PM(UTC)
Excited to see mention of an approach that might let us address software version issues.

The heirarchyid version of the report returns a red-on-red warning below the graphic portion of the Lanasweeper report editor but above the tabular section; the text of this warning was almost entirely obscured by the tabular section of the screen, but I was able to triple-click into the space to select the text to copy/paste it below.

Our Lansweeper data is stored in Microsoft SQL Server 2008 R2 (64-bit) and Microsoft .NET Framework 4.5.2. Could our issue could have to do with SQL / .NET version.?

From what I read here, our SQL version supports SQLHierarchyId.

Maybe we need to install a feature pack for SQL server? I've read "The SQL Server System CLR Types package contains the components implementing the new geometry, geography, and hierarchyid types in SQL Server 2008."

Maybe .NET needs to be at 4.6 or higher?

Here's the warning message:
A .NET Framework error occurred during execution of user-defined routine or aggregate "hierarchyid": Microsoft.SqlServer.Types.HierarchyIdException: 24001: SqlHierarchyId.Parse failed because the input string '/69/0/3497/92-1/' is not a valid string representation of a SqlHierarchyId node. Microsoft.SqlServer.Types.HierarchyIdException: at Microsoft.SqlServer.Types.ex_raise(Int32 major, Int32 minor, Int32 sev, Int32 state, Object param1, Object param2, Object param3) at Microsoft.SqlServer.Types.OrdPath.InitFromString(String chDottedString) at Microsoft.SqlServer.Types.SqlHierarchyId.Parse(SqlString input) .
AZHockeyNut
#10AZHockeyNut Member Alpha Tester Posts: 231  
posted: 3/7/2019 6:34:28 PM(UTC)
Originally Posted by: joe_user Go to Quoted Post
Excited to see mention of an approach that might let us address software version issues.

The heirarchyid version of the report returns a red-on-red warning below the graphic portion of the Lanasweeper report editor but above the tabular section; the text of this warning was almost entirely obscured by the tabular section of the screen, but I was able to triple-click into the space to select the text to copy/paste it below.

Our Lansweeper data is stored in Microsoft SQL Server 2008 R2 (64-bit) and Microsoft .NET Framework 4.5.2. Could our issue could have to do with SQL / .NET version.?

From what I read here, our SQL version supports SQLHierarchyId.

Maybe we need to install a feature pack for SQL server? I've read "The SQL Server System CLR Types package contains the components implementing the new geometry, geography, and hierarchyid types in SQL Server 2008."

Maybe .NET needs to be at 4.6 or higher?

Here's the warning message:
A .NET Framework error occurred during execution of user-defined routine or aggregate "hierarchyid": Microsoft.SqlServer.Types.HierarchyIdException: 24001: SqlHierarchyId.Parse failed because the input string '/69/0/3497/92-1/' is not a valid string representation of a SqlHierarchyId node. Microsoft.SqlServer.Types.HierarchyIdException: at Microsoft.SqlServer.Types.ex_raise(Int32 major, Int32 minor, Int32 sev, Int32 state, Object param1, Object param2, Object param3) at Microsoft.SqlServer.Types.OrdPath.InitFromString(String chDottedString) at Microsoft.SqlServer.Types.SqlHierarchyId.Parse(SqlString input) .


@robinsonr Nice work! Worked perfect for me on SQL server thanks!!
marck1024
#11marck1024 Member Posts: 31  
posted: 3/8/2019 1:37:13 AM(UTC)
Originally Posted by: Esben.D Go to Quoted Post
...heirarchyid...doesn't work with an SQL Compact database...

Do yourself a favor and consider moving to SQL Server Express. I got a nice performance boost when I did that. Express is running on the same server as Lansweeper. So, no additional hardware in the mix.

Lasse Dollerup
#12Lasse Dollerup Member Posts: 2  
posted: 3/8/2019 9:43:50 AM(UTC)
Hi
I'm getting this error when i try the HiearchyID version:

Error: A .NET Framework error occurred during execution of user-defined routine or aggregate "hierarchyid": Microsoft.SqlServer.Types.HierarchyIdException: 24001: SqlHierarchyId.Parse failed because the input string '//' is not a valid string representation of a SqlHierarchyId node. Microsoft.SqlServer.Types.HierarchyIdException: at Microsoft.SqlServer.Types.SqlHierarchyId.Parse(SqlString input) .

I suspect, that i have a version with (..), any suggestions to get aroud this?
Lasse Dollerup
#13Lasse Dollerup Member Posts: 2  
posted: 3/8/2019 10:48:15 AM(UTC)
Managed to fix my problem, by implementing af replace that turned // into /
Esben.D
#14Esben.D Member Administration Original PosterPosts: 1,768  
posted: 3/8/2019 11:07:38 AM(UTC)
Originally Posted by: marck1024 Go to Quoted Post
Originally Posted by: Esben.D Go to Quoted Post
...heirarchyid...doesn't work with an SQL Compact database...

Do yourself a favor and consider moving to SQL Server Express. I got a nice performance boost when I did that. Express is running on the same server as Lansweeper. So, no additional hardware in the mix.



Believe me I am, and I would also recommend it to anyone. But I know many people (especially with smaller installations) use SQL Compact. So I can't publish reports that only work on SQL Server if there is a possibility to make it work on SQL Compact.
joe_user
#15joe_user Member Posts: 11  
posted: 3/8/2019 1:05:18 PM(UTC)
Originally Posted by: Lasse Dollerup Go to Quoted Post
Managed to fix my problem, by implementing af replace that turned // into /


Lasse Dollerup, could you please explain what is meant by your statement below?

"implementing af replace that turned // into /"

Seems like we encountered the same issue, so I'm hoping your fix works for me.

Thanks!
marck1024
#16marck1024 Member Posts: 31  
posted: 3/8/2019 3:40:54 PM(UTC)
Originally Posted by: Esben.D Go to Quoted Post
Believe me I am, and I would also recommend it to anyone. But I know many people (especially with smaller installations) use SQL Compact. So I can't publish reports that only work on SQL Server if there is a possibility to make it work on SQL Compact.

I've had applications that bundled SQL Server Express (Backup Exec & Veeam are two examples that come to mind). Is there a reason why Lansweeper couldn't do that instead of bundling SQL Compact Edition? The smaller maximum database size, the poorer performance, and the reduced programmability surface all seem like good reasons to jump ship from CE.

df1823
#17df1823 Member Posts: 7  
posted: 3/13/2019 5:16:18 PM(UTC)
I just noticed there is an even newer version of Chrome that came out yesterday, 73.0.3683.75 i believe, so when i ran this report in my environment it flagged up the machines with that version as out of date.

Is there any way i can update the report so it will class anything running 72.0.3626.121 or higher as safe?
marck1024
#18marck1024 Member Posts: 31  
posted: 3/13/2019 5:29:53 PM(UTC)
If you're got Lansweeper using an SQL edition other than the bundled Compact Edition, you can use robisonr's version.

Note that I found it was labelling backwards. I updated that portion of the code to:

Code:
Case
When Cast('/' + Replace(tblSoftware.softwareVersion, '.', '/') +
'/' As hierarchyid) < Cast('/72/0/3626/121/' As hierarchyid) Then
'Out of date'
Else 'Up to date'
df1823
#19df1823 Member Posts: 7  
posted: 3/14/2019 9:52:56 AM(UTC)
Sorry i should have read the other comments in here. We have the database on a sql server 2008 r2 box but yet i get the error similar to others when i save and run the that.

.NET Framework error occurred during execution of user-defined routine or aggregate "hierarchyid":
Microsoft.SqlServer.Types.HierarchyIdException: 24001: SqlHierarchyId.Parse failed because the input string '//' is not a valid string representation of a SqlHierarchyId node. Microsoft.SqlServer.Types.HierarchyIdException: at Microsoft.SqlServer.Types.SqlHierarchyId.Parse(SqlString input).
mikeNDF
#20mikeNDF Member Posts: 8  
posted: 3/15/2019 3:29:28 PM(UTC)
Originally Posted by: df1823 Go to Quoted Post
I just noticed there is an even newer version of Chrome that came out yesterday, 73.0.3683.75 i believe, so when i ran this report in my environment it flagged up the machines with that version as out of date.

Is there any way i can update the report so it will class anything running 72.0.3626.121 or higher as safe?



Thanks for this very post!! I made some edits to the originating report to answer this very question!! Very simple edits change the '=' signs to '>=' and the '<>' to '<'. Worked for me!:

Code:
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,
  Case
    When tblSoftware.softwareVersion >= '72.0.3626.121' Then 'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblSoftware.Lastchanged,
  Case
    When tblSoftware.softwareVersion < '72.0.3626.121' Then '#ffadad'
    Else '#d4f4be'
  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 '%Google Chrome%' 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,
  Case
    When tblMacApplications.Version >= '72.0.3626.121' Then 'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName As Software,
  tblMacApplications.Version As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblMacApplications.LastChanged,
  Case
    When tblMacApplications.Version < '72.0.3626.121' Then '#ffadad'
    Else '#d4f4be'
  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 '%Google Chrome%' 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,
  Case
    When tblLinuxSoftware.Version >= '72.0.3626.121-1' Then 'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName As Software,
  tblLinuxSoftware.Version As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblLinuxSoftware.LastChanged,
  Case
    When tblLinuxSoftware.Version < '72.0.3626.121-1' Then '#ffadad'
    Else '#d4f4be'
  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 '%google-chrome%' And
  tblState.Statename = 'Active'
Order By Domain,
  AssetName,
  Software
df1823
#21df1823 Member Posts: 7  
posted: 3/19/2019 3:28:53 PM(UTC)
Thats worked for me! thanks for this
Argon0
#22Argon0 Member Posts: 37  
posted: 4/4/2019 4:12:03 PM(UTC)
Eh? So... Which is the current/most up to date version of the report?

I'm running on SQL Express....

Cheers
Esben.D
#23Esben.D Member Administration Original PosterPosts: 1,768  
posted: 4/5/2019 8:21:16 AM(UTC)
Originally Posted by: Argon0 Go to Quoted Post
Eh? So... Which is the current/most up to date version of the report?

I'm running on SQL Express....

Cheers


If you just want the basic report, best thing is to grab the report from the original post, however it's best you update the version numbers to the latest available Chrome version then.

Active Discussions

Lansweeper Patch Tuesday report, last 3 months
by  dshu   Go to last post Go to first unread
Last post: Yesterday at 7:04:41 PM(UTC)
Lansweeper Active Assets Versus Non-Active
by  RC62N  
Go to last post Go to first unread
Last post: 8/23/2019 9:29:26 PM(UTC)
Report Center Low Hard Disk Space (coloured) - Win+Linux
by  Olivier J.   Go to last post Go to first unread
Last post: 8/23/2019 4:16:02 PM(UTC)
Lansweeper check if process is running report
by  Randomusername  
Go to last post Go to first unread
Last post: 8/23/2019 3:29:16 PM(UTC)
Lansweeper Top 10 users submitting tickets
by  ChuckSchurman   Go to last post Go to first unread
Last post: 8/22/2019 3:53:21 AM(UTC)
Lansweeper Identifying weak protocols
by  Jason Tree  
Go to last post Go to first unread
Last post: 8/20/2019 3:06:03 PM(UTC)
Lansweeper Ticket Report Overview
by  briangmiller   Go to last post Go to first unread
Last post: 8/20/2019 1:49:35 PM(UTC)
Lansweeper switch ports with multiple assets
by  FischbachM  
Go to last post Go to first unread
Last post: 8/19/2019 9:10:42 AM(UTC)