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

  • , my bad, forgot to alias out the selection in that. Try this

    (
    SELECT TOP 1 tostring(tolocal(EndTime.EventTime)) AS UpTime
    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

  • , so I don't have an instance I can mess around and test a query. so what I have provided will conceptually work, you just need to fine-tune the syntax errors I have given you. Normally I would work that out before I gave it to you. I think the issue is with the order by in that subquery, but without an instance to confirm it's just a feeling.

  • Can you work out which line is causing the issue by adding and removing the  -  - 

  • Dear 

    not expert in scripting that's why need help on adding +5 Hours in Node 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,

  • Let's take a step backward:

    What are you trying to get this set of data to represent?  How do you want it represented?  What data/fields do you want returned? For what timeframe?

  • Main issue we are facing is that different  SWQL Quires are showing Data ( Time period ) which is always - 5 Hours of  current time.

    Because of this .reason i try to add tostring(tolocal  which provide correct time period for me.

    In mentioned script we are trying to know the reason whether nodes ( Routers )  are getting Down in Power or having Media issue.

    for e.g.

    ALPHA Router get down on NMS at 6 AM and get UP on 8 AM , after getting UP on NMS at 8 AM Router LAST Boot value shows that Router rebooted at 7:50 AM by which we can consider that Router was Down since 6 AM due to Power issue.

    In other scenario ALPHA Router get down at 6 AM and get UP at 8 AM but its Last Boot is showing yesterday date on NMS by which we can consider that Today’s Router is not rebooted and its was Down due to Media issue for 2 Hours.

    In mentioned script we added tostring(tolocal by which we are getting correct time period of  StartTime.EventTime and LastBoot.

     tostring(tolocal(StartTime.EventTime)) AS DownTime , nodes.status,tostring(tolocal(LastBoot)) AS LastBoot

    need to  add +5 hours in Uptime also in mentioned SWQL Script.

     

    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 > DATETRUNC('day', GETDATE())

    ORDER BY StartTime.EventTime desc