cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
vlad_greyze
Engaged Sweeper II
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!
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
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.

View solution in original post

5 REPLIES 5
Daniel_B
Lansweeper Alumni
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
Engaged Sweeper II
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
Lansweeper Alumni
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
Engaged Sweeper II
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
Lansweeper Alumni
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.