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: 12  
posted: 12/11/2019 12:15:53 PM(UTC)
I am only getting information on one switch
rader
#2NWSF Member Posts: 10  
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: 86  
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

Lansweeper License renewal - but why
by  mrusso   Go to last post Go to first unread
Last post: Yesterday at 5:01:47 PM(UTC)
Lansweeper Deployment Package Error Message
by  Brandon  
Go to last post Go to first unread
Last post: Yesterday at 2:04:25 PM(UTC)
Lansweeper Asset Type Mail Server
by  MarkPayton   Go to last post Go to first unread
Last post: Yesterday at 1:03:54 PM(UTC)
Lansweeper Upgrade Win 10 build to version 2004
by  Jean-FB  
Go to last post Go to first unread
Last post: 10/28/2020 7:34:29 PM(UTC)
Lansweeper Uptime only shows Standby
by  Gst4r   Go to last post Go to first unread
Last post: 10/28/2020 4:19:33 PM(UTC)
Lansweeper Excepciones
by  Pablo  
Go to last post Go to first unread
Last post: 10/27/2020 7:35:21 PM(UTC)
Lansweeper Help desk API
by  Skylar@Hennig   Go to last post Go to first unread
Last post: 10/27/2020 5:01:18 PM(UTC)
Lansweeper Helpdesk API
by  Skylar@Hennig  
Go to last post Go to first unread
Last post: 10/27/2020 4:44:50 PM(UTC)