cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
hirslanden
Engaged Sweeper
I'm new to lansweeper and I'm looking for a reporting possibility to display/report kind a grid:

Asset Types per IP Location (in sum).

Is it possible to create such a report?

We would use that as KPI report to see how many Assets per type we have at which (ip) location.


1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
I moved this topic from the Report Center to Report Requests & Questions. Please keep in mind that the Report Center is for posting ready-to-use reports, not questions.

A sample report that lists the information you are after can be seen below. Instructions for adding this report to your installation 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 is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  • Updating to Lansweeper 5.3, if you haven't already. Lansweeper 5.3 includes a database dictionary, which is linked at the top of the report builder. The dictionary explains in great detail what each table and field stores.

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

View solution in original post

4 REPLIES 4
RCorbeil
Honored Sweeper II
Challenge accepted.

I thought I would take the opportunity to try to wrap my head around PIVOT. The LANSweeper report editor won't let you do this directly (or it won't on my installation, at least), but if you're willing to edit the view that the editor creates on the server, it can be done.

Step 1: create a basic report in LANSweeper, naming it as whatever you want the resulting grid report to be called. The built-in report editor doesn't support PIVOT, so you need to set things up as if this is the final form of the report. Take note of the view name created when you save the report. (i.e. http://yourserver:82/report.aspx?det=web50long-hex-value)

Step 2: use your preferred SQL management tool to connect to the server and script the above view to an ALTER script.

Step 3: isolate the SELECT statement and replace it with
SELECT
AssetTypeName,
[your IP group name 1],
[your IP group name 2],
[your IP group name 3],
[Null]
FROM
( SELECT Top 1000000
IsNull(tsysIPLocations.IPLocation, 'Null') AS IPLocation,
tsysAssetTypes.AssetTypename,
tblAssets.AssetID
FROM
tblAssets
INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
LEFT JOIN tsysIPLocations ON tblAssets.IPNumeric >= tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
WHERE
tblAssetCustom.State = 1
) AS pivotSource
PIVOT
( Count(AssetID)
FOR IPLocation IN ([your IP group name 1], [your IP group name 2], [your IP group name 3], [Null])
) AS pivotResult

Substitute your actual IP group names for "your IP group name 1", "your IP group name 2", etc.

I included [Null] because I noticed that I have some assets, primarily monitors, that, not surprisingly, don't have associated IP groups. If you want a different label, change the "IsNull(tsysIPLocations.IPLocation, 'Null') AS IPLocation," and reflect that by replacing the two [Null]s to match your new label.

No promises; this is my first attempt at using PIVOT and you need to work outside the LANSweeper editor, but my output looks like what you described.
Susan_A
Lansweeper Alumni
SQL displays table records (e.g. IP locations) as lines by default, not columns. There are possible workarounds to turn rows into columns (using subqueries or the PIVOT or STUFF function), but we do not currently provide support for implementing these unfortunately.
hirslanden
Engaged Sweeper
Thank you very much Susan.A! ...I'm looking actually for such a solution (please see attachment)... if that's possible? 🙂
Susan_A
Lansweeper Alumni
I moved this topic from the Report Center to Report Requests & Questions. Please keep in mind that the Report Center is for posting ready-to-use reports, not questions.

A sample report that lists the information you are after can be seen below. Instructions for adding this report to your installation 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 is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  • Updating to Lansweeper 5.3, if you haven't already. Lansweeper 5.3 includes a database dictionary, which is linked at the top of the report builder. The dictionary explains in great detail what each table and field stores.

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

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now