Monitoring recalcitrant APIs with New Relic Flex

Data doesn’t always come to us perfectly formatted for our uses. We often have to manipulate, convert, or parse it so it does what we need it to do. In this blog I’ll show you how to take numeric data that comes formatted as a string and make it act like numbers.

Recently I posted a tutorial on collecting API data that can be used to track state, errors, and performance of an application. Like many tutorials, what I shared was the end result: a clear and concise narrative of the perfect execution of a well-conceived idea where all the steps and variables are foreseen beforehand.

This, my friends, is not how a typical day in IT goes.

The truth is that, like most of the work we do, there were a lot of false starts and a couple of sleepless nights as I struggled to get everything just the way I wanted. Things simply didn't work the way I initially expected, and required that I find workarounds. In the end, if it weren't for the help of several colleagues, including Mark Netterfield, Zack Mutchler, Zameer Fouzan, Kav Pather, and Haihong Ren, I probably wouldn’t have found a solution, and certainly not one as elegant as what I’m presenting here.

Before you call for your fainting couch and smelling salts, shocked as I know you are to hear me imply that every day in tech isn’t pure perfection and elegant execution, I want to be clear: While it’s true that no tool is perfect or does all things for all people, the issues I ran into were completely normal. Not only that, in the end the solutions I discovered were both simple to understand and easy to execute.

And it’s important to note here at the beginning that a lot of my struggles were rooted in a stubborn desire to make New Relic operate based on my preconceived ideas of how I thought things ought to work, rather than understanding and accepting how they actually operated.

The problem in a nutshell

Let me get to the specifics. On the pihole, you can query the API for data like this:

http://pi.hole/admin/api.php?summary

And it will give you output like this:

While it may not be immediately obvious, let me draw your attention to the issue:

"domains_being_blocked":"177,888"

Being surrounded by quotes, New Relic will treat the number "177,888" as a string (text) rather than as a number.

NRQL to the rescue

My first approach was pretty obvious: change the type of the input using a function. For this, the New Relic Query Language (NRQL) function numeric() is purpose-built to do just that—take data that's "typed" as a string and treat it as a number. Easy-peasy right?

You can see there’s more blog left, so obviously the answer is "of course not." This technique only worked for numbers less than 1,000.

It turns out numeric() can't handle formatted numbers, meaning strings with symbols for currency, percentage, or—to my chagrin—commas.

At that point, my colleague and fellow DevRel advocate Zameer Fouzan came to my rescue. He helped me leverage one of the newer capabilities in NRQL: the ability to parse out subelements in a table.

The feature is named aparse(), which stands for "anchor parse." You can find more information about it here, but in brief it lets you name a field, describe how you want to separate it, and then rename the separated parts. Like this:

aparse(unique_domains ,'*,*' ) As (n1,n2)

In plain English this says, "take the data in the unique_domains field, put everything before the comma into one variable (called n1), and everything after the comma into another variable (called n2)."

Now I have the two halves of my number and I can recombine them:

numeric(concat(n1,n2))

The result looks like this:

This might have been the end of my problems, except it only handles one comma. If the numbers are in millions, my NRQL query gets more complicated and ultimately I felt like there had to be a more elegant approach.

A more Flex-able approach

The penultimate stop in my journey was to take it back to the source—in this case the New Relic Flex integration—to see if I couldn't reformat the data before sending it into New Relic.

Within the Flex YAML file, there are a lot of methods for parsing, re-arranging, and reformatting the data prior to passing it into the data store. One of the most powerful of these is jq. You can find the New Relic documentation on jq here. But for a deeper dive into the utility itself, you should go to the source.

In fact, I can't describe how jq works any better than the author:

"A jq program is a "filter": it takes an input, and produces an output. There are a lot of builtin filters for extracting a particular field of an object, or converting a number to a string, or various other standard tasks."

Being a little more specific, jq will take JSON input, and for every key that matches your search parameters, it will output the value of that key, and reformat it in the process if you tell it to.

Therefore, I could create the most basic search filter like this:

jq > .domains_being_blocked

and it would output "177,888". Of course, that alone doesn’t solve my issue. By using additional filters I was able to split the output by its comma, join the two parts back together, set the output as a number, and come out the other side with a beautiful (and correct) data set.

I don't want you to think this solution occurred to me all on its own, or that I was able to slap it all together with minimal effort. This was all as new to me as it may be to you, and what you're reading below comes from the amazing and generous minds of Kav Pather (who basically invented the Flex integration) and Senior Solutions Architect Haihong Ren.

The full YAML file looks like this:

Which results in data that looks like this:

"domains_being_blocked": 182113
"dns_queries_today": 41258
"ads_blocked_today": 3152

(and so on)

This is a wonderfully effective solution to the entire issue. Unwinding the fullness of the jq filter is far beyond the scope of this blog. But Kav and Haihong have helped me to understand enough to summarize it:

• Pull out the "status:" key and the entire "gravity_last_updated" section and keep it as is.
• For everything else,
  • Split the value on the comma
  • Put the component parts back together (without the comma)
  • Output it as a number, rather than a string
• Finally, output everything (status, gravity_last_updated, and all the other values) as a single data block, which Flex will pack up and send to New Relic.

A summary…

To sum everything up, over the course of this journey I learned how to:

• Convert strings to numbers in NRQL using numeric().
• Split strings in NRQL based on a delimiter (or even text) using aparse().
• Put the split parts back together in NRQL using concat().
• Use jq in Flex to perform fairly complex string and data manipulations.

But most importantly, I hope this blog underscores the essential lesson that asking colleagues for help isn't a sign of weakness; it's a sign of maturity and that, as author and speaker Ken Blanchard said, "None of us is as smart as all of us."

…and a plot twist

After all of my searching, all of my queries to coworkers, all of my testing and troubleshooting; and after all the associated tears, rage, and frustration, I discovered I didn't need any of it.

Remember what I said at the start of this blog?

… it’s important to note here at the beginning that a lot of my struggles were rooted in a stubborn desire to make New Relic operate based on my preconceived ideas of how I thought things ought to work, rather than understanding and accepting how they actually operated.

In my haste to force New Relic to do all the heavy lifting; in my self-centered belief that New Relic should be able to accept any data I threw at it and convert it to any other type of format I needed on the fly; I ignored the simplest and most elegant solution of all: 

It turns out that Pi-hole has an option to output raw, unformatted data. In the place of the standard URL http://pi.hole/admin/api.php?summary, I can use this instead: http://pi.hole/admin/api.php?summaryRaw. 

Once I did so, all the numbers were returned in a way that New Relic could use without any additional manipulation.

{
  "domains_being_blocked": 182113,
  "dns_queries_today": 42825,
  "ads_blocked_today": 1846,
  "ads_percentage_today": 4.310566,
  "unique_domains": 9228,
  "queries_forwarded": 25224,
  "queries_cached": 15547,
  "clients_ever_seen": 36,

  (and so on)

Which simply goes to show that the solutions to our problems are out there, as long as we have the patience and perseverance to find them.