Report showing device types by group

[steve_shaw]

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.

[Susan_A · Accepted solution]

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.