This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Report for node down longer than 14 days

I have looked for multiple days...

even watched every youtube video i can find, but i can't find a swql query that will show me all node down for over 14 days...  due to the fact that we have devices that are not servers, it should work for 'IS SERVER=YES' or anyway to make sure I am just looking at down servers...

any help is appreciated...  I have also tried to create a custom report, but can only get the total duration in minutes to be 100805, and i cant change that number...

Parents
  • Here is a SWQL statement I picked up somewhere. I originally had it set for 60 minutes, but I believe it is set to a week now.--


    select n.nodeid, 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],GETUTCDATE())),99999) as Minutes


    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, and where I can only get nodes that are still down.
    where (n.status = 2 AND t2.[Up Event] IS NULL)


    -- If you want to filter the results to only show outages of a minimum duration uncomment the below line, this is set to a week.
    and MINUTEDIFF(isnull(t2.[Down Event],(GETUTCDATE()-30)), isnull(t2.[Up Event],GETUTCDATE())) > 10080


    -- 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

  • Oh, but you wanted 2 weeks, just change 10080 to 20160 near the end of the statement. And to get only IsServer then change the last where line and add--  AND n.isserver = TRUE 

    select n.nodeid, 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],GETUTCDATE())),99999) as Minutes


    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, and where I can only get nodes that are still down.
    where (n.status = 2 AND t2.[Up Event] IS NULL AND n.IsServer=TRUE)


    -- If you want to filter the results to only show outages of a minimum duration uncomment the below line, this is set to a week.
    and MINUTEDIFF(isnull(t2.[Down Event],(GETUTCDATE()-30)), isnull(t2.[Up Event],GETUTCDATE())) > 20160


    -- 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

  • I appreciate your response!  

    I had found this one online as well...  It works great on a dashboard as a custom query...  but i could not get it to work as a report!!   In SWQL, it works great as well...but when in put it in a report and then 'preview' the report it shows no data...then when the report is finished, there is no data..

  • I have it all updated and will let you know how it goes... Thanks again for your assistance...

  • Thanks again for your assistance... however, it initially did not work...   I actually had to take the n.IsServer out of the parenthesis and place it below that line... I also commented out the graphics...  below is what worked for me...

    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
    
    
    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
    AND n.IsServer = TRUE
    
    -- 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)
    
    
    -- 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())) >  20160
    
    
    -- 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

    I then went into the report properties and applied my link there.  

    Thanks!

  • Question, 

    How could I group by 'Owner'?

    and ignore 'BioMed' devices...

    I have tried 

    AND n.Model <> 'BioMed'

    but i get an error that Model is not part of orion.nodes

Reply Children