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

View solution in original post

Lansweeper Alumni
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

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

Lansweeper Alumni
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.
Engaged Sweeper III
Same problem here, can you please post the solution if the support figured it out? 🙂

Lansweeper Alumni
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.
Lansweeper Alumni
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.
Engaged Sweeper II
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
Lansweeper Alumni
Getting Incorrect Syntax near keyword 'AS'
Engaged Sweeper II
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