→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
‎04-09-2021 05:22 PM - last edited on ‎05-15-2024 03:20 PM by Riley
Dear Lady's and Gents,
I been asked to make a custom lansweeper report but im not really a SQL expert :).
I searched multiple lansweeper posts, but didnt quite found my answer. I was looking for the following:
I need a custom report that shows: AssestName from ad objects (like desktops, servers, printers etc), their IP location, The IP-adress from the switch it's connected to, and the port from the switch.
I tried the following SQL query:
Select Top 1000000 tblAssets.AssetID As [Asset ID],
tblAssets.AssetName,
tsysIPLocations.IPLocation As [SER Location],
tblAssets.IPAddress,
tblSNMPInfo.IfDescription As Port
From tblAssets
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblSNMPInfo On tblAssets.AssetID = tblSNMPInfo.AssetID
So with this query i got the right IP location, Switch IP-adress and port but it doesn't align with the right computer objects. I'm hoping that i was on the right track with the specific tables but i lack the knowledge in the execution part
I hope someone can help me with this.
With kind regards.
Solved! Go to Solution.
‎04-13-2021 04:56 PM
‎04-14-2021 11:52 AM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As Icon,
tsysIPLocations.IPLocation As [SER Location],
tblAssets.IPAddress As [Asset IP],
tblAssets_1.IPAddress As [Switch IP],
tblSNMPInfo.IfDescription As [Switch Port],
tblAssets_1.AssetName As [Switch Name]
From tsysAssetTypes
Inner Join tblAssets On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetMacAddress On
tblAssets.AssetID = tblAssetMacAddress.AssetID
Inner Join tblSNMPAssetMac On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress
Inner Join tblSNMPInfo On tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex And
tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID
Inner Join tblAssets tblAssets_1 On tblSNMPInfo.AssetID = tblAssets_1.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Order By tblAssets.AssetName
‎04-14-2021 11:52 AM
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As Icon,
tsysIPLocations.IPLocation As [SER Location],
tblAssets.IPAddress As [Asset IP],
tblAssets_1.IPAddress As [Switch IP],
tblSNMPInfo.IfDescription As [Switch Port],
tblAssets_1.AssetName As [Switch Name]
From tsysAssetTypes
Inner Join tblAssets On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetMacAddress On
tblAssets.AssetID = tblAssetMacAddress.AssetID
Inner Join tblSNMPAssetMac On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress
Inner Join tblSNMPInfo On tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex And
tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID
Inner Join tblAssets tblAssets_1 On tblSNMPInfo.AssetID = tblAssets_1.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Order By tblAssets.AssetName
‎04-13-2021 04:56 PM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now