Notification

Icon
Error

All switches and the assets connected to them

Posted: Tuesday, November 18, 2014 2:39:19 PM(UTC)
Bart.M

Bart.M

Member Original PosterPosts: 13
6
Like
List of switches, some details of the switch and IP, Mask, Mac and Name of the asset connected to them.

No criteria.

Sorted on:
- Assetname name of the switch
- If (alias for IfIndex)

Code:
Select Distinct Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename As AssetType,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblSNMPInfo.IfIndex As [Interface],
  tblSNMPInfo.IfDescription As Name,
  tblSNMPIfTypes.IfTypename As Type,
  tblSNMPInfo.IfAdminstatus As Admin,
  tblSNMPInfo.IfMTU As MTU,
  Ceiling(tblSNMPInfo.IfSpeed / 1000 / 1000) As Speed,
  tblSNMPInfo.IfIPAddress As IP,
  tblSNMPInfo.IfMask As Mask,
  tblSNMPInfo.IfMacaddress As MAC,
  tblAssets1.AssetName As Asset
From tblAssets
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblSNMPInfo On tblAssets.AssetID = tblSNMPInfo.AssetID
  Inner Join tblSNMPIfTypes On tblSNMPIfTypes.IfType = tblSNMPInfo.IfType
  Left Join tblSNMPAssetMac On tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID And
    tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex
  Left Join tblAssetMacAddress On tblAssetMacAddress.Mac =
    tblSNMPAssetMac.AssetMacAddress
  Left Join tblAssets tblAssets1 On tblAssets1.AssetID =
    tblAssetMacAddress.AssetID
Where tsysAssetTypes.AssetTypename = 'switch'
Order By tblAssets.AssetName,
  [Interface]
CoolDood
#1CoolDood Member Posts: 16  
posted: 12/11/2019 12:15:53 PM(UTC)
I am only getting information on one switch
rader
#2NWSF Member Posts: 30  
posted: 12/12/2019 5:23:34 PM(UTC)
Nicely done. Lists all of our managed switches only. No unmanaged switches of course.
Thanks.
pryan67
#3pryan67 Member Posts: 102  
posted: 4/9/2020 1:49:56 PM(UTC)
This has a lot of potential for us, but it doesn't give us hostnames of workstations/servers for some reason, and only shows one IP address per switch.

Active Discussions

Action Remote Uninstaller
by  hirogen   Go to last post Go to first unread
Last post: 7/15/2021 2:09:47 PM(UTC)
Lansweeper uVNC Portable (trying to replace LSRemote)
by  CyberCitizen  
Go to last post Go to first unread
Last post: 6/15/2021 11:40:21 PM(UTC)
Action Password Status
by  steveb   Go to last post Go to first unread
Last post: 5/20/2021 5:24:13 PM(UTC)
Lansweeper Verify a successful copy to user computer
by  Brandon  
Go to last post Go to first unread
Last post: 4/22/2021 9:09:50 PM(UTC)
Lansweeper Pablo
by  Pablo   Go to last post Go to first unread
Last post: 4/20/2021 8:05:07 PM(UTC)
Lansweeper Can Actions work when using when using a Ubuntu machine
by  mzipperer  
Go to last post Go to first unread
Last post: 4/1/2021 10:16:34 PM(UTC)
Lansweeper Remove user from local admin group
by  jhartley@alcona-county.net   Go to last post Go to first unread
Last post: 3/24/2021 4:27:30 PM(UTC)
Lansweeper Network Scanning
by  MZU  
Go to last post Go to first unread
Last post: 3/7/2021 8:23:10 AM(UTC)