Switch/Router Alert Reports

Add Switch Related Data to Your Alert Report Widget

The alert report dashboard widget allows you to add reports to a widget to quickly identify issues or get information at a glance. The alert reports are designed to provide a single number in a specific use case like the number of devices down, or the number of ports inactive. Since the reports added to this widget are automatically updated every minute, it will always have up-to-date information so you can quickly act on potential issues. These alert reports are part of the switch dashboard use case and you can read more about this use case in the Pro Tips #4 blog post.

You can find the following alert reports below:

• Switches/Routers Down
• Switch Port Operational Status Down
• Switch Port Admin Status Down

Switches/Routers Down Query

Switch/Router Port Operational Status Down Query

Select Top 1000000 
tblassets.AssetID,
tblassets.assetname,
tblSNMPInfo.IfIndex As [If],
tblSNMPInfo.IfDescription As Name,
Case
When tblSNMPInfo.IfOperstatus = 1 Then 'Up'
When tblSNMPInfo.IfOperstatus = 2 Then 'Down'
When tblSNMPInfo.IfOperstatus = 3 Then 'Testing'
When tblSNMPInfo.IfOperstatus = 4 Then 'Unknown'
When tblSNMPInfo.IfOperstatus = 5 Then 'Dormant'
When tblSNMPInfo.IfOperstatus = 6 Then 'NotPresent'
When tblSNMPInfo.IfOperstatus = 7 Then 'LowLayerDown'
Else 'Other'
End As ' Operational Status'
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
Left Outer Join tsysAssetTypes As tsysAssetTypes_1 On tblAssets1.Assettype =
tsysAssetTypes_1.AssetType
Where (tsysAssetTypes.AssetTypename = 'switch' Or tsysAssetTypes.AssetTypename =
'router') And tblSNMPIfTypes.IfTypename Like '%Ethernet%' and tblSNMPInfo.IfAdminstatus <> 1

Select Top 1000000 
tblassets.AssetID,
tblassets.assetname,
tblSNMPInfo.IfIndex As [If],
tblSNMPInfo.IfDescription As Name,
Case
When tblSNMPInfo.IfAdminstatus = 1 Then 'Up'
When tblSNMPInfo.IfAdminstatus = 2 Then 'Down'
When tblSNMPInfo.IfAdminstatus = 3 Then 'Testing'
Else 'Other'
End As 'Admin Status'
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
Left Outer Join tsysAssetTypes As tsysAssetTypes_1 On tblAssets1.Assettype =
tsysAssetTypes_1.AssetType
Where (tsysAssetTypes.AssetTypename = 'switch' Or tsysAssetTypes.AssetTypename =
'router') And tblSNMPIfTypes.IfTypename Like '%Ethernet%' and tblSNMPInfo.IfAdminstatus <> 1

Select Top 1000000 
tblassets.AssetID,
tblassets.AssetName,
tblassets.Lastseen,
tblassets.Lasttried
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where (tsysAssetTypes.AssetTypename = 'switch' Or tsysAssetTypes.AssetTypename =
'router') and (tblassets.Lastseen <> tblassets.Lasttried or tblassets.lastseen < Getdate() -1)