I have a bunch of legacy reports that were written on the console using a custom MSSQL queries. I'm converting these to SWQL format but I'm not as familiar with SWQL as I am with MSSQL. A lot of the reports I have are rather similar in queries. Below I have the custom MSSQL used to generate the reports. If someone can help me convert it to SWQL I would probably have enough information to do the rest myself.
SELECT * FROM (
SELECT
Nodes.Caption,
Nodes.NodeID,
StartTime.EventTime AS DownEventTime,
(
SELECT TOP 1 EventTime
FROM Events AS EndTimeTable
where EndTimeTable.EventTime >= StartTime.EventTime
AND EndTimeTable.EventType = 5
AND EndTimeTable.NetObjectType = 'N'
AND EndTimeTable.NetworkNode = StartTime.NetworkNode
AND EventTime IS NOT NULL
ORDER BY EndTimeTable.EventTime
) AS UpEventTime,
DATEDIFF(Mi, StartTime.EventTime,(
SELECT TOP 1 EventTime FROM 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 OutageDurationInMinutes
FROM Events StartTime
INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
WHERE (StartTime.EventType = 1)
AND CHARINDEX('VPNDR',UPPER(Nodes.Caption))=0
) AS UpTimeTable
where outageDurationInMinutes IS NOT NULL
AND DownEventTime between dateadd(month, datediff(month, 0, getdate())-1, 0) and dateadd(month, datediff(month, 0, getdate()), 0)
AND outageDurationInMinutes > 60
ORDER BY DownEventTime ASC, Caption ASC;
