InterfaceDowntimeV1.swql

micheal100

Simple query that shows downtime for interfaces this month, with information such as Bandwidth and a custom interface property also included. Status information based on values courtesy of sean.martinez​ post Orion Platform SWQL, Thwack! Now I got it.

SELECT N.CAption as Device, I.Caption as Interface, INOD.DateTimeFrom, INOD.DateTimeUntilNow,

INOD.State, INOD.TotalDurationMin, I.CustomProperties.Comments, I.InterfaceSpeed as Bandwidth

FROM Orion.NPM.InterfaceNetObjectDowntime INOD left join Orion.NPM.Interfaces I on INOD.EntityID = I.InterfaceID left join Orion.nodes N on I.NodeID = N.Nodeid

where INOD.entitytype = 'Orion.NPM.Interfaces'

and INOD.State in (2,3,4,5,6,7,8,9,10)

and month(INOD.DateTimeFrom) = MONTH(GETUTCDATE()) and year(INOD.DateTimeFrom) = year(GETUTCDATE())

order by Device, Interface, Inod.DateTimeFrom

InterfaceDowntimeV1.swql

kpmarcin

Hello Michael,

This is a great query. Just what I was looking for.

I have made some of my own modifications. If someone is interested, I am pasting it below. Search option is commented inside the query.

--------------------------

SELECT

    N.Caption as Node

    ,N.detailsurl as [_linkfor_Node]

    ,I.Caption as Interface

    ,I.detailsurl as [_linkfor_Interface]

    ,INOD.DateTimeFrom AS [From]

    ,INOD.DateTimeUntilNow AS [Till]

    ,INOD.State

    ,TOSTRING(INOD.TotalDurationMin) + ' min' AS [Total]

    ,CASE

        when INOD.State = '2' then ('/Orion/images/StatusIcons/Small-Down.gif')

        when INOD.State = '3' then ('/Orion/images/StatusIcons/Small-Warning.gif')

        when INOD.State = '4' then ('/Orion/images/StatusIcons/Small-Shutdown.gif')

        when INOD.State = '5' then ('/Orion/images/StatusIcons/Small-testing.gif')

        when INOD.State = '6' then ('/Orion/images/StatusIcons/Small-unknown.gif')

        when INOD.State = '7' then ('/Orion/images/StatusIcons/Small-unknown.gif')

        when INOD.State = '8' then ('/Orion/images/StatusIcons/Small-unknown.gif')

        when INOD.State = '9' then ('/Orion/images/StatusIcons/Small-Unmanaged.gif')

        when INOD.State = '10' then ('/Orion/images/StatusIcons/Small-unplugged.gif')

        else ('/Orion/images/StatusIcons/Small-unknown.gif')

    end as [_IconFor_State]

FROM Orion.NPM.InterfaceNetObjectDowntime INOD

left join Orion.NPM.Interfaces I on INOD.EntityID = I.InterfaceID

left join Orion.nodes N on I.NodeID = N.Nodeid

where INOD.entitytype = 'Orion.NPM.Interfaces'

and INOD.State in (2,3,4,5,6,7,8,9,10)

and month(INOD.DateTimeFrom) = MONTH(GETDATE()) and year(INOD.DateTimeFrom) = year(GETDATE())

--and (N.Caption like '%${SEARCH_STRING}%' OR I.Caption like '%${SEARCH_STRING}%' OR INOD.DateTimeFrom like '%${SEARCH_STRING}%' OR INOD.DateTimeUntilNow like '%${SEARCH_STRING}%')

order by Inod.DateTimeFrom DESC

--------------------------

Looks good

Kind regards,

Marcin Kazmierczak.

---

IT-Indago Ltd. - Authorized Reseller & SolarWinds Certified SCP Professional

IT-Indago – let's monitor!  |  Follow us on Facebook & LinkedIn