cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
steve_shaw
Engaged Sweeper
Hi,

I'm looking for a report which will give me a count of certain device types by group.

For example I have a group named "Milan" which contains all machines in the Milan IP ranges (we have multiple ranges per site).

I managed to create this report by IP range following advice on this forum (thanks!), which gives me results in this format:

Milan IP range 1 - Printers - 12
Milan IP range 1 - Windows - 30
Milan IP range 2 - Printers - 4
Milan IP range 2 - Windows - 10

And so on.

However, at a glance this requires manually adding up the devices per range to discover the devices per site.

How do I write a report which will give me a count of all assets of certain types by group, rather than by IP range?

For example:

Milan - Printers - 16
Milan - Windows - 40

and so on.

Thanks for any help,

Steve.
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
The report below provides asset type counts per asset group. You can limit the report to a specific group within the report results, if required.
Select Top 1000000 tblAssetGroups.AssetGroup,
tsysAssetTypes.AssetTypename As [Asset Type],
Count(tblAssets.AssetID) As Count
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1
Group By tblAssetGroups.AssetGroup,
tsysAssetTypes.AssetTypename
Order By tblAssetGroups.AssetGroup,
[Asset Type]

If you are interested in building or modifying reports, we would recommend:
  • Reviewing some SQL tutorials, as the Lansweeper report builder uses standard SQL queries. This seems like a good tutorial.
  • Updating to Lansweeper 5.2, if you haven't already. Lansweeper 5.2 includes a database dictionary, which is linked at the top of the report builder.

View solution in original post

1 REPLY 1
Susan_A
Lansweeper Alumni
The report below provides asset type counts per asset group. You can limit the report to a specific group within the report results, if required.
Select Top 1000000 tblAssetGroups.AssetGroup,
tsysAssetTypes.AssetTypename As [Asset Type],
Count(tblAssets.AssetID) As Count
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1
Group By tblAssetGroups.AssetGroup,
tsysAssetTypes.AssetTypename
Order By tblAssetGroups.AssetGroup,
[Asset Type]

If you are interested in building or modifying reports, we would recommend:
  • Reviewing some SQL tutorials, as the Lansweeper report builder uses standard SQL queries. This seems like a good tutorial.
  • Updating to Lansweeper 5.2, if you haven't already. Lansweeper 5.2 includes a database dictionary, which is linked at the top of the report builder.