Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Down interface dashboard

Hi team,

I have created a dashboard for down interfaces using the below SQL query and the end results are showing as expected but i would like to add "DOWN TIME" like at what time the interface went down? Can someone help ?

select i.FullName as Interface, i.DetailsUrl as [_linkfor_Interface], '/Orion/images/StatusIcons/Small-' + StatusIcon as [_iconfor_Interface]

from orion.npm.Interfaces i

left join orion.AlertSuppression sup on (sup.entityuri = i.node.Uri and sup.SuppressFrom <= GETUTCDATE() and sup.SuppressUntil > GETUTCDATE()) or (sup.entityuri = i.Uri and sup.SuppressFrom <= GETUTCDATE() and sup.SuppressUntil > GETUTCDATE())

where i.status=2

and sup.EntityUri is null

Find more posts tagged with

Accepted answers

bobmarley

Or try this which is just adding LastChange from the table.

select i.FullName as Interface, i.DetailsUrl as [_linkfor_Interface], '/Orion/images/StatusIcons/Small-' + StatusIcon as [_iconfor_Interface], TOLOCAL (Lastchange) AS DownTime

from orion.npm.Interfaces i

left join orion.AlertSuppression sup on (sup.entityuri = i.node.Uri and sup.SuppressFrom <= GETUTCDATE() and sup.SuppressUntil > GETUTCDATE()) or (sup.entityuri = i.Uri and sup.SuppressFrom <= GETUTCDATE() and sup.SuppressUntil > GETUTCDATE())

where i.status=2

and sup.EntityUri is null

mharvey

I updated this SWQL to the below and it seems to run for my environment.

select i.FullName as Interface, i.DetailsUrl as [_linkfor_Interface], '/Orion/images/StatusIcons/Small-' + StatusIcon as [_iconfor_Interface], act.TriggeredDateTime
from orion.npm.Interfaces i

join orion.AlertObjects obj on i.Caption = obj.EntityCaption
join orion.AlertActive act on act.AlertObjectID = obj.AlertObjectID

where i.status=2

and sup.EntityUri is null

All comments

bobmarley

This is from an older Thwack post it may be a good start.

--https://thwack.solarwinds.com/products/network-performance-monitor-npm/f/forum/95022/report-for-interface-down-for-more-than-31-days

SELECT I.Caption as [Interface Name]
, I.Node.Caption as [Node Name]
, tolocal(I.LastChange) as [Last Change Time]
,I.Node.ObjectSubType AS PollingMethod
,I.Node.Status
,I.Node.ChildStatus
,I.Node.DetailsUrl
,I.Node.IPAddress
,I.Node.MachineType
FROM Orion.NPM.Interfaces I
WHERE I.Status = 2 --AND I.Node.Vendor ='Cisco'
--AND I.Node.Caption LIKE '%SITECODE%'
AND DAYDIFF(TOLOCAL(I.LastChange), GETDATE()) > 31
ORDER BY I.LastChange ASC

bobmarley

Or try this which is just adding LastChange from the table.

select i.FullName as Interface, i.DetailsUrl as [_linkfor_Interface], '/Orion/images/StatusIcons/Small-' + StatusIcon as [_iconfor_Interface], TOLOCAL (Lastchange) AS DownTime

from orion.npm.Interfaces i

left join orion.AlertSuppression sup on (sup.entityuri = i.node.Uri and sup.SuppressFrom <= GETUTCDATE() and sup.SuppressUntil > GETUTCDATE()) or (sup.entityuri = i.Uri and sup.SuppressFrom <= GETUTCDATE() and sup.SuppressUntil > GETUTCDATE())

where i.status=2

and sup.EntityUri is null

mharvey

I updated this SWQL to the below and it seems to run for my environment.

select i.FullName as Interface, i.DetailsUrl as [_linkfor_Interface], '/Orion/images/StatusIcons/Small-' + StatusIcon as [_iconfor_Interface], act.TriggeredDateTime
from orion.npm.Interfaces i

join orion.AlertObjects obj on i.Caption = obj.EntityCaption
join orion.AlertActive act on act.AlertObjectID = obj.AlertObjectID

where i.status=2

and sup.EntityUri is null

Naresh.Inala

Thank you mharvey it's working.

Naresh.Inala

Thank you bobmarley it's working.

Naresh.Inala

Hi All,

I would like to ask you one more question. is there a SQL for muted interfaces, example if someone has muted the alerts for Interfaces and i would like to know who has muted the alert for particular interface.

mharvey

This should work.

SELECT TOP 10
ADDHOUR(-5,n.ObservationTimestamp) as DateTIme,
n.AccountID,
n.DisplayName
FROM Orion.AuditingEvents n
where (n.accountID <> 'admin' AND n.AccountID NOT LIKE 'website%')
AND n.ObservationTimestamp like '%2024%'
and ActionTypeId = 57
Order by n.ObservationTimestamp DESC