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

Time formatting assistance on SWQL Query - Down Nodes with Down TIme

Jump to solution

 

When I run this query is displays the downtime five in the future of my current time, which happens to coincide with GMT so I think the

database table is in GMT. Could anyone assist with the code to transform this the EST ? 

SELECT
NodeName AS [Node Name],
'/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Node Name],
DetailsUrl AS [_LinkFor_Node Name],
MAX(DOWNEVENT.EVENTTIME) AS [DOWN TIME]
FROM ORION.NODES NODES
INNER JOIN ORION.EVENTS DOWNEVENT
ON NODES.NODEID = DOWNEVENT.NETWORKNODE
WHERE STATUS = 2 
GROUP BY NodeName, StatusIcon, DetailsUrl
ORDER BY NodeName

1 Solution

@bobmarley you should be change your select for DOWN TIME to below and it will convert the time to your local time. 

MAX(tolocal(DOWNEVENT.EVENTTIME)) AS [DOWN TIME]

View solution in original post

2 Replies

@bobmarley you should be change your select for DOWN TIME to below and it will convert the time to your local time. 

MAX(tolocal(DOWNEVENT.EVENTTIME)) AS [DOWN TIME]

View solution in original post

@christopher.t.jones123 

Thanks! That worked great! If anyone else wants to use this the code is below. The normal Down Nodes resource on the main page doesn't show the time the node went down so you can make a custom widget to replace that view with this one. 

SELECT
NodeName AS [Node Name],
'/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Node Name],
DetailsUrl AS [_LinkFor_Node Name],
--MAX(DOWNEVENT.EVENTTIME) AS [DOWN TIME] --this shows GMT
MAX(tolocal(DOWNEVENT.EVENTTIME)) AS [DOWN TIME]
FROM ORION.NODES NODES
INNER JOIN ORION.EVENTS DOWNEVENT
ON NODES.NODEID = DOWNEVENT.NETWORKNODE
WHERE STATUS = 2
GROUP BY NodeName, StatusIcon, DetailsUrl, Nodes.CustomProperties._Group
ORDER BY NodeName