Your data. Anywhere you go.

New Relic for iOS or Android

Download on the App Store    Android App on Google play

New Relic Insights App for iOS

Download on the App Store

Learn more

Close icon

April Coffee Chat: Bring Your Broken NRQL!



April Coffee Chat :coffee:

We are excited this month to host a #coffee-chat in slack focused on NRQL! Since we are all sharing custom solutions with each other for most of the month, come troubleshoot any “broken” or less optimal NRQL with two experts! This month our Coffee Chat will feature Ben @babbott and Aubrey @amasten, two NRQL masters! (Not an official title, but it should be.) Come ask any and all questions about New Relic’s query language.

What can you expect from a Coffee Chat ? It’s an entire hour dedicated to learning from New Relic experts! We move a conversation topic to slack and you can pepper our willing expert with questions in real time. I then take that conversation and publish it here, in the community! (Check out this one , and this one !)

Coffee Chats are a great opportunity to learn something new, share knowledge with each other in a casual setting, and share a virtual cup of coffee or tea with other New Relic Explorers.

Coffee Chat with Ben and Aubrey - Bring Your Broken NRQL

  • Topic: New Relic AMA Coffee Chat
  • Date: Wednesday, April 24, 2019
  • Time: 1 PM Pacific Time
  • Location Coffee Chat Slack Instance (#coffee-chat)

How to join the Coffee Chat Slack Channel

To get invited to our New Relic Users Slack workspace follow the instructions below:

  1. Head over to the sign up page:
  2. Enter in your email address. Pretty soon you’ll receive an invite in your email inbox from Slack to join the New Relic Users workspace.
  3. Once you’re in the correct workspace, either through your browser or the Slack app you can navigate to the #coffee-chat channel . You’re welcome to also come to #introduce-yourself to tell us a bit about you, and what your New Relic specialty subjects are . At the time the Coffee Chat is due to begin, come on into the #coffee-chat channel to ask your questions.

Note that this workspace isn’t monitored outside of scheduled Coffee Chats. If you do have questions outside of the timeframe of the scheduled coffee chats, please post that question on the Explorers Hub.

See you on the 24th for some nerd-ing out on NRQL :nerd_face:

Looking for NRQL to display total pages which are Below and Over SLA

Linds [12:54 PM]
Only 7 minutes until we get started! So excited… :coffee:

Make sure you have your beverage of choice and any NRQL questions you have! @here

ReliableMK [12:56 PM]
M ready

Linds [1:01 PM]
WELCOME! Everyone! We are here with @Ben Abbott @Aubrey & @Sean Henry - three of the best in the NRQL Biz

We only have an hour so who is going to kick things off?

Aubrey [1:01 PM]
Hey everyone! Excited for your questions

Ben Abbott [1:01 PM]
Hey everybody!

Sean Henry [1:01 PM]
Howdy do!

Linds [1:02 PM]
I think NRQL questions are the most popular topic most days in the community, and these three answer a LOT of them

ZackM [1:04 PM]
not quite technical per say; but is there any roadmap that you can speak of that removes the 1000 row limit from NRQL queries?

Linds [1:04 PM]
Aww come on Zack…… haha

ZackM [1:05 PM]
what can I say? “I like big data and I cannot lie” (if y’all make that into a t-shirt I call dibs on first edition)

Linds [1:05 PM]
LOL that is GOLD
We won’t be able to speak to any Insights Roadmap questions today, sorry!

ZackM [1:09 PM]
that’s fair; let’s turn it technical then; what would you recommend to work around the current NRQL limitations of 1000 row returns, and odd aggregations when you get above XX results in a facet?

ReliableMK [1:09 PM]
I will go next once Zack M is done

Sean Henry [1:09 PM]
Filters/WHERE clauses!
Not the best answer, I realize, but if you just cut out enough extraneous data you can usually cull down to them meaningful stuff

ZackM [1:10 PM]
in our situation(s), we need all the results; it’s an unfortunate place to be in
thanks for the sanity check though; appreciate seeing that we’re not just missing some glaringly obvious trick

Aubrey [1:12 PM]
@ZackM correct, you’re not missing anything obvious. For uniques and uniqueCount functions, splitting a query by an attribute or splitting by the time range you’re querying until each resulting query is less than 256 or 1000 is the way to go

ReliableMK [1:13 PM]
Question: I want to create a quick dashboard that will be like APM and INFRA key dump in one place, without giving any details. This will be an end client facing dashboard.
so any NRQL stack that I can use to build that dashboard will make my life easy.
got this request like 10 mins back!! So Perfect time for this chat :smile:

Ben Abbott [1:14 PM]
What do you mean by “key dump”?

Steve [1:16 PM]
One of the customer-shared dashboards referenced both APM and Infra, maybe you can grab parts out of there that fulfills most of the need? Customer Dashboard: APM & Infrastructure

Aubrey [1:16 PM]

Sean Henry [1:16 PM]

Ben Abbott [1:16 PM]

Linds [1:16 PM]

Steve [1:16 PM]
oh hai
Posted using /giphy

Linds [1:17 PM]
That dashboard is seriously great

ReliableMK [1:19 PM]
Thanks STEVE

Linds [1:20 PM]
Is that kind of what you had in mind? @ReliableMK

ReliableMK [1:20 PM]
honstly not really.
but i can work with this concept.

Linds [1:20 PM]
Tell us more!

Sean Henry [1:20 PM]
Can you let us know more about what you mean by “key dump?”

ReliableMK [1:21 PM]
I want to make some thing is more customer facing and does not tell any critical info, but says service is up, infra is up, apdex is good, latency is good and so on…
so generate the widgets that will tell them life is good.
without giving any internal laundry
What I meant by key dump was, all the kpi from APM and INFRA
on high level bases

Steve [1:22 PM]
Kinda sounds like you’re looking for a status page i.e. but within Insights

ReliableMK [1:23 PM]
somewhat yes
the client asked we need access to new relic
and we said are you kidding me
now i have been told to get the work around done

Linds [1:24 PM]
Ooh that is an interesting spot to be in…

ReliableMK [1:24 PM]
so i have been thinking

Ben Abbott [1:24 PM]
You can use embedded charts to share live widgets with your clients without allowing them access to the underlying data.

ReliableMK [1:25 PM]
and i though would be easier to get apdex and than right its good or bad

Sean Henry [1:25 PM]
Each query would be different depending on the data you’re accessing

ReliableMK [1:25 PM]
@Ben Abbott already done that for some kpi, with internal departments via confluence
@Sean Henry I know
writing soo many queries, but than I have the brain to power 3 in this chat
and idea popped up.
sorry NRQL brains to the power 3
so let the queries flow!!

Linds [1:26 PM]
:brain: + :three:

Sean Henry [1:26 PM]
SELECT count(*) FROM SystemSample FACET hostname SINCE 45 minutes ago TIMESERIES 1 minute
If any hosts drop to 0, you know they’ve lost connection
How about how healthy a host is?

ReliableMK [1:28 PM]
yea that oo

Sean Henry [1:28 PM]
SELECT max(loadAverageFiveMinute) FACET hostname SINCE 45 minutes ago TIMESERIES 1 minute (edited)
I find that Load Average is an underappreciated measure of host health
If one of the lines in that chart started going up, you’d know a host was under a heavy load and/or health was getting bad

ReliableMK [1:30 PM]
SELECT average(loadAverageFiveMinute) from SystemSample FACET hostname SINCE 45 minutes ago TIMESERIES 1 minute

Sean Henry [1:30 PM]
Yes, but I just changed it from average to max, so you’d get quicker warning if something was going bad

ReliableMK [1:30 PM]

Sean Henry [1:31 PM]
(For those of you who are curious about Load Average, here’s an introductory explanation:

Aubrey [1:33 PM]
SELECT uniqueCount(`error.class`) FROM TransactionError SINCE 1 day ago COMPARE WITH 1 week ago

ReliableMK [1:33 PM]
very nice
keep them flowing
this coffee is too sweet!!

Aubrey [1:33 PM]
That’s showing whether new types of errors have shown up in the last week

ReliableMK [1:34 PM]

Aubrey [1:38 PM]
@ReliableMK are there any other specific KPIs you want to monitor?

Steve [1:38 PM]
Here’s a question for the room: what’s your favorite/most interesting query that uses 2+ event types?

Linds [1:39 PM]
…challenge extended

Sean Henry [1:39 PM]
select filter(count(*), where `error.message` is not null)*100 / filter(count(*), where duration is not null) from Transaction, TransactionError where appId = {your_app_id}

:point_up: Error percentage (edited)

Linds [1:39 PM]
…challenge accepted

Ben Abbott [1:40 PM]
SELECT average(duration) FROM PageView, Transaction FACET eventType()

Sean Henry [1:40 PM]
:point_up: Front-end vs. back-end performance

Linds [1:42 PM]
We know you must have one, @Steve

Steve [1:42 PM]
who me?

Sean Henry [1:43 PM]
For me, this is like playing Scrabble — I’m better at coming up with words as needed, vs. coming up with words when given a random series of letters. So … give us your use case and we’ll try to come up with a query to solve it!

Steve [1:43 PM]
SELECT count(*) from JavaScriptError, TransactionError since 24 hours ago facet eventType() TIMESERIES

Steve [1:43 PM]
^ compares front end and back end errors; ever wanted to get confirmation that your api is falling over? there ya go

Ben Abbott [1:47 PM]
While I’m here, I want to make sure everyone is aware of the relatively new math functions available in NRQL, such as abs, clamp, round, etc…

Sean Henry [1:48 PM]
Not to mention numeric, which allows users to now do math on coreCount, something @jbiggley asked about a long time ago, IIRC

Ben Abbott [1:49 PM]
I :heart: numeric

Steve [1:49 PM]
:raised_hands: numeric

jbiggley [1:49 PM]
Love me some numeric

Aubrey [1:49 PM]
As long as we’re sharing links, everyone interested should read Kevin’s post on advanced NRQL!

Steve [1:49 PM]

Linds [1:51 PM]
Last 10 minutes! Definitely have time for one more question!
Any takers?
This has been really great—and we basically watched an entire dashboard be built! All in a day’s work.
Well we have churros waiting for us, so we at New Relic are going to jump off! Look out for this thread to be posted in the Explorers Hub!

Ben Abbott [1:52 PM]
Thanks everyone. See you next time.

Sean Henry [1:53 PM]
Asta la barista, y’all!

Aubrey [1:53 PM]
Thank you everyone!
Message Input

ReliableMK [2:28 PM]
sorry, got pulled into meeting
thanks for the help
and tips