Assistance with creating a downtime report

I need some assistance creating a report to track downtime for specific nodes each month. I have attempted to build this on my own, but cant get the table options I need to work correctly.

I need a report for specific nodes that went down last month and their duration of downtime for each event (not total downtime). I also need the to only report outages > 10 minutes if possible. I would like the table to include Date and or time | node | IP address | and duration of that events downtime.

I have no experience writing SQL or SWQL so I am lost at this point. Can anyone help me out with this?

Find more posts tagged with

sql query

report

Accepted answers

stevenstadel

Added the Node's IP and a method to query only for the last month. How do you identify the nodes you want specifically report on? One way would be to use a Custom Property. I've added a comment in the where clause on how you can filter nodes on that. (-- AND n.CustomProperties.Environment LIKE 'Production' -- Filter on Custom Property)

SELECT 
    n.caption AS [Device], -- shows the current status icon
    n.IP_Address,
    '/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Device], -- makes a clickable link to the node details
    n.DetailsUrl AS [_linkfor_Device], -- shows the timestamp of the down event, if there is no timestamp then is says the event was greater than the number of days in your event retention settings
    ISNULL(
        TOSTRING(t2.[Down Event]),
        CONCAT(
            'Greater than ',
            (
                SELECT CurrentValue 
                FROM Orion.Settings 
                WHERE settingid='SWNetPerfMon-Settings-Retain Events'
            ),
            ' days ago'
        )
    ) AS [Down Event], -- shows the timestamp of the up event, unless the object is still down
    ISNULL(TOSTRING(t2.[Up Event]), 'Still Down') AS [Up Event], -- figures out the minutes between the down and up events, if the object is still down it counts from the down event to now, displays 99999 if we cannot accurately determine the original downtime, and 
    ISNULL(MINUTEDIFF(t2.[Down Event], ISNULL(t2.[Up Event], GETDATE())), 99999) AS Minutes
FROM 
    orion.nodes n
LEFT JOIN (
    SELECT    
        StartTime.Nodes.NodeID, -- Device nodeid used for our join   
        ToLocal(StartTime.EventTime) AS [Down Event], -- Down Event time stamp in local time zone    
        (
            SELECT TOP 1    
                ToLocal(EventTime) AS [EventTime]    
            FROM 
                Orion.Events AS [EndTime]    
            WHERE 
                EndTime.EventTime >= StartTime.EventTime AND -- picks the first up event that is newer than the down event for this node
                EndTime.EventType = 5 AND -- EventType 5 is a node up 
                EndTime.NetObjectID = StartTime.NetObjectID AND
                EventTime IS NOT NULL 
            ORDER BY 
                EndTime.EventTime    
        ) AS [Up Event] -- Up Event time stamp in local time zone    
    FROM 
        Orion.Events StartTime -- This is the table we are querying    
    WHERE 
        StartTime.EventType = 1 AND -- EventType 1 is a node down
        StartTime.EventTime >= ADDDAY(-DAY(GETUTCDATE()), ADDMONTH(-1, GETUTCDATE())) AND -- First day of last month
        StartTime.EventTime < ADDDAY(-DAY(GETUTCDATE()), GETUTCDATE()) -- First day of this month
) t2 ON n.NodeID = t2.nodeid
WHERE 
    (n.status = 2 OR t2.nodeid IS NOT NULL) -- this is how I catch nodes that are down but have aged out of the events table
--    AND n.CustomProperties.Environment LIKE 'Production' -- Filter on Custom Property
ORDER BY 
    t2.[down event] DESC

All comments

stevenstadel

This is a SWQL query that I use in a custom table widget. You can play with it and use it in a report as well. Not mine but I don't recall exactly who created it. It uses the down event to calculate how long an node was down.

select n.caption as [Device]
-- shows the current status icon
, '/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Device]
-- makes a clickable link to the node details
, n.DetailsUrl as [_linkfor_Device]
-- shows the timestamp of the down event, if there is no timestamp then is says the event was greater than the number of days in your event retention settings
, isnull(tostring(t2.[Down Event]),concat('Greater than ',(SELECT CurrentValue FROM Orion.Settings where settingid='SWNetPerfMon-Settings-Retain Events'),' days ago')) as [Down Event]
-- shows the timestamp of the up event, unless the object is still down
, isnull(tostring(t2.[Up Event]),'Still Down') as [Up Event]
-- figures out the minutes between the down and up events, if the object is still down it counts from the down event to now, displays 99999 if we cannot accurately determine the original downtime, and 
, isnull(MINUTEDIFF(t2.[Down Event], isnull(t2.[Up Event],GETDATE())),99999) as Minutes
--, n.vendor

from orion.nodes n
left join (SELECT    
 -- Device nodeid used for our join   
 StartTime.Nodes.NodeID     

 -- Down Event time stamp in local time zone    
 ,ToLocal(StartTime.EventTime) AS [Down Event]    

 -- Up Event time stamp in local time zone    
 ,(SELECT TOP 1    
 ToLocal(EventTime) AS [EventTime]    
 FROM Orion.Events AS [EndTime]    
-- picks the first up event that is newer than the down event for this node
 WHERE EndTime.EventTime >= StartTime.EventTime   
-- EventType 5 is a node up 
 AND EndTime.EventType = 5    
 AND EndTime.NetObjectID = StartTime.NetObjectID    
 AND EventTime IS NOT NULL 

 ORDER BY EndTime.EventTime    
 ) AS [Up Event]    

-- This is the table we are querying    
FROM Orion.Events StartTime    

-- EventType 1 is a node down
WHERE StartTime.EventType = 1    

) t2 on n.NodeID = t2.nodeid

-- this is how I catch nodes that are down but have aged out of the events table
where (n.status = 2 or t2.nodeid is not null)
-- Filter on Vendor
--and n.vendor like '%Palo%'
-- Filter on Node Name
--and n.Caption like '%switch%'
-- If you want to filter the results to only show outages of a minimum duration uncomment the below line
--and MINUTEDIFF(isnull(t2.[Down Event],(GETUTCDATE()-30)), isnull(t2.[Up Event],GETUTCDATE())) >  60

-- if you want to use this query in a search box of the Custom Query resource uncomment the below line
--and n.Caption like '%${SEARCH_STRING}%'

order by t2.[down event] desc

pjd1213

Thanks for this. This is much closer to what I am actually looking for. This includes all of our nodes, however I am looking to be able to only include specific nodes. This is a great start for me though as it is closer than anything I was able to create to what we need. Thank you.

pjd1213

After using the template @stevenstadel provided, I was able to edit the query to only show nodes down for over 10 minutes like I need. However I still need some assistance with the following:

Add a column for node IP address.
Only show nodes that went down last month.
Only show specific nodes (if possible).

Can anyone else look at the query and add any suggestions to achieve these results?

select n.caption as [Device]
-- shows the current status icon
, '/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Device]
-- makes a clickable link to the node details
, n.DetailsUrl as [_linkfor_Device]
-- shows the timestamp of the down event, if there is no timestamp then is says the event was greater than the number of days in your event retention settings
, isnull(tostring(t2.[Down Event]),concat('Greater than ',(SELECT CurrentValue FROM Orion.Settings where settingid='SWNetPerfMon-Settings-Retain Events'),' days ago')) as [Down Event]
-- shows the timestamp of the up event, unless the object is still down
, isnull(tostring(t2.[Up Event]),'Still Down') as [Up Event]
-- figures out the minutes between the down and up events, if the object is still down it counts from the down event to now, displays 99999 if we cannot accurately determine the original downtime, and 
, isnull(MINUTEDIFF(t2.[Down Event], isnull(t2.[Up Event],GETDATE())),99999) as Minutes
--, n.vendor

from orion.nodes n
left join (SELECT    
 -- Device nodeid used for our join   
 StartTime.Nodes.NodeID     

 -- Down Event time stamp in local time zone    
 ,ToLocal(StartTime.EventTime) AS [Down Event]    

 -- Up Event time stamp in local time zone    
 ,(SELECT TOP 1    
 ToLocal(EventTime) AS [EventTime]    
 FROM Orion.Events AS [EndTime]    
-- picks the first up event that is newer than the down event for this node
 WHERE EndTime.EventTime >= StartTime.EventTime   
-- EventType 5 is a node up 
 AND EndTime.EventType = 5    
 AND EndTime.NetObjectID = StartTime.NetObjectID    
 AND EventTime IS NOT NULL 

 ORDER BY EndTime.EventTime    
 ) AS [Up Event]    

-- This is the table we are querying    
FROM Orion.Events StartTime    

-- EventType 1 is a node down
WHERE StartTime.EventType = 1    

) t2 on n.NodeID = t2.nodeid

-- this is how I catch nodes that are down but have aged out of the events table
where (n.status = 2 or t2.nodeid is not null)
-- Filter on Vendor
--and n.vendor like '%Palo%'
-- Filter on Node Name
--and n.Caption like '%switch%'
-- If you want to filter the results to only show outages of a minimum duration uncomment the below line
and MINUTEDIFF(isnull(t2.[Down Event],(GETUTCDATE()-30)), isnull(t2.[Up Event],GETUTCDATE())) >  10


-- if you want to use this query in a search box of the Custom Query resource uncomment the below line
--and n.Caption like '%${SEARCH_STRING}%'

order by t2.[down event] desc

stevenstadel

SELECT 
    n.caption AS [Device], -- shows the current status icon
    n.IP_Address,
    '/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Device], -- makes a clickable link to the node details
    n.DetailsUrl AS [_linkfor_Device], -- shows the timestamp of the down event, if there is no timestamp then is says the event was greater than the number of days in your event retention settings
    ISNULL(
        TOSTRING(t2.[Down Event]),
        CONCAT(
            'Greater than ',
            (
                SELECT CurrentValue 
                FROM Orion.Settings 
                WHERE settingid='SWNetPerfMon-Settings-Retain Events'
            ),
            ' days ago'
        )
    ) AS [Down Event], -- shows the timestamp of the up event, unless the object is still down
    ISNULL(TOSTRING(t2.[Up Event]), 'Still Down') AS [Up Event], -- figures out the minutes between the down and up events, if the object is still down it counts from the down event to now, displays 99999 if we cannot accurately determine the original downtime, and 
    ISNULL(MINUTEDIFF(t2.[Down Event], ISNULL(t2.[Up Event], GETDATE())), 99999) AS Minutes
FROM 
    orion.nodes n
LEFT JOIN (
    SELECT    
        StartTime.Nodes.NodeID, -- Device nodeid used for our join   
        ToLocal(StartTime.EventTime) AS [Down Event], -- Down Event time stamp in local time zone    
        (
            SELECT TOP 1    
                ToLocal(EventTime) AS [EventTime]    
            FROM 
                Orion.Events AS [EndTime]    
            WHERE 
                EndTime.EventTime >= StartTime.EventTime AND -- picks the first up event that is newer than the down event for this node
                EndTime.EventType = 5 AND -- EventType 5 is a node up 
                EndTime.NetObjectID = StartTime.NetObjectID AND
                EventTime IS NOT NULL 
            ORDER BY 
                EndTime.EventTime    
        ) AS [Up Event] -- Up Event time stamp in local time zone    
    FROM 
        Orion.Events StartTime -- This is the table we are querying    
    WHERE 
        StartTime.EventType = 1 AND -- EventType 1 is a node down
        StartTime.EventTime >= ADDDAY(-DAY(GETUTCDATE()), ADDMONTH(-1, GETUTCDATE())) AND -- First day of last month
        StartTime.EventTime < ADDDAY(-DAY(GETUTCDATE()), GETUTCDATE()) -- First day of this month
) t2 ON n.NodeID = t2.nodeid
WHERE 
    (n.status = 2 OR t2.nodeid IS NOT NULL) -- this is how I catch nodes that are down but have aged out of the events table
--    AND n.CustomProperties.Environment LIKE 'Production' -- Filter on Custom Property
ORDER BY 
    t2.[down event] DESC

pjd1213

Thanks for the help. I return to the office tomorrow and I will test this out. Usually, is select nodes in the setup of the report, but I’m not sure how that would be performed in SWQL. I will review your suggestions tomorrow and let you know how it goes. Thanks again for your help.

pjd1213

This worked great. I added the following line to add specific nodes.

and n.Caption IN ('NodeName1', 'NodeName2', 'NodeName3') -- Replace with the actual node names you want to include

I really appreciate your help!