topic Re: Data Explorer - Transform negative value to 0 in Dashboarding

Data Explorer - Transform negative value to 0

AntonPineiro — Wed, 12 Jun 2024 12:51:12 GMT

Hi,

Do you know if that is possible? For example for "1 - builtin:host.cpu.usage":

Can we transform any negative value to "0"? It means, force minimal value is 0.

Best regards

Re: Data Explorer - Transform negative value to 0

Julius_Loman — Wed, 12 Jun 2024 13:44:41 GMT

Sure we can!

Apply a combination of filter and default transformations, something like below. But you must apply fold before (as you require single value)

:fold(avg):filter(series(avg,gt(0))):default(0,always)

Re: Data Explorer - Transform negative value to 0

AntonPineiro — Wed, 12 Jun 2024 16:58:57 GMT

Hi,

Thank you for you answer but it is still negative:

Maybe I am missing something.

Best regards

Re: Data Explorer - Transform negative value to 0

Julius_Loman — Thu, 13 Jun 2024 06:36:38 GMT

You have the wrong parenthesis as you apply it on the metric not on the expression. You need something like this:

(1 - builtin:host.cpu.usage:splitBy():auto):fold(avg):filter(series(avg,gt(0))):default(0,always)

Re: Data Explorer - Transform negative value to 0

AntonPineiro — Thu, 13 Jun 2024 06:51:39 GMT

Hi Julius,

Ok, thank you. Then, let me explain with more details target use case.

5 - log.XXXXX :filter(and(or(eq("YYYYY","ZZZZZ")),or(eq("YYYYY","ZZZZZ")))) :splitBy() :sum :sort(value(sum,descending))

I have an integer log metric already filtered under some dimensions. That metric is "1" or "0" and sum value in timeframe. Expresion is "target number - log metric". Normaly that expresion is "0", it means, nothing is pending.

But it is negative in some scenarios, and we want to transform it to "0" in those situations.

Are you going to follow same approach in that scenario?

Thank you so much!

Best regards

Re: Data Explorer - Transform negative value to 0

Julius_Loman — Thu, 13 Jun 2024 06:57:07 GMT

Sure it will. Something like this with the CPU metric, similarly for your log case

(1 - builtin:host.cpu.usage:filter(eq("dt.entity.host","HOST-AC0325FE2CC8184B")):splitBy():auto):fold(avg):filter(series(avg,gt(0))):default(0,always)

Re: Data Explorer - Transform negative value to 0

AntonPineiro — Thu, 13 Jun 2024 13:19:28 GMT

Hi,

I am not getting same, maybe for some transformation:

5 - log.XXXXX :filter(and(or(eq("YYYYY","ZZZZZ")),or(eq("YYYYY","ZZZZZ")))) :splitBy() :sum :sort(value(sum,descending))

I get "-1" and adding adding those:

(5 - log.XXXXX :filter(and(or(eq("YYYYY","ZZZZZ")),or(eq("YYYYY","ZZZZZ")))) :splitBy() :sum ) :fold(avg):filter(series(avg,gt(0))):default(0,always)

I am getting "1" instead of "0".

I have just added "()" to expresion and line ":fold(avg):filter(series(avg,gt(0))):default(0,always)".

Best regards

Re: Data Explorer - Transform negative value to 0

Julius_Loman — Thu, 13 Jun 2024 21:44:51 GMT

If you need a single value only, I'd go with:

(5 - log.XXXXX :filter(and(or(eq("YYYYY","ZZZZZ")),or(eq("YYYYY","ZZZZZ")))) :splitBy() :fold(sum)) :filter(series(avg,gt(0))):default(0,always)

It's sometimes tricky with the folding.

Re: Data Explorer - Transform negative value to 0

AntonPineiro — Fri, 14 Jun 2024 12:31:07 GMT

Hi @Julius_Loman,

Thank you so much!! Before I marked your answers as solutions, do you know if more logic can be applied? Some as if-else conditions. It is because if log metric does not have value (short timeframe for example), it shows "0" instead of "No data".
Ideal scenario would be.

If no data, shows No data or static number as "100".
If aritmetic operation is positive, shows result.
If aritmetic operation is negative, shows "0".

It means, any way to show only "0" with negative numbers, but not when no data points?

Best regards

Re: Data Explorer - Transform negative value to 0

Julius_Loman — Fri, 14 Jun 2024 14:51:46 GMT

Certainly!

You need to be creative 😁 and use arithmetic expressions. For example if you multiply by non-existent value, you get non-existent value. If we multiply by 1 we get the same value:

(log.couchdbosprocesserror:splitBy():sum:fold(sum)/ log.couchdbosprocesserror:splitBy():sum:fold(sum)) * (10 - log.couchdbosprocesserror :splitBy():sum :fold(sum)) :filter(series(sum,gt(0))):default(0,always)

I think you can see the logic here. Apply filtering and metric name accordingly to your case.

Re: Data Explorer - Transform negative value to 0

AntonPineiro — Mon, 17 Jun 2024 07:19:18 GMT

Catched it!

Thank you so much!