convert SQL to SWQL Query

Question

Hi Can any one convert mentioned SQL query to SWQL ** time = locatime SELECT AVG(rt.Availability) AS AverageAvailability FROM ResponseTime AS rt INNER JOIN Nodes ON Nodes.NodeID = rt.NodeID WHERE (Nodes.BRANCH_TYPE = 'non-atm') 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;

stuartd · Answer

You might, for testing purposes, want to restrict your query so edit the select line to be: SELECT TOP 10 rt.  etc

And you can drop the rt.NodeID if needed - I just added it in for my own sanity

stuartd · Answer

I can't hep with the Date function but your base query in SWQL would look like this:

SELECT  rt.NodeID, Availability
FROM Orion.ResponseTime AS rt
WHERE (rt.Node.CustomProperties.BRANCH_Type = 'non-atm')

This makes use of the implicit JOIN to Orion.Nodes to pick up on your custom property.