Asset count report

[vlad_greyze]

Hi,
We have over 100 IP Locations and 3500 assets. I need to prepare a report that will give me a count of the following per IP Location but I need to have all IP Locations on one report:

Servers
Desktops
Laptops
VOIP phones
Switches
UPS
Printers
Wireless Access Points
IP Cameras
Total count

Your help is much appreciated!

[Daniel_B]

No sure if I understand you correctly. Your physical locations are determined in Lansweeper by the assets' IP addresses and therefore through IP locations. The report given here already lists all assets at all locations (even those which might not have an IP location assigned because none was defined for their IP).

If you run the report and export it to XLS, you should have all numbers on one sheet.

Please correct me if I understood something wrong.

Note: A report can't be run on another report. Instead you'll need to modify the report in order to show what you need. If you would like to sum up several numbers already listed in one report it might be best to do this in Excel.

[vlad_greyze]

Thanks Daniel for your response. This has worked the magic!

Am I able to run a report on the results of another report (sub-reporting I guess...)? I will give an example: We have 27 branches and 1 data centre. Each of the locations has 10 VLANs, so the total number of IP locations = 280. IP Locations are labelled in the following manner: Branch name - VLAN tag. For example Albury - Branch data IP Range, Albury - Security IP Range, Albury - Management IP Range etc, so a total of 10 IP Locations per one physical location.

Using the query you have given I can report on the number of each of the devices per physical location. Now having 28 physical locations I will have 28 reports. In order to get a full picture of our assets nationwide on one report I need to query all of the 28 report results so I get all the numbers on one page. Is this possible at all or I am asking too much?

PS It would be handy if I could place IP Locations in a container of some kind and give it a name so I could also sort locations geographically.

Thanks,

[Daniel_B]

This report should already list asset types such as IP phone or Printer. tblComputersystem.Domainrole will only be considered if the asset is a Windows asset.

[vlad_greyze]

Thanks for your response. How do I get a count of phones, printers etc? Most likely they won't be under tblComputersystem.Domainrole. Correct me if I am wrong.

[Daniel_B · Accepted solution]

Asset types are stored in tsysassettypes.assettypename. In order to differentiate Windows servers, desktops and laptops, you'll need to use a case statement which checks tblComputersystem.Domainrole (bigger than 1 for servers) and if the computer has an entry in tblPortableBattery:

Select Top 1000000 tsysIPLocations.IPLocation,
  tAssettype.[asset type],
  Count(tblAssets.AssetID) As number
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join (Select tblAssets.AssetID,
    Case When tblAssets.Assettype = -1 Then Case
        When tblComputersystem.Domainrole > 1 Then 'Server' Else Case
          When Coalesce(tblPortableBattery.AssetID, 0) <> 0 Then 'Laptop'
          Else 'Desktop' End End Else tsysAssetTypes.AssetTypename
    End As [asset type]
  From tblAssets
    Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
    Left Join tblComputersystem On tblComputersystem.AssetID = tblAssets.AssetID
    Left Join tblPortableBattery On tblPortableBattery.AssetID =
      tblAssets.AssetID) tAssettype On tAssettype.AssetID = tblAssets.AssetID
  Left Join tsysIPLocations
    On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
    tsysIPLocations.EndIP
Where tblAssetCustom.State = 1
Group By tsysIPLocations.IPLocation,
  tAssettype.[asset type]
Order By tsysIPLocations.IPLocation,
  tAssettype.[asset type]

A total count needs to be made additionally to this report, or by a second report like the one here but without tAssettype.