TblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex Left Join tblSNMPAssetMac On tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID And Inner Join tblSNMPIfTypes On tblSNMPIfTypes.IfType = tblSNMPInfo.IfType Inner Join tblSNMPInfo On tblAssets.AssetID = tblSNMPInfo.AssetID Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype TsysAssetTypes_1.AssetTypeIcon16 As icon2, When tsysMacVendor.Vendor Is Null Then 'MAC detected but Vendor not found' When tblSNMPAssetMac.AssetMacAddress is Null Then 'No MAC detected' TblSNMPAssetMac.AssetMacAddress As AssetMAC,
TblSNMPInfo.IfMacaddress As SwitchportMAC, When tblSNMPInfo.IfAdminstatus = 1 Then 'Up'Ĭeiling(tblSNMPInfo.IfSpeed / 1000 / 1000) As Speed, TsysAssetTypes.AssetTypename As AssetType, Select Distinct Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
Is very usefull to detect 'rogue' devices. I use the following (SQL Server) report which also shows me the vendor of the connected device when the MAC address is detected (if you still have table tsysMacVendor) and is color-coded based on the port status.