NQL query for total monthly downtime based on web-checks

I have a NQL script that is intended to give me a value for total website downtime over a period of time:
SELECT ((MAX(timestamp) - MIN(timestamp)) / 1000 / 60 / 60 ) AS 'Total Failure time (hrs)', min(timestamp) as 'Start',max(timestamp) as 'End' FROM SyntheticCheck where result = 'FAILED' and locationLabel='Washington, DC, USA' Facet monitorName SINCE 1 month ago limit 100

However, in a case where there is more than one downtime event within the period of time (1 month in this case), the above query obviously returns an incorrect result since it counts the downtime as from the beginning of the first event to the end of the last event.

Does anyone have a suggestion on how I could structure this to accurately count up only the actual downtime minutes/hrs even if there are multiple events?
thanks

Could you simply multiply the count of check failures by the interval of the check value to get a general estimate of downtime? Obviously, the shorter the interval the more accurate the sum.

2 Likes

Hi @lprikockis - You will probably struggle with this type of calculation within New Relic. You can write a query which will provide the uptime percentage, for which there are also online tools to provide the duration of downtime based on that percentage.

There should be an example of how to do this in the NRQL Library

1 Like

Great suggestions @6MM and @stefan_garnham—thanks for taking the time to help troubleshoot!

@lprikockis: Did either of the posts above get you closer to where you need to be? Let us know what else you need at this point. :blush: Eager to hear see you get a solution!

1 Like