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 Using tblO365User report for devices Out of warranty
by  QueryLSTech   Go to last post Go to first unread
Last post: Yesterday at 5:15:37 PM(UTC)
Lansweeper Duplicate assets, random monitor unique keys
by  kloosterd  
Go to last post Go to first unread
Last post: Yesterday at 1:00:34 PM(UTC)
Lansweeper Scanning - nothing appears in the queue
by  LS IT Admins   Go to last post Go to first unread
Last post: Yesterday at 11:08:22 AM(UTC)
Lansweeper Broken scanning of AD
by  LS IT Admins  
Go to last post Go to first unread
Last post: Yesterday at 10:59:35 AM(UTC)
Lansweeper New ticket auto-assignment & default state
by  Brandon   Go to last post Go to first unread
Last post: 5/13/2021 5:21:31 PM(UTC)
Lansweeper Automatic Follow-Up for Tickets
by  Francis Lee Mondia - Endace  
Go to last post Go to first unread
Last post: 5/12/2021 11:06:51 PM(UTC)
Lansweeper Can't see devices on Lansweeper
by  vqT4cDoP9iXyMZwoDUWU   Go to last post Go to first unread
Last post: 5/12/2021 8:33:21 PM(UTC)
Lansweeper LAPS managed password
by  SystemAdmin  
Go to last post Go to first unread
Last post: 5/12/2021 6:08:42 PM(UTC)