
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎11-19-2018 03:18 PM
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!
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!
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎11-19-2018 09:09 PM
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.
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
3 REPLIES 3

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎11-19-2018 09:44 PM
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.
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.

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎11-20-2018 02:51 PM
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.

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎11-19-2018 09:09 PM
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.
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
