Software Version Charts
Chart SoftwareUsing 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
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