Version 1

    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