Top Errors and Discards This Hour (SWQL) add

https://thwack.solarwinds.com/t5/NPM-Documents/Top-Errors-and-Discards-This-Hour-SWQL/ta-p/516820

37 seconds running on my DB
1 second below

select top 10
SUBSTRING(n.caption,1,case when charindex('.',n.caption,1) <=4  then length(n.caption) else (charindex('.',n.caption,1)-1) end) AS [Node]
,n.DetailsUrl AS [_LinkFor_Node]
,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Node]
,i.caption as [Interface]
,i.DetailsUrl as [_LinkFor_Interface]
,'/Orion/images/StatusIcons/Small-' + i.StatusIcon AS [_IconFor_Interface]
,sum(i.InErrorsThisHour) as [RxErr]
,case when sum(i.InErrorsThisHour) > 0 then '/Orion/images/StatusIcons/Small-Critical.gif' end as [_iconfor_RxErr]
,concat('/Orion/Charts/CustomChart.aspx?ChartName=InterfaceErrors&NetObject=I:',i.InterfaceID,'&Period=PastHour&SampleSize=15') as [_LinkFor_RxErr]
,sum(i.InDiscardsThisHour) as [RxDis]
,case when sum(i.InDiscardsThisHour) > 0 then '/Orion/images/StatusIcons/Small-Critical.gif' end as [_iconfor_RxDis]
,concat('/Orion/Charts/CustomChart.aspx?ChartName=InterfaceDiscards&NetObject=I:',i.InterfaceID,'&Period=PastHour&SampleSize=15') as [_LinkFor_RxDis]
,sum(i.OutErrorsThisHour) as [TxErr]
,case when sum(i.OutErrorsThisHour) > 0 then '/Orion/images/StatusIcons/Small-Critical.gif' end as [_iconfor_TxErr]
,concat('/Orion/Charts/CustomChart.aspx?ChartName=InterfaceErrors&NetObject=I:',i.InterfaceID,'&Period=PastHour&SampleSize=15') as [_LinkFor_TxErr]
,sum(i.OutDiscardsThisHour) as [TxDis]
,case when sum(i.OutDiscardsThisHour) > 0 then '/Orion/images/StatusIcons/Small-Critical.gif' end as [_iconfor_TxDis]
,concat('/Orion/Charts/CustomChart.aspx?ChartName=InterfaceDiscards&NetObject=I:',i.InterfaceID,'&Period=PastHour&SampleSize=15') as [_LinkFor_TxDis]
from Orion.Nodes n
join Orion.NPM.Interfaces i on n.NodeID=i.NodeID

group by n.caption, n.DetailsUrl, n.StatusIcon, i.Caption, i.DetailsUrl, i.StatusIcon, i.InterfaceID
having (sum(i.InErrorsThisHour)+sum(i.InDiscardsThisHour)+sum(i.OutErrorsThisHour)+sum(i.OutDiscardsThisHour))>0
order by sum(i.InErrorsThisHour)+sum(i.InDiscardsThisHour)+sum(i.OutErrorsThisHour)+sum(i.OutDiscardsThisHour) desc

Find more posts tagged with

Accepted answers

All comments

mesverrum

An important distinction to make is that the query from @ciscoengineer will be showing interfaces with errors in the current hour, where your method shows interfaces with errors in the previous hour, because he is counting them from the error historical table basically as they happen, the "this hour" fields on the interfaces table only gets updated every hour after the hour is over. If you just want to get a general feel for which interfaces have errors overall then yours is fine, but if you wanted to put this on a noc for real time troubleshooting it would be misleading.