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.

Custom Table showing Custom Property Current Downtime

Morning/Afternoon/Evening all,

I'm trying to get a table created that shows all my printers that are down, and how long they have been down. I've found quite a few downtime sql reports such as the one below but have been unable to get any of them to work in the current 2020 enviroment. Anyone have any suggestions on how to do this? 

I've tried scripts such as the following:

SELECT * from (

SELECT

StartTime.EventTime AS Down_Event_time,

(SELECT TOP 1

EventTime

FROM Events AS Endtime

WHERE EndTime.EventTime >= StartTime.EventTime

AND EndTime.EventType = 5

AND EndTime.NetObjectType = 'N'

AND EndTime.NetworkNode = StartTime.NetworkNode

AND EventTime IS NOT NULL

ORDER BY EndTime.EventTime) AS UpEventTime,

Nodes.Caption, StartTime.Message, DATEDIFF(Mi, StartTime.EventTime,(SELECT TOP 1 EventTime FROM Events AS Endtime

WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5 AND EndTime.NetObjectType = 'N'

AND EndTime.NetworkNode = StartTime.NetworkNode ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes

FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID

WHERE (StartTime.EventType = 1)

) as uptimetable

WHERE outageDurationInMinutes IS NOT NULL

ORDER BY eventtime desc

All I get is "Query not valid". The custom property I'm trying to use is "SAP_Printer = 'YES'. 

Anyone able to advise? 

  • We have a similar table showing nodes that have been down (or status = 'Warning') along with downtime and a few other details. The query I've used is:

    SELECT
    n.caption as Node
    ,CASE WHEN n.URI IN (SELECT EntityURI FROM Orion.AlertSuppression) THEN 'Y' ELSE 'N' END as IsMuted
    ,n.detailsurl as [_linkfor_Node]
    ,CONCAT('<img src="','/Orion/images/StatusIcons/Small-', StatusIcon,'">') AS [IconFor_Node]
    ,CONCAT('<div style="background-color:',(CASE REPLACE(StatusIcon,'.gif','') WHEN 'Warning' THEN '#FAC22A' WHEN 'Down' THEN '#D4303C' END ),';font-weight:bold;font-size:18px">',REPLACE(StatusIcon,'.gif',''),'</div>') AS Status
    ,round((minutediff(n.LASTSYSTEMUPTIMEPOLLUTC,getutcdate())/60.0),1) as [DownHours]
    , CASE REPLACE(StatusIcon,'.gif','') WHEN 'Down' THEN CONCAT(FLOOR((minutediff(n.LASTSYSTEMUPTIMEPOLLUTC,getutcdate())/60.0)/24),' d ', FLOOR(round((minutediff(n.LASTSYSTEMUPTIMEPOLLUTC,getutcdate())/60.0),1) - FLOOR((minutediff(n.LASTSYSTEMUPTIMEPOLLUTC,getutcdate())/60.0)/24)*24), ' h ', FLOOR((round((minutediff(n.LASTSYSTEMUPTIMEPOLLUTC,getutcdate())/60.0),1) - FLOOR((minutediff(n.LASTSYSTEMUPTIMEPOLLUTC,getutcdate())/60.0)))*60), ' m') WHEN 'Warning' THEN '---' END
    AS Since
    from orion.nodes n
    
    where [Conditions]
    ORDER By Status ASC,DownHours DESC

    There are also a few HTML tags in there for formatting (field backgrounds based on status, etc). 

    Hope this helps.

  • What do I replace with my printer custom property? n.URI?

  • Try this: 

    WHERE n.CustomProperties.SAP_Printer = 'YES'

    Also, I now notice that you query is SQL, whereas the one I suggested is SWQL, but that should not be a problem since Custom Table supports both.

  • So weird, in the preview it looks ok, except for the DHM showing up instead of the actual time. When I continue with it, it shows this below. Also I tried it just under custom query too and same result. Sorry I could be doing something completely wrong, I'm SQL illiterate, I just started down this path of trying out SQL custom scripts. 

    attachments.zip
  • Make sure to check 'Allow HTML tags' for table columns that contain HTML code:

    ioan_bucsa_0-1605992740712.png

  • Looking better, thank you so much for that however, my time still isn't working. Do I have to do something with the GMT offset? Also I'd like to limit this to ONLY down nodes, mine is showing all my SAP printers, any suggestions on that? I really appreciate all the help!

    2020-11-23 08_17_16-SAP Printer Down Time.png