Asset Count

[s_subramanian]

Hi,

Is there any way that i can take the Asset count report by model wise or location wise or asset manufacturer wise

like for example..

1.
Location1 - 100
Location 2 - 200
Location 3 - 300

2.
Thinkpad 230 - 100
Thinpas X240 - 100

3.
Dell - 100
Lenovo - 100
Hewlett-Packard - 100

Is it possible or am i asking too much.

[Daniel_B · Accepted solution]

You can do this by using aggregate functions. Enable Grouping for all fields but tblAssets.AssetID and on tblAssets.AssetID use Count as aggregate function. You can do this in many variations. Please find two example reports below.

Instructions for running reports can be found here. 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.

Count based on Model:

Select Top 1000000 tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  Count(tblAssets.AssetID) As Number
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1
Group By tblAssetCustom.Manufacturer,
  tblAssetCustom.Model
Order By tblAssetCustom.Manufacturer,
  tblAssetCustom.Model

Count based on IP location:

Select Top 1000000 tsysIPLocations.IPLocation,
  Count(tblAssets.AssetID) As Number
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations
    On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
    tsysIPLocations.EndIP
Where tblAssetCustom.State = 1
Group By tsysIPLocations.IPLocation