SWQL query showing node down/up time with 5 hours difference

makecrazy

Hi 

Need help to insert " AddHour +5 " in Uptime result and downtime as mentioned query providing Dow/Uptime with 5 hours diff of my local time

SWQL output 

but actual Dow/Up time is 

SELECT Nodes.Caption, Nodes.IPAddress,Nodes.CustomProperties.SP1,Nodes.CustomProperties.SP2,Nodes.CustomProperties.SP3, Nodes.CustomProperties.regions, nodes.status, StartTime.EventTime AS DownTime ,
(
SELECT TOP 1 EndTime.EventTime
FROM Orion.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 UpTime,
MINUTEDIFF(StartTime.EventTime,(
SELECT TOP 1 EventTime FROM Orion.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 MinutesDown
FROM Orion.Events AS StartTime
INNER JOIN Orion.Nodes ON StartTime.NetworkNode = Nodes.NodeID
WHERE (StartTime.EventType = 1)
and nodes.status = 1
and ( Nodes.CustomProperties.SP1 = 'Mobilink' or Nodes.CustomProperties.SP2 = 'Mobilink' or Nodes.CustomProperties.SP3 = 'Mobilink')
AND (StartTime.EventTime BETWEEN DATETRUNC('WEEK', GETDATE())
AND DATETRUNC('day', ADDWEEK(1, GETDATE())))
ORDER BY StartTime.EventTime desc

sum_giais

The dates in the database are UTC. To convert to local time use the TOLOCAL() function like so:

TOLOCAL(EndTime.EventTime) AS [EventTime]