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

Reply
  •  

     

    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

Children