Select Top 1000000 Switch.AssetID,
Switch.icon,
Switch.AssetName,
Switch.[If],
Switch.ifName,
Switch.ifAlias,
Switch.IfDescription,
Switch.Type,
Switch.Admin,
Switch.MTU,
Switch.Speed,
Switch.[IP Address],
Switch.Mask,
Switch.Portname,
Switch.Vlan,
Switch.MacAddress,
tsysAssetTypes1.AssetTypeIcon16 As icon2,
Switch.deviceassetid,
tblAssets.AssetName As deviceassetname,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
Switch.AssetMacAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Firstseen,
tblAssets.Lastseen
From (Select Top 1000000 tblAssets1.AssetID,
tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets1.AssetName,
tblSNMPInfo.IfIndex As [If],
tblSNMPInfo.ifName,
tblSNMPInfo.ifAlias,
tblSNMPInfo.IfDescription,
tblSNMPIfTypes.IfTypename As Type,
Case When tblSNMPInfo.IfAdminstatus = 1 Then 'Up'
When tblSNMPInfo.IfAdminstatus = 2 Then 'Down' Else 'Testing'
End As Admin,
tblSNMPInfo.IfMTU As MTU,
Case When (tblSNMPInfo.IfSpeed Is Null Or tblSNMPInfo.IfSpeed = 0) Then ''
When tblSNMPInfo.IfSpeed >
999999999 Then Convert(nvarchar(255),Cast(tblSNMPInfo.IfSpeed /
1000000000 As float)) + 'Gbit'
When tblSNMPInfo.IfSpeed >
999999 Then Convert(nvarchar(255),Cast(tblSNMPInfo.IfSpeed /
1000000 As float)) + 'Mbit'
Else Convert(nvarchar(255),Cast(tblSNMPInfo.IfSpeed / 1000 As float)) +
'Kbit' End As Speed,
tblSNMPInfo.IfIPAddress As [IP Address],
tblSNMPInfo.IfMask As Mask,
tblSNMPInfo.IfMacaddress As MacAddress,
tblSNMPAssetMac.AssetMacAddress,
tblSNMPInfo.Portname,
tblSNMPInfo.Vlan,
tblAssetMacAddress.AssetID As deviceassetid
From tblSNMPInfo
Left Join tblAssets As tblAssets1 On tblSNMPInfo.AssetID =
tblAssets1.AssetID
Left Join tsysAssetTypes On tblAssets1.Assettype = tsysAssetTypes.AssetType
Left Outer Join tblSNMPIfTypes On tblSNMPInfo.IfType = tblSNMPIfTypes.IfType
Left Outer Join (tblAssetMacAddress
Right Outer Join tblSNMPAssetMac On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress) On tblSNMPInfo.IfIndex =
tblSNMPAssetMac.IfIndex And tblSNMPInfo.AssetID = tblSNMPAssetMac.AssetID
Where tsysAssetTypes.AssetTypename = 'Switch'
Order By tblAssets1.AssetName,
[If]) As Switch
Left Outer Join tblAssets On Switch.deviceassetid = tblAssets.AssetID
Left Outer Join tsysAssetTypes As tsysAssetTypes1 On tblAssets.Assettype =
tsysAssetTypes1.AssetType
Left Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Left Outer Join tsysIPLocations On tblAssets.LocationID =
tsysIPLocations.LocationID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode