Software Version Charts

Chart Software

Using the ‘Not Latest Version’ report methodology, here are some chart reports to make a software compliance dashboard that quickly shows you the different software versions, as well as the latest version installed. I will provide one for a Version/Major/Minor scenario (like Zoom – 5.17.11) as well as a standard Version/Major/Minor/Hotfix scenario (Like Chrome). You can take these reports and simply pick the appropriate versioning report and change the application name to what you want to see. You can find more info about this topic in Pro Tips 52.

To use this in a chart widget, prefix the report name with „Chart:“ and select it in the chart report widget found on a dashboard.

Chart: Zoom Installed Software Versions (Version/Major/Minor format) Lansweeper On-Prem Query

Select Top 1000000 Case
    When query1.LatestVersion Is Null Then
      query1.Version + ' (Latest Installed)'
    Else query1.Version
  End As Version,
  Count(query1.assetid) As Count
From (Select Top 1000000 tblAssets.AssetID,
      tblSoftware.softwareVersion As Version,
      b.LatestVersion
    From tblAssets
      Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
      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 (Select __.SoftID,
          __.Major,
          __.Minor,
          __.Build,
          LatestVersion = __.softwareVersion
        From (Select *,
              r = Row_Number() Over (Partition By _.SoftID Order By
              _.Major Desc, _.Minor Desc, _.Build Desc)
            From (Select Distinct tblSoftware.SoftID,
                  Major = Convert(INT,Case
                    When CharIndex(' (', tblSoftware.softwareVersion) > 0 Then
                      SubString(tblSoftware.softwareVersion, 1, CharIndex('.',
                      tblSoftware.softwareVersion) - 1)
                    Else ParseName(tblSoftware.softwareVersion, 3)
                  End),
                  Minor = Convert(INT,ParseName(Case
                    When CharIndex(' (', tblSoftware.softwareVersion) > 0 Then
                      Left(tblSoftware.softwareVersion, CharIndex(' (',
                      tblSoftware.softwareVersion) - 1)
                    Else tblSoftware.softwareVersion
                  End, 2)),
                  Build = Convert(INT,ParseName(Case
                    When CharIndex(' (', tblSoftware.softwareVersion) > 0 Then
                      Left(tblSoftware.softwareVersion, CharIndex(' (',
                      tblSoftware.softwareVersion) - 1)
                    Else tblSoftware.softwareVersion
                  End, 1)),
                  tblSoftware.softwareVersion
                From tblSoftware
                  Inner Join tblSoftwareUni On tblSoftware.SoftID =
                      tblSoftwareUni.SoftID
                Where tblSoftwareUni.softwareName = 'Zoom') _) __
        Where __.r = 1) b On tblSoftware.SoftID = b.SoftID And
          tblSoftware.softwareVersion <> b.LatestVersion
    Where tblSoftwareUni.softwareName = 'Zoom' And tblState.Statename =
      'Active') As query1
Group By query1.Version,
  query1.LatestVersion
Order By query1.LatestVersion,
  query1.Version Desc

Show

Hide

Chart: Google Chrome Installed Software Versions (Version/Major/Minor/Hotfix format) Lansweeper On-Prem Query

Select Top 1000000 Case
    When query1.LatestVersion Is Null Then
      query1.Version + ' (Latest Installed)'
    Else query1.Version
  End As Version,
  Count(query1.assetid) As Count
From (Select Top 1000000 tblAssets.AssetID,
      tblSoftware.softwareVersion As Version,
      b.LatestVersion
    From tblAssets
      Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
      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 (Select __.SoftID,
          __.Major,
          __.Minor,
          __.Build,
          __.Revision,
          LatestVersion = __.softwareVersion
        From (Select *,
              r = Row_Number() Over (Partition By _.SoftID Order By
              _.Major Desc, _.Minor Desc, _.Build Desc, _.Revision Desc)
            From (Select Distinct tblSoftware.SoftID,
                  Major = Convert(int,ParseName(tblSoftware.softwareVersion,
                  4)),
                  Minor = Convert(int,ParseName(tblSoftware.softwareVersion,
                  3)),
                  Build = Convert(int,ParseName(tblSoftware.softwareVersion,
                  2)),
                  Revision = Convert(int,ParseName(tblSoftware.softwareVersion,
                  1)),
                  tblSoftware.softwareVersion
                From tblSoftware
                  Inner Join tblSoftwareUni On tblSoftware.SoftID =
                      tblSoftwareUni.SoftID
                Where tblSoftwareUni.softwareName = 'Google Chrome') _) __
        Where __.r = 1) b On tblSoftware.SoftID = b.SoftID And
          tblSoftware.softwareVersion <> b.LatestVersion
    Where tblSoftwareUni.softwareName = 'Google Chrome' And tblState.Statename =
      'Active') As query1
Group By query1.Version,
  query1.LatestVersion
Order By query1.LatestVersion,
  query1.Version Desc

Show

Hide