All Interfaces Down by Days Report

jimmy_mccubbins

I am trying to find a way to create a Report that shows all interfaces that are down in SolarWinds by days regardless of priority.

I did try to use SW Report Builder but it kept giving me false information based on what I was asking it.

I have not created this query from scratch, I have found a query already being used and modified it where I can get some info out of it.

My issue is that I can look in SolarWinds and see that there are more interfaces that are down that what the query is providing me.

Any assistance would be great.  I'm new to SQL and databases so I'm slugging along here.

_________________________________

Here is where I started:

select  n.StatusLED as Status

,n.Caption as NodeName

,i.StatusLED as 'Interface Status '

,i.caption as 'Interface Name',

dateadd(hour,-5,a.triggertimestamp)  as 'Went Down'

,n.NodeID

from alertstatusview a join interfaces i on i.Interfaceid = a.activeobject

                       join nodes n on i.NodeID = n.NodeID

where acknowledged = '0'

AND i.i10_INTERFACE_PRIORITY like '1%'

AND i.StatusLED = 'Down.gif'

AND A.AlertDefID =  '5D326083-B03A-4D8D-A7DE-D3F9AC50618D'

order by Triggertimestamp DESC

(I have been looking to see where A.AlertDefID =  '5D326083-B03A-4D8D-A7DE-D3F9AC50618D' came from, and I have no idea where they got that defid in the database.)

_______________________________________________

Here is where I am now:

select  n.StatusLED as Status

,n.Caption as NodeName

,i.StatusLED as 'Interface Status'

,i.caption as 'Interface Name',

dateadd(hour,-5,a.triggertimestamp)  as 'Went Down',

datediff(day,a.triggertimestamp,GETDATE()) as 'Days Down'

from alertstatusview a join interfaces i on i.Interfaceid = a.activeobject

join nodes n on i.NodeID = n.NodeID

WHERE n.StatusLED = 'Up.gif'

AND i.StatusLED <> 'Up.gif'

order by 'Days Down' DESC

Thanks in advance.