Chart report with count of Chassis Type

[galesj]

I'm trying to create a Chart report to count each of the Chassis types in our environment. I have a report that gives me the Chassis type, but having trouble turning it into a count of each type:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
TsysChassisTypes.ChassisName As Chassis
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSystemEnclosure On
tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName

Bonus would be the ability to group certain chassis types into "desktop" or "laptop", but I'd be happy with just the raw count of each chassis type for now.

Thanks!

[galesj]

This is perfect, thank you very much.

I'm shaking my head at how little code you used to get the job done lol.

I did have to switch the order so the Chart widget shows correctly:

Select Top 1000000 web50repchassisoverview.ChassisName,
Count(Distinct web50repchassisoverview.AssetID) As Count
From web50repchassisoverview
Group By web50repchassisoverview.ChassisName

This is exactly what I wanted, thanks again.

[ajokerst]

galesj wrote:
This is perfect, thank you very much.

I'm shaking my head at how little code you used to get the job done lol.

I did have to switch the order so the Chart widget shows correctly:

Select Top 1000000 web50repchassisoverview.ChassisName,
Count(Distinct web50repchassisoverview.AssetID) As Count
From web50repchassisoverview
Group By web50repchassisoverview.ChassisName

This is exactly what I wanted, thanks again.

No problem. I dug a bit to find it as well. To your last portion of what you want.. I can not remember how to do this (group counts). But if you google around you should be able to group counts together based on Chassis name.

[ajokerst · Accepted solution]

OK so this is a bit of a hack using an existing report. So you can sort of understand what is going on I will give a high level overview of what I am doing... So Lansweeper stores its reports in SQL as "Views". Views are pretty much cherry picked tables that consolidate data into pretty columns. This makes this take simple. So below is my SQL code that you need to use to get what you want. It uses the view "web50repchassisoverview" that uses data from several other tables to get the end result.

At this point since we have the items we need we just need to group items and count them. Which is what the code below does.

Select Top 1000000 Count(Distinct web50repchassisoverview.AssetID) As Count,
  web50repchassisoverview.ChassisName
From web50repchassisoverview
Group By web50repchassisoverview.ChassisName