Overall Node Availability (Last 7 Days)

Please post questions like this in the Orion SDK Forum,

KMSigma.SWI

Tried to edit another table widget with the following, hope you can assist?

SELECT n.Caption AS [Node Name]
, n.IP AS [IP Address]
, n.StatusDescription AS [Status Description]
, n.DetailsURL AS [Node Details URL]
, n.ResponseTime AS [Response Time]
, n.PercentLoss AS [Packet Loss]
FROM Orion.Nodes n
WHERE n.ResponseTimeHistory.DateTime BETWEEN GETDATE() - 1 AND GETDATE() -- this is where you define your time period
HAVING AVG(n.ResponseTimeHistory.Availability) > 99.5

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

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'