NRQL - How to receive the percentage occurrence of a particular error in relation to all that occurred in query

Let’s say that I have an NRQL like:
SELECT count(*) from MigrationEvent WHERE ERROR_CODE IS NOT NULL LIMIT MAX FACET ERROR_MESSAGE
which returns 36 records faceted by ERROR_MESSAGE attribute

Error Message Migration Events
Entity not found 28
Customer not found 6
Bad request exception 1
Body validation error 1

Is it possible to modify this query and somehow receive a chart/table/etc. as a result that will contain a percentage share or particular error like:
Entity not found: 77.77%
Customer not found: 16.66%
Bad request exception: 2,77%
Body validation error: 2,77%
I tried with percantage() and filter() but never reached the desirable goal. Can somebody suggest how to reach a desirable goal?

Hi @MartinDeveloper - Yes it is possible using the percentage function.

Just noticed the last line where you stated that percentage did not provide the correct information. If you post the percentage query we may be able to spot the issue and assist :wink:

Hello @stefan_garnham, I thought about something like the below:
SELECT percentage(count(), WHERE ERROR_CODE IS NOT NULL)/filter(count(), where ERROR_CODE IS NOT NULL OR ERROR_CODE IS NULL) FROM MigrationEvent facet ERROR_CODE LIMIT MAX since 48 hours ago
but, when I want to sum up received percentages it’s never equal to 100% :slight_smile:
Cheers!

It is a simple as this:

SELECT percentage(count(*), WHERE ERROR_CODE IS NOT NULL) FROM MigrationEvent FACET ERROR_CODE LIMIT MAX SINCE 48 hours ago

Well, I tried this query before and it returns percentage: 100% for each error code. @stefan_garnham

I am stumped. As I do not work for New Relic, I cannot view your data. Hopefully someone from New Relic will be along to explain why this is happening on your data.

@stefan_garnham sure, either way, thanks for help attempt :slight_smile:
I see the root cause, I am dividing by all error events but want to divide by faceted unique one.
SELECT percentage(count(), WHERE ERROR_CODE IS NOT NULL)/filter(count( ), where ERROR_CODE IS NOT NULL OR ERROR_CODE IS NULL) FROM MigrationEvent facet ERROR_CODE LIMIT MAX since 48 hours ago
The problematic part is the numerator of the fraction, It takes sum of unique occurrences instead of the counter for particular ERROR_CODE:
Latest version of NRQL:
SELECT percentage(uniqueCount(ERROR_CODE), WHERE ERROR_CODE IS NOT NULL)/filter(count(*), where ERROR_CODE IS NOT NULL) FROM MigrationEvent facet ERROR_DESCRIPTION LIMIT MAX since 48 hours ago

1 Like

Hi @MartinDeveloper ,

First of all thank you to @stefan_garnham for helping you… Unlike Stefan, I can see your data Mwhahaha!!

Can you point me to the account that you are running this on ( Account name or Account Number will be enough for me to find it)

However from your NRQL → percentage(uniqueCount(ERROR_CODE), WHERE ERROR_CODE IS NOT NULL)/filter(count(*), where ERROR_CODE IS NOT NULL) I don’t believe this is correct.

you may need to use additional (), something like, this

(percentage(uniqueCount(ERROR_CODE), WHERE ERROR_CODE IS NOT NULL))/(filter(count(*), where ERROR_CODE IS NOT NULL) )

Without seeing your data, Im unable to check if this is correct. for you.

I hope this helps.

1 Like