SWQL to SQL

gadbekr

Is there any SQL wizards out there that can convert the below script from SWQL to SQL.  I have found the timestamps in SWQL are in UTC and there for makes the report look like the uptime is a time in the future in CST terms.  That raises questions from management and instead of trying to explain the UTC and CST difference, If I can somehow convert this to SQL (Which I am not familiar with) then I can just create the report with the accurate CST timestamps, and viola....everyone is happy.

SELECT
T.[Node name]
,T.[Start of Downtime]
,T.[End of Downtime]
,T.[Duration]
FROM (

SELECT
N.Caption AS [Node Name],
E.EventTime AS [Start of Downtime],
(SELECT TOP 1
EventTime
FROM Orion.Events AS EndEvent
WHERE
EndEvent.NetworkNode = E.NetworkNode
AND EndEvent.EventTime > E.EventTime
AND EndEvent.EventType = 5) AS [End of Downtime]
,MINUTEDIFF(E.EventTime,
(SELECT TOP 1
EventTime
FROM Orion.Events AS EndEvent
WHERE
EndEvent.NetworkNode = E.NetworkNode
AND EndEvent.EventTime > E.EventTime
AND EndEvent.EventType = 5)
) AS [Duration]
FROM Orion.Events AS E
INNER JOIN Orion.Nodes AS N ON E.NetworkNode = N.NodeID
WHERE
E.EventType = 1
AND E.EventTime>ADDDAY(-7,GETDATE())
AND N.Caption LIKE '%imbox%'
ORDER BY
E.EventTime DESC
) AS T
WHERE
T.[Duration]>60

bobmarley

Give this a try  

SELECT
    T.[Node Name],
    T.[Start of Downtime],
    T.[End of Downtime],
    T.[Duration]
FROM (
    SELECT
        N.Caption AS [Node Name],
        E.EventTime AS [Start of Downtime],
        (
            SELECT TOP 1 EventTime
            FROM Events AS EndEvent
            WHERE EndEvent.NetworkNode = E.NetworkNode
              AND EndEvent.EventTime > E.EventTime
              AND EndEvent.EventType = 5
            ORDER BY EndEvent.EventTime ASC
        ) AS [End of Downtime],
        DATEDIFF(MINUTE, E.EventTime,
            (
                SELECT TOP 1 EventTime
                FROM Events AS EndEvent
                WHERE EndEvent.NetworkNode = E.NetworkNode
                  AND EndEvent.EventTime > E.EventTime
                  AND EndEvent.EventType = 5
                ORDER BY EndEvent.EventTime ASC
            )
        ) AS [Duration]
    FROM Events AS E
    INNER JOIN Nodes AS N ON E.NetworkNode = N.NodeID
    WHERE E.EventType = 1
      AND E.EventTime > DATEADD(DAY, -7, GETDATE())
      AND N.Caption LIKE '%imbox%'
) AS T
WHERE T.[Duration] > 60
ORDER BY T.[Start of Downtime] DESC;