This is a breakdown of node availability by Nines as inspired by Uptime Report SQL query - Report Lab - The Orion Platform - THWACK (solarwinds.com)
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'
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 190,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.