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: 11  
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 LSPUSH and Group Policy
by  HurricaneDave   Go to last post Go to first unread
Last post: Yesterday at 10:15:12 PM(UTC)
Lansweeper How to see additional info columns on Asset Groups
by  virtualnik8  
Go to last post Go to first unread
Last post: Yesterday at 7:40:41 PM(UTC)
Lansweeper Certificate status & end dates
by  doone128   Go to last post Go to first unread
Last post: Yesterday at 11:43:46 AM(UTC)
Lansweeper O365
by  Azkor  
Go to last post Go to first unread
Last post: 9/18/2020 10:45:23 PM(UTC)
Lansweeper 2012R2: LSAgent: Could not create SSL/TLS secure channel
by  JimL   Go to last post Go to first unread
Last post: 9/18/2020 6:35:00 AM(UTC)
Lansweeper Biometric Devices
by  FrankSc   Go to last post Go to first unread
Last post: 9/17/2020 6:56:05 PM(UTC)
Lansweeper Auto Assign Agent on First Email Response
by  Elwood472  
Go to last post Go to first unread
Last post: 9/17/2020 4:33:16 PM(UTC)