Need help in converting SQL to SWQL Query

makecrazy

Hi 

I have SQL Query which is showing Nodes Todays Availability in Business Hours , need urs help to convert this query in SWQL so that i can use it in Modern Dashboard.

SELECT
Nodes.ATM_Branches ,

round(AVG(rt.Availability),2) AS AverageAvailability
--CAST( GETDATE() AS Date ) as "Date"
FROM ResponseTime AS rt INNER JOIN
Nodes ON Nodes.NodeID = rt.NodeID
WHERE (Nodes.ATM_Branches = 'ATM Branches')

AND DateTime >= CAST(CONVERT(DATE, SYSDATETIME()) AS DATETIME)
AND DateTime < DATEADD(DAY, 1, CAST(CONVERT(DATE, SYSDATETIME()) AS DATETIME))
AND DATEPART(HOUR, DateTime) >= 9 AND DATEPART(HOUR, DateTime) < 17

group by Nodes.ATM_Branches

jajao555

Something like this? Replace LATA with your custom property.

SELECT
N.CustomProperties.LATA,
N.ResponseTime,
N.ResponseTimeHistory.PercentDown,
N.ResponseTimeHistory.Availability,
round(AVG(N.ResponseTimeHistory.Availability),2) AS AverageAvailability
FROM Orion.Nodes N 

WHERE (N.CustomProperties.LATA LIKE '%West%')

-- Filter for business hours (9 AM to 5 PM)
    AND (HOUR(N.ResponseTimeHistory.DateTime) >= 9 AND HOUR(N.ResponseTimeHistory.DateTime) < 17)

GROUP BY (N.CustomProperties.LATA,
N.ResponseTime,
N.ResponseTimeHistory.PercentDown,
N.ResponseTimeHistory.Availability)

You can find SWQL functions here

github.com/.../SWQL-Functions