Need help with the NR insights query

I want to display the total time duration of my synthetics monitor in minutes to the insights dashboard from since it was down upto it was successful. The monitor has automated script which runs in every 15 mins. It checks the verification point given to it in the click-stream. If it does not got the verification point then it generates the alert and shown ‘failed’ result otherwise ‘success’. How I can do that? can you help me with this.

URL for the dashboard:- https://insights.newrelic.com/accounts/962251/dashboards/551775

Thanks,
Rahul Patil

Hi @Rahul_Patil

My apologies but its not too clear as to what you are asking for, also the URL wont work as it is internal to you and in future please share either the NRQL or the dashboard screen shot.

that being said, If you want to get the list of failed and success checks here is one good query.

SELECT filter(count(result), WHERE result = ‘FAILED’) as ‘Failed’, filter(count(result), WHERE result = ‘SUCCESS’) as ‘Success’ FROM SyntheticCheck since 1 months ago FACET monitorName LIMIT 1000

Also please have a look at this share, it has some amazing queries that can help you make your own.

https://discuss.newrelic.com/t/useful-insights-nrql-queries-library/30968/22

Hi @MKhanna ,

Let me make it clear what I am trying to do is We have synthetic monitor at different locations which runs our application in every 15 minutes interval using the script and logins as a virtual user. We have setup the clickstream for that monitors. Which contains some verification points on the web pages of our application. It checks one by one verification points on web pages of our application. If it got the verification point on the pages then it goes to the next VP and if it doesn’t got the verification point then the application goes down and it generates the failed alert mail and send it to us.
So the cycle continues. If in the next cycle the monitor got the VP which it doesn’t got in the previous cycle then it generates the success alert and send the mail to us as well as the application goes up.

So I want to calculate that time when the application goes down till the application was came up.
for example:- The application is down for 1 hr or 20 minutes and so on.

Attached screenshot of the alerts for your reference.

Thanks,
Rahul Patil

Hi @Rahul_Patil

so to confirm you want to know the total time incident was open or the monitor was in failed state?

is that accurate? and you want the number by NRQL?

thanks

Hi @MKhanna

Yes … for all incidents opened in a day or whatever time period I select…

Thanks

Hi @Rahul_Patil

check if this works for you.

SELECT uniqueCount(location), min(timestamp) as ‘Start’,
max(timestamp) as ‘End’,
((MAX(timestamp) - MIN(timestamp)) / 1000) AS ‘Length (seconds)’ FROM SyntheticCheck where result = ‘FAILED’ Facet monitorName,locationLabel SINCE last quarter

Hi @MKhanna,

SELECT uniqueCount(location), min(timestamp) as ‘Start’,
max(timestamp) as ‘End’,
((MAX(timestamp) - MIN(timestamp)) / 1000) AS ‘Length (seconds)’ FROM SyntheticCheck where result = ‘FAILED’ WHERE monitorName=‘ESS-OneHR-EE-View-US’ WHERE locationLabel=‘Fremont, CA, USA’ Facet monitorName,locationLabel SINCE today

used this because I want to know the duration for Particular location and monitor but still not getting total downtime.

Today we received the alert from the ‘Fremont, CA, USA’ monitor location and its down for 25 minutes but its not shown by the above query. We received 4 of such failures today but the query showing only one result mentioned the limit as well in the query

Attached screenshot for your reference.

Hi @MKhanna

output by the above query

SELECT uniqueCount(location), ((MAX(timestamp) - MIN(timestamp)) / 1000 / 60 ) AS ‘Total Failure time (minutes)’, min(timestamp) as ‘Start’,
max(timestamp) as ‘End’ FROM SyntheticCheck where result = ‘FAILED’ Facet monitorName,locationLabel SINCE last quarter

total failure time should give you the number you are looking for.

LMK if this works for you.

Hi @MKhanna,

Want below output

Thanks

Hi @Rahul_Patil

This is the way you use it.

SELECT min(timestamp) as ‘Incident open time’,max(timestamp) as ‘Closed Time’,((MAX(timestamp) - MIN(timestamp)) / 1000 / 60 ) AS ‘Total downtime(incidentopetime - closedtime)’ FROM SyntheticCheck where result = ‘FAILED’ Facet monitorName,locationLabel SINCE last quarter

I think this is showing you the numbers you want, you can play around with the layout.

thanks

Hi @MKhanna,

I want the downtime for each and every alert which we received in a day for the monitor.
What the query is doing is it is taking the time when the monitor goes in failed state first time in a day and when it goes in success last time. I don’t want the query to do this. What I want is each alert should have its separate report such as its open time , closed time and total duration.

getting this by query

I have got 4 alerts from Fremont,CA ,USA location today and each has its own downtime. I want to show that. not the total downtime by mixing all the alerts

Hi @Rahul_Patil

you can now play with the details based on playing with Facets, for example you can do facet on Minionid that will show you all the distinct minions that were created for failed so you can have distinct results you are looking for.

example

SELECT min(timestamp) as ‘Incident open time’,max(timestamp) as ‘Closed Time’,((MAX(timestamp) - MIN(timestamp)) / 1000 / 60 ) AS ‘Total downtime(incidentopetime - closedtime)’ FROM SyntheticCheck where result = ‘FAILED’ Facet monitorName, result, minionId SINCE last quarter

Play around with the query to tune it to your needs.

also try the insights tutorial, helps with understanding key data points and way to get them.

Hi @MKhanna,

Please help me with the query.

Thanks

Hi @Rahul_Patil

did you try this?
SELECT min(timestamp) as ‘Incident open time’,max(timestamp) as ‘Closed Time’,((MAX(timestamp) - MIN(timestamp)) / 1000 / 60 ) AS ‘Total downtime(incidentopetime - closedtime)’ FROM SyntheticCheck where result = ‘FAILED’ Facet monitorName, result, minionId SINCE 1 day ago

Hi @Rahul_Patil

Any update on this? please mark the request solved if the query did work for you.

thanks

Hi @MKhanna

No the query doesn’t worked for me. I am simply telling you we receive multiple alerts in a day I need each and every alert in the dashboard list with its down time. you are query is displaying th e total downtime for the period mentioned in the query.

what needed is…
for example
I received 4 alerts in a day for our monitor each and every alert has its own downtime such as first alert can have downtime 10 minutes so it should be added in the dashboard chart , second alert can have downtime 30 minutes so it should be added in list and so on.

Check below the query is collecting downtime for all alerts in a day and showing me the collection of downtime for all alerts.

I do not want this. I want result for each and every alert separately. Check below these are the alerts we received on june 24 and each has its own downtime.
ex.

Hi @MKhanna

I have the following query using which I got the incident open time

SELECT locationLabel,timestamp as ‘opened’ FROM SyntheticCheck where monitorName=‘ESS-OneHR-EE-View-US’ WHERE result=‘FAILED’ SINCE today limit 100

I want to know the incident closed time for each of these alerts and the duration between opened and closed in minutes.

thanks

Hi @Rahul_Patil

Let me put this into more detail.

So every time a check fails, it generates and alert, and as soon as the fail is resolved it closes an alert.

The idea is that when you will look at insights data explorer you will see the data points you can get the information from.

My query was trying to get you that information, every incident has one alert ( this is based on your alerts setup). so if you look at the query below, you get the time incident started (meaning you got alert) then the incident close time( meaning when check had success) and then total time the incident was open for that mean that alert that you got was for how long, that is defined by total downtime. and then we facet on the minion id is created. now if you dont want on that minion id you can go on facet of just monitor name or even error.

SELECT min(timestamp) as ‘Incident open time’,max(timestamp) as ‘Closed Time’,((MAX(timestamp) - MIN(timestamp)) / 1000 / 60 ) AS ‘Total downtime(incidentopetime - closedtime)’ FROM SyntheticCheck where result = ‘FAILED’ Facet monitorName, result, minionId SINCE 1 day ago

the query you shared.

SELECT locationLabel,timestamp as ‘opened’ FROM SyntheticCheck where monitorName=‘ESS-OneHR-EE-View-US’ WHERE result=‘FAILED’ SINCE today limit 100

Its doing a select on location and timestamp when it fails. try to do min and max time stamp to get the total time in the same query. and the add facet on minion id or result.

Thats the best I can think at this point. @philweber if you can share some inputs will be great.

thanks

2 Likes

Hey @Rahul_Patil

Was @MKhanna 's response above helpful? Let us know if you need further help