Data Explorer - Transform negative value to 0

[AntonPineiro]

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

[Julius_Loman]

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)

[AntonPineiro]

Hi,

Thank you for you answer but it is still negative:

Maybe I am missing something.

Best regards

[Julius_Loman]

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)

[AntonPineiro]

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

[Julius_Loman]

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)

[AntonPineiro]

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

[Julius_Loman · Accepted solution]

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.

[AntonPineiro]

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.

1. If no data, shows No data or static number as "100".
2. If aritmetic operation is positive, shows result.
3. 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

[Julius_Loman · Accepted solution]

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.

[AntonPineiro]

Catched it!

Thank you so much!