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:

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