cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
CyberCitizen
Honored Sweeper
Hi Guy's

I am trying to create a chart report to show OS Version Numbers.

I have the following, but I can't get the first numbers listed first to show the Version Numbers and the count afterwards which is what I believe is required for the chart.

At present it displays the count numbers first so on the Chart that is what its filted by.

Select Top 1000000 Count(tblAssets.AssetID) As Total,
tblAssets.Version As Version
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Group By tblAssets.Version,
tsysOS.OSname
Order By Total Desc
3 REPLIES 3
CyberCitizen
Honored Sweeper
Got It. Bloody Reports...

Select Top 1000000 tblAssets.Version As Version,
Count(tblAssets.AssetID) As Count
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.Assettype = -1 And tblAssetCustom.State = 1
Group By tblAssets.Version,
tsysOS.OSname,
tblAssets.Assettype
Order By Count Desc
CyberCitizen
Honored Sweeper
Hi DontByteMe,

Thank you for that, I just need the version numbers (not the build numbers eg 1903, 1909 etc).

The issue with the pie chart I am having is it uses that first field for the results, which is the version numbers I need to sort by eg 1903.

With the report you provided I end up with the below when its made as a chart.

DontByteMe
Engaged Sweeper III
CyberCitizen wrote:
Hi Guy's

I am trying to create a chart report to show OS Version Numbers.

I have the following, but I can't get the first numbers listed first to show the Version Numbers and the count afterwards which is what I believe is required for the chart.

At present it displays the count numbers first so on the Chart that is what its filted by.

Select Top 1000000 Count(tblAssets.AssetID) As Total,
tblAssets.Version As Version
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Group By tblAssets.Version,
tsysOS.OSname
Order By Total Desc



Are you wanting this?

version

Select
Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblOperatingsystem.Lastchanged,
tsysOS.Image As icon,
tblOperatingsystem.Version As Build,
Case tblOperatingsystem.Version
When '10.0.10240' Then '1507'
When '10.0.10586' Then '1511'
When '10.0.14393' Then '1607'
When '10.0.15063' Then '1703'
When '10.0.16299' Then '1709'
When '10.0.17134' Then '1803'
When '10.0.17763' Then '1809'
When '10.0.18362' Then '1903'
When '10.0.18363' Then '1909'
Else '?'
End as Version
From tblAssets
Inner Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where
tsysOS.OSname = 'Win 10'
And tblAssetCustom.State = 1
Order By
Build,
tblAssets.AssetUnique