Notification

Icon
Error

Lansweeper custom report

Posted: Friday, April 9, 2021 4:22:46 PM(UTC)
Arelan

Arelan

Member Original PosterPosts: 2
1
Like
This issue has been solved! Click here to view the solution
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 Angel

I hope someone can help me with this.

With kind regards.



Arelan
#1Arelan Member Original PosterPosts: 2  
posted: 4/13/2021 3:56:05 PM(UTC)
Got a little bit further:

Select Top 1000000 tblAssets.AssetID As [Asset ID],
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As Icon,
tsysIPLocations.IPLocation As [SER Location],
tblSNMPInfo.IfDescription As Port,
tblAssets.IPAddress
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

This shows everything i asked above but not the right IP-adress from the switch it's connected to, i hope someone can help with this last part.
Hendrik.VE
#2Hendrik.VE Member Posts: 81  
posted: 4/14/2021 10:52:03 AM(UTC)
Try this:
Code:
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


Be aware that it can give multiple lines per asset, if you have multiple IP's.

Active Discussions

Lansweeper Automatic Follow-Up for Tickets
by  Francis Lee Mondia - Endace   Go to last post Go to first unread
Last post: Yesterday at 11:06:51 PM(UTC)
Lansweeper Can't see devices on Lansweeper
by  vqT4cDoP9iXyMZwoDUWU  
Go to last post Go to first unread
Last post: Yesterday at 8:33:21 PM(UTC)
Lansweeper LAPS managed password
by  SystemAdmin   Go to last post Go to first unread
Last post: Yesterday at 6:08:42 PM(UTC)
Lansweeper Install Adobe Reader Error
by  Brandon  
Go to last post Go to first unread
Last post: Yesterday at 2:46:54 PM(UTC)
Lansweeper Stop deployment
by  Tyler M.   Go to last post Go to first unread
Last post: Yesterday at 1:59:22 PM(UTC)
Lansweeper "Out of memory" in errorlog.txt
by  dua  
Go to last post Go to first unread
Last post: Yesterday at 1:00:42 PM(UTC)
Lansweeper Installation issues
by  Troy   Go to last post Go to first unread
Last post: Yesterday at 9:24:54 AM(UTC)
Lansweeper End of Life Asset Report
by  DJ Bradley  
Go to last post Go to first unread
Last post: 5/11/2021 4:09:33 PM(UTC)