Sum different custom parameters in NRQL

I’m having great difficulty in achieving the following from NRQL.
Basically I’m adding two custom parameters like Custom/MyAttribute/One and Custom/MyAttribute/Two (both are mutually exclusive) (I’m adding only one parameter based on a logic per AwsLambdaInvocation)

And what I’m trying is
SELECT sum(Custom/MyAttribute/One)+ sum(Custom/MyAttribute/Two) as 'Total Relics' from AwsLambdaInvocation FACET basicParam SINCE 7 days ago limit max

And this always returns no data available.

But if I try getting individually like
SELECT sum(Custom/MyAttribute/One) as 'Total Relics' from AwsLambdaInvocation FACET basicParam SINCE 7 days ago limit max
it will always work.

Can someone help me to achieve sum of both attributes?

Hi, @santosh.tungam: It is because number + null = null. Try something like this:

SELECT filter(sum('Custom/MyAttribute/One'), WHERE 'Custom/MyAttribute/One' IS NOT NULL) + 
       filter(sum('Custom/MyAttribute/Two'), WHERE 'Custom/MyAttribute/Two' IS NOT NULL)

A cleaner way to do this would be to have two custom attributes: value and type. Then you could simply do:

SELECT sum(value) WHERE type IN ('One', 'Two')

Unfortunately, this is not working. This time I tried with three attributes, and when I run query individually each of them is always returning a value > 0

SELECT filter(sum('Custom/MyAttribute/One'), WHERE 'Custom/MyAttribute/One' IS NOT NULL) as 'Total Relics (Part A)' from AwsLambdaInvocation ← returning 21

SELECT filter(sum('Custom/MyAttribute/Two'), WHERE 'Custom/MyAttribute/Two' IS NOT NULL) as 'Total Relics (Part B)' from AwsLambdaInvocation ← returning 3

SELECT filter(sum('Custom/MyAttribute/Three'), WHERE 'Custom/MyAttribute/Three' IS NOT NULL) as 'Total Relics (Part C)' from AwsLambdaInvocation ← returning 11

But if I combine all three as suggested, I’m getting No chart data available.

SELECT sum('Custom/MyAttribute/One' or 0) + sum('Custom/MyAttribute/Two' or 0) + sum('Custom/MyAttribute/Three' or 0) as 'Total Relics (Part C)' from AwsLambdaInvocation FACET basicParam SINCE 7 days ago limit max

Might work. Might want to try limiting it to a specific lambda to test out.

2 Likes

@6MM I tried this, but still it doesn’t work. I can see the same behaviour as before :slightly_frowning_face:

Can you post exactly what you are using? I feel like you might have a simple NRQL problem.

Thanks @6MM for getting back.

Please find the query I’m using. (Sorry, I had to mask the custom parameter names as I should not be posting the actual wordings we have)

SELECT sum(`L/A/I` or 0) + sum(`L/A/Q` or 0) + sum(`L/A/A` or 0) from AwsLambdaInvocation where billPaid = ‘false’ FACET someParamName SINCE 7 days ago limit max

Please note that billPaid and someParamName parameters will always be added to all lambda invocations while I’m adding at most one of (L/A/I or L/A/Q or L/A/A) based on the input request to lambda.

In the above query, if I specify any one parameter (unlike sum() + sum() + sum()), then I’m getting the results. (Even summing two parameters is not working)

Thank you!

Can you try it without the facet once?

Each custom attribute is numeric?

Each custom metric is exclusive? Meaning on each event you will only have one of the 3?

When you look at one of the event where you might have 1 of the 3 attributes do you have null attributes for 2 of the 3 or does the event not have the attribute name present? You can check this in the json view.

@6MM Wow! Surprisingly removing facet is giving me the exact results I needed.

Yes, each custom metric is numeric and exclusive. I might add atmost any one of the above three parameters (in the sense most of the events will not have any of the three parameters recorded.)

That said, it is always guaranteed that billPaid and someParamName parameters will always be added for all lambda invocations.

Please let me know if I need to make any tweaks in the query to achieve results faceted by someParamName

Thank you!

Try this

SELECT sum('Custom/MyAttribute/One' or 0) + sum('Custom/MyAttribute/Two' or 0) + sum('Custom/MyAttribute/Three' or 0) as 'Total Relics (Part C)' from AwsLambdaInvocation FACET someParamName, cases( where someParamName is null) SINCE 7 days ago limit max

@6MM Nope, that is not working.

I’d just assume you were spelling something wrong. I’d double check.

I double checked it @6MM , it is still the same.

… and if you remove the facet it works?

Yeah, it is working if I remove the facet condition. I’ve opened a support ticket on the same, @6MM