Need help to insert " AddHour +5 " in Uptime result

Hi 

Need help to insert " AddHour +5 " in Uptime result

SELECT Nodes.Caption, Nodes.IPAddress, AddHour(+5,StartTime.EventTime) AS DownTime , nodes.status,AddHour(+5,LastBoot) AS LastBoot , ncp.ATM_Branches ,
(
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
INNER JOIN Orion.NodesCustomProperties as ncp ON ncp.NodeID = Nodes.NodeID


WHERE (StartTime.EventType = 1) AND ncp.ATM_Branches = 'ATM Branches' and Nodes.IPAddress = '10.30.115.1'

--AND StartTime.EventTime >= GETDATE() - 1 --For the Last 24 hours, not just since midnight.
AND StartTime.EventTime > DATETRUNC('day', GETDATE())
--AND StartTime.EventTime >'2022-02-25'
-- AND (StartTime.EventTime BETWEEN DATETRUNC('day', GETDATE()) AND DATETRUNC('day', ADDDAY(1, GETDATE())))
ORDER BY StartTime.EventTime desc

Parents
  • , if the goal is to convert the returned time result to a local timezone you can also just use the tolocal function in SWQL. 

  • issue is that swql giving me node up/down time which is lesser than 5 hours from down/up event actual time.

  • , is that timestamp 5 hours off even when using the tolocal function?

  •  

     

    let me share complete script with result , need to  add +5 hours in Uptime ( down time and last boot now showing correct time )

    SELECT Nodes.Caption, Nodes.IPAddress, tostring(tolocal(StartTime.EventTime)) AS DownTime , nodes.status,tostring(tolocal(LastBoot)) AS LastBoot , ncp.ATM_Branches ,


    (
    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
    INNER JOIN Orion.NodesCustomProperties as ncp ON ncp.NodeID = Nodes.NodeID


    WHERE (StartTime.EventType = 1) AND ncp.ATM_Branches = 'ATM Branches'

    --AND StartTime.EventTime >= GETDATE() - 1 --For the Last 24 hours, not just since midnight.
    AND StartTime.EventTime > DATETRUNC('day', GETDATE())
    --AND StartTime.EventTime >'2022-02-25'
    -- AND (StartTime.EventTime BETWEEN DATETRUNC('day', GETDATE()) AND DATETRUNC('day', ADDDAY(1, GETDATE())))
    ORDER BY StartTime.EventTime desc

    Uptime Colum also need to add +5 hours

  • , when you use the tolocal function is the time still 5 hours off? I'm asking because using the tolocal function is much easier than trying to add hours to the selection. If the end result is to take it from what is saved in the DB which is UTC to whatever timezone you are in the tolocal cuntion is the way to go imo.

  • when you use the tolocal function is the time still 5 hours off >  its resolve timime issue only on Downtime and lastboot 

    how to add tolocal on Uptime ?

    (
    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

  • try this

    (
    SELECT TOP 1 tostring(tolocal(EndTime.EventTime))
    FROM Orion.Events AS Endtime
    WHERE tolocal(EndTime.EventTime) > tolocal(StartTime.EventTime)
    AND EndTime.EventType = 5
    AND EndTime.NetObjectType = 'N'
    AND EndTime.NetworkNode = StartTime.NetworkNode
    ORDER BY tolocal(EndTime.EventTime)
    ) AS UpTime

Reply Children