Assets and the switch they're connected to

[Bart_M]

List of all assets and the switch and port they are connected to.

Only shows assets:
- Connected to a switch

Sorted on:
- Asset name

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As Icon,
  tblSNMPAssetMac.AssetMacAddress,
  tblAssets_1.AssetName As Switch,
  tblSNMPInfo.IfIndex As Port
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
Order By tblAssets.AssetName

[santeroc]

Thank you for your report. I have adjusted it to suit my needs.
Here is the code :

Select Distinct Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.IPAddress,
tblAssets.AssetName,
tblSNMPInfo.IfIndex As Interface,
tblSNMPIfTypes.IfTypename As Type,
Ceiling(tblSNMPInfo.IfSpeed / 1000 / 1000) As Speed,
tblSNMPInfo.IfMacaddress As MAC,
tblAssets1.AssetName As Asset,
tblAssets.Firstseen,
tblAssets.Lastseen
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 tblAssets1.AssetName <> '' And tsysAssetTypes.AssetTypename = 'switch'
Order By tblAssets.AssetName,
Interface