Reporting on switch port information

[RJAH]

Love the SNMP port mapping feature in v5, but having difficulty integrating connection data into reports. I'd like to report on workstations for which this information is present (and conversly those that haven't populated), for example Hostname, Computer OU, Switch Name, Port Number.

I can see the data in SQL (tblSNMPAssetMac and tblSNMPInfo, along with tblAssetMacAddress
and tblAssets) but can't work out how to link the tables together.
Any help gratefully received!

[chads]

not sure when the next update is but this is the code we used on the report builder to make the report work. Once you put this code in and save the report you can't make changes to the report but the report will work. Of course you can just wait till lansweeper releases the next update.

with a1 as 
(
  select * from tblassets
)

select 
  a.assetid        as [Asset ID],
  a.assetname      as [Asset Name],
  c.ou             as [Organization Unit],
  a1.assetname     as [Switch],
  si.ifdescription as [Port]
from tblassetmacaddress as am
inner join tblsnmpassetmac as sam on am.mac = sam.assetmacaddress
inner join tblsnmpinfo as si on sam.assetid = si.assetid and sam.ifindex = si.ifindex
inner join tblassets as a on am.assetid = a.assetid
inner join a1 on si.assetid = a1.assetid
left outer join dbo.tbladcomputers as c on a.assetid = c.assetid
order by a1.assetname, si.ifindex

[Hemoco · Accepted solution]

The sql syntax is correct but it gives an error due to a bug in the report builder, will be fixed in the next update.

[FrankW]

Same problem here, can you please post the solution if the support figured it out? 🙂

thx!

[chads]

Well for whatever reason we can't get this to work on the lansweeper report builder. works on the SQL side but not in the report builder. i have sent an email into Lansweeper support will see what they have to say.

[chads]

5052 is our version
SQL Express 2008 Lansweeper install.

I have one of coders looking at it to maybe something we need to do on the server.

[RJAH]

Chads if you've copied like for like then I can't think why. I've just double-checked by copying the code directly from my post into SQL management studio and it runs fine, same when pasted into report builder query window. For reference, we're still running v.5049

[chads]

Getting Incorrect Syntax near keyword 'AS'

[RJAH]

Never mind, I worked it out. For any interested parties the query below will list asset name, OU, switch and port they connect to;

SELECT     TOP (100) PERCENT dbo.tblAssets.AssetID, dbo.tblAssets.AssetName, dbo.tblADComputers.OU, tblAssets_1.AssetName AS Switch, 
                      dbo.tblSNMPInfo.IfDescription AS Port
FROM         dbo.tblAssetMacAddress INNER JOIN
                      dbo.tblSNMPAssetMac ON dbo.tblAssetMacAddress.Mac = dbo.tblSNMPAssetMac.AssetMacAddress INNER JOIN
                      dbo.tblSNMPInfo ON dbo.tblSNMPAssetMac.AssetID = dbo.tblSNMPInfo.AssetID AND 
                      dbo.tblSNMPAssetMac.IfIndex = dbo.tblSNMPInfo.IfIndex INNER JOIN
                      dbo.tblAssets ON dbo.tblAssetMacAddress.AssetID = dbo.tblAssets.AssetID INNER JOIN
                      dbo.tblAssets AS tblAssets_1 ON dbo.tblSNMPInfo.AssetID = tblAssets_1.AssetID LEFT OUTER JOIN
                      dbo.tblADComputers ON dbo.tblAssets.AssetID = dbo.tblADComputers.AssetID
ORDER BY Switch, dbo.tblSNMPInfo.IfIndex