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

The first alert report indicated how many switches or routers are suspected to be down. This is calculated by checking which assets have not been scanned in the last 24h. Therefore scanning your switches/router once per day is required.

Switches/Routers Down Query

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)

The following alert report shows the number of operational status down ports.

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

The following alert report shows the number of admin status down ports.

Switch/Router Port Admin Status Down Query

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

Run This Report in 3 Easy Steps

1. Download & Install Lansweeper

3. Run the Report

Download Lansweeper to Run this Report

Harness the Power of Reporting