cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Interactive Node Outage tracker

First off thank you to wluther​ and his posts that led me in the right direction to create this, as well as mesverrum​ for his query on node outages.

This is a custom HTML resource that uses SWQL queries, Javascript and Google charts to create interactive charts that allow for great customization.

Here is a screenshot of the results:

pastedImage_0.png

This resource starts with just the calendar that shows a count of Branch Outages and color codes them depending on the amount of outages that day.

Once you click on a day a Timeline will appear that breaks down all outages that started on that day.

The code provided can easily be changed to your needs if you are familiar in SWQL. There are 2 main SWQL queries that are running here and I have formatted them to be read easily as in the HTML resource. It is condensed to one line, and I have added comments to help explain each line (needs to be removed when condensing to one line for HTML).

SWQL Query for Count of Branch Outages per day: (line 5)

SELECT 
    SUBSTRING(TOSTRING(DATETRUNC('Day',ToLocal(E.EventTime))),0,12) AS Day, --Format Ex. Apr 19 2019
    Count(*) as [Qty] --Count of Branch Outages
FROM
    Orion.Events E --Table we are querying
WHERE
    Year(TOLOCAL(E.EventTime)) = Year(GETDATE()) --Only get events from the current year
    AND E.EventType = 1 --Get Node Down Events
    AND E.NetObjectType = 'N' --Only Nodes
    AND E.Nodes.CustomProperties._DeviceType = 'Branch Router' --Custom Properties assigned to Branch Routers
    AND E.Nodes.Status <> 9 --Don't grab devices currently unmanaged
GROUP BY
    DATETRUNC('Day',ToLocal(E.EventTime))
ORDER BY
    DATETRUNC('Day',ToLocal(E.EventTime)) DESC --Order by newest to oldest

SWQL Query for Timeline Events: (line 56)

SELECT 
    N.Caption AS [Device], --Get Device Name
    ToLocal(t2.[Down Event]) as [Down Event], --Get Down Event
    ISNULL(ToLocal(t2.[Up Event]), GETDATE()) as [Up Event] --Get Up Event or use current time if not up yet
FROM
    Orion.Nodes N --Table we are querying
LEFT JOIN ( --Left Join a sub query for start and end times
    SELECT
        StartTime.Nodes.NodeID, --NodeID to match node with event
        StartTime.EventTime AS [Down Event], --Down event
        (SELECT TOP 1 --Subquery to grab only the next up event after going down
            EventTime AS [EventTime] --Up event
         FROM
            Orion.Events AS [EndTime]
         WHERE
            EndTime.EventTime >= StartTime.EventTime --only events after the down event
            AND EndTime.EventType = 5 --grab an up event
            AND EndTime.NetObjectID = StartTime.NetObjectID --match nodeIDs
            AND EventTime IS NOT NULL
         ORDER BY
            EndTime.EventTime --Order from latest to most current which allows it to get the next up event after going down
        ) AS [Up Event]
    FROM
        Orion.Events StartTime
    WHERE StartTime.EventType = 1 --Get down events
) T2 on N.NodeID = T2.NodeId --Match events
WHERE
    YEAR(TOLOCAL(T2.[Down Event])) = YEAR(GETDATE()) --Match year
    AND MONTH(TOLOCAL(T2.[Down Event])) = " +querymonth + " --Match month with Javascript var querymonth
    AND DAY(TOLOCAL(T2.[Down Event])) = " +querydate+" --Match day with Javascript var queryday
    AND N.CustomProperties._DeviceType = 'Branch Router' --Custom properties assigned to Branch Routers
    AND T2.[Down Event] IS NOT NULL --No null down events

Other Posts that helped with making this:

Using Your Custom HTML Resource To Properly Display SWQL Query Results

Using Your Custom HTML Resource To View Events On A Timeline

Node Downtime with Duration and Minimum Length Filtering

If there are any questions or improvements I could make please let me know!

Thank you,

-Joshua

Labels (3)
Attachments
Comments

This is an excellent example of what you can do with this type of widget, jhaas​! I'll definitely be borrowing this for a couple of pages in our environment.

Thank you for sharing.

-Will

This is amazing. All I had to do was modify the custom property title and it looks great. What is the deal with days that have zero events, though? They give me:

pastedImage_0.png

If there was 0 outages on that day then the swql query does not return anything for that date. Due to that it is not a part of the array and when you click on that day it will return an out of bounds error for the array. At least that is my understanding of that error. I didn't include days with 0 events due to not getting any more useful info if I already know nothing went down that day and the error did not mess up anything else on the charts.

Great stuff, Love it!

I've added one line of code in my implementation to avoid that nasty 'invalid row index undefined'

Just add:

if (selectedItem.row === undefined) {return;}

Between lines 46 and 47

if (selectedItem) {

var dateindex=["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]

End result should look like this:

if (selectedItem) {

if (selectedItem.row === undefined) {return;}

var dateindex=["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]

Basically if the row is undefined just do nothing (exit the function)

Wow, such a simple solution. Thank you for sharing this, antonis.athanasiou​!

Version history
Revision #:
1 of 1
Last update:
‎04-19-2019 08:44 AM
Updated by: