cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

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? 

0 Kudos
6 Replies
Level 12

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.

 

0 Kudos

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

0 Kudos

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.

0 Kudos

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. 

0 Kudos

Make sure to check 'Allow HTML tags' for table columns that contain HTML code:

ioan_bucsa_0-1605992740712.png

 

0 Kudos

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

0 Kudos