Moodle alerts using New Relic Query Language NRQL

I’ve been using New Relic to monitor our Moodle installation for the past 4 years. Recently New Relic have added some lovely alerting features which use their NRQL language to trigger any application issues.

With my recent change of job roles (Technology Enhanced Learning Manager) I have now being using NR on a daily basis, trying to get the best out of it.

Whilst reading through the help on NRQL alerting available on NR site, NR blog and in the NR forums I hit upon a few issues.

What if you know your site performs early morning syncs with other systems and these syncs will always trigger your alerts. Something that I didn’t want happening. How can you stop this from happening?

This is how I managed it:

NRQL Database spike alert : ignores 2 to 4:59 am

SELECT average(databaseDuration) * 100 FROM Transaction WHERE hourOf(timestamp) IN ('0:00','1:00','5:00','6:00','7:00','8:00','9:00','10:00','11:00','12:00','13:00','14:00','15:00','16:00','17:00','18:00','19:00','20:00','21:00','22:00','23:00') with TIMEZONE 'Europe/London'

The above is checking average database duration in the Transaction only within the hours 0:00-01:59 skipping 02:00 to 4:59 and checking during 05:00-23:59. The * 100 gives the query value the same representation as what you can see in the APM / Database section.

NRQL Errors > 20% : ignores 2 to 4:59 am

SELECT filter(count(*), WHERE `error.message` is not null)*100 / filter(count(*), WHERE duration is not null) as 'Errors' from Transaction, TransactionError WHERE hourOf(timestamp) IN ('0:00','1:00','5:00','6:00','7:00','8:00','9:00','10:00','11:00','12:00','13:00','14:00','15:00','16:00','17:00','18:00','19:00','20:00','21:00','22:00','23:00') with TIMEZONE 'Europe/London'

The above is converting the query value into the same as what you see when looking at the Error rate on the APM / Overview. This is checking in the Transaction only within the hours 0:00-01:59 skipping 02:00 to 4:59 and checking during 05:00-23:59.

NRQL APDEX < 0.5 for 2 minutes

SELECT apdex(duration, 0.5) FROM Transaction WHERE hourOf(timestamp) IN ('0:00','1:00','2:00','3:00','4:00','5:00','6:00','7:00','8:00','9:00','10:00','11:00','12:00','13:00','14:00','15:00','16:00','17:00','18:00','19:00','20:00','21:00','22:00','23:00') with TIMEZONE 'Europe/London'

The above is converting the query value into the same as what you see when looking at the Apdex score APM / Overview. This is checking in the Transaction within all 24hrs in a day.

I’ve got this set to only trigger if the duration is longer than 2 minutes as this can trigger in the early am. When using the threshold type of static and query value below 0.5 you cannot use ‘hourOf(timestamp) IN’ and skip some hours as those hours will trigger as zero which will throw the alert.

When I first tried setting up these alerts I was using ‘hourOf(timestamp) NOT IN’ rather than ‘hourOf(timestamp) IN’ it was thanks to a NR support person who helped me figure out that ‘NOT IN’ wasn’t working correctly.

Hopefully you can get some use out of the above, please leave any examples in the comments below.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s