Overall Node Availability (Last 7 Days)

Could you please help to get average availability for last hour based on custom properties

--AVG availability
SELECT
concat(round(avg(Availability), 2), ' %' ) as Network
FROM Orion.ResponseTime rt
where
monthdiff(datetime,getdate())=1 and rt.Node.CustomProperties.device_type ='network'

Could you please help to get average availability for last hour based on custom properties

--AVG availability
SELECT
concat(round(avg(Availability), 2), ' %' ) as Network
FROM Orion.ResponseTime rt
where
monthdiff(datetime,getdate())=1 and rt.Node.CustomProperties.device_type ='network'

Your above question should be in the Orion SDK Forum, but I'll answer it here.

You are asking for the last 1 hour in the text, but your filter is for the last month, so I'm not sure what you want.  I don't have that particular custom property defined, so I've commented it out of my responses.

Your original query is fine, if you want the availability of all 'network' devices over the last month.

SELECT CONCAT(ROUND(AVG(Availability), 2), ' %' ) AS [Network]
FROM Orion.ResponseTime AS [RT]
WHERE MONTHDIFF([RT].ObservationTimestamp, GETDATE() ) = 1
--and [RT].Node.CustomProperties.device_type ='network'

But you are asking for the last hour.  For that you'd just need to change the WHERE clause to only give you the times between one hour ago and "now."

SELECT CONCAT(ROUND(AVG(Availability), 2), ' %' ) AS [Network]
FROM Orion.ResponseTime AS [RT]
WHERE [RT].ObservationTimestamp >= AddHour(-1, GetDate())
--and [RT].Node.CustomProperties.device_type ='network'

SELECT CONCAT(ROUND(AVG([RT].Availability), 2), ' %' ) AS [%Avail]
     , DATETRUNC('hour', [RT].ObservationTimestamp) AS [Day]
FROM Orion.ResponseTime AS [RT]
WHERE MONTHDIFF( [RT].ObservationTimeStamp, GETDATE() ) = 1
-- and [RT].Node.CustomProperties.device_type ='network' -- <-- this is where you define your custom property filter.
GROUP BY DATETRUNC('hour', [RT].ObservationTimestamp)
ORDER BY DATETRUNC('hour', [RT].ObservationTimestamp) DESC