Max result from facet

HI, I’m new to New Relic but getting on fairly well… But I’m getting stuck on how to select the maximum count from a facet query.
I want to generate an alert when the max count in a facet query hit’s above a threshold.
e.g. something like select max(count(*) from Mobile where status = ‘badStuff’ facet appGroup) to select the top appGroup generating badStuff
Any thoughts?

Hi @Jensen

did you try
select max(count(*) from Mobile where status = ‘badStuff’ facet appGroup since 1 month ago

And you can refer to NRQL Facet clause

Hi @MKhanna
I did. I see an ‘unexpected FROM’ error when I start a query with “select max(count(*) from”
So I figure that syntax must be wrong?

I think the query you need @Jensen is:

select count(*) from Mobile where status = 'badStuff' SINCE 1 month ago facet appGroup LIMIT 1

That will give you the top result with the highest count where status is badStuff

1 Like

Thanks @stefan_garnham
This indeed does give me the top result together with the impacted appGroup!
But maybe I’m attacking the problem incorrectly… I want to end up with just that top count number as a single metric, so I can transfer the NRQL into the alerting section of New Relic.
e.g. send alert when count(badStuff) for any appGroup > 50

Yes, I think that the query I gave will work for the NRQL alert, but I have not tried it. You can then set your threshold.

If you want the alert to provide the specific appGroup application name in the email then this is currently not supported but was added to a feature request.


Thanks @Jensen, @stefan_garnham, and @MKhanna! This is a great conversation. @Jensen - let us know if that NRQL query worked for you.

1 Like

Sadly it didn’t. The facet clause is apparently not supported in the alert NRQL statments.
but thanks to @stefan_garnham and @MKhanna for their help.

@hross is there a contact for your professional services team? I did email the contact I have (Tyron W) but didn’t get a response yet and I could use some help urgently on a couple of matters.

Hey there @Jensen - let me work with that team to see what I can do for you. Looks like your rep is in Europe (are you?) and I am in the US, so the time zone may slow us down.

thanks @hross. I’ve made contact now.