Overall Node Availability (Last 7 Days)

This is a breakdown of node availability by Nines as inspired by Uptime Report SQL query - Report Lab - The Orion Platform - THWACK (solarwinds.com)

Anonymous
  • 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'
    if you actually wanted the last month, but broken down by hour, then it's:
    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'