GRID: Asset Types per IP Location

[hirslanden]

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.

[RCorbeil]

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]

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]

Thank you very much Susan.A! ...I'm looking actually for such a solution (please see attachment)... if that's possible? 🙂

[Susan_A · Accepted solution]

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