Splunk Search

plus and minus in a trend calcul

jip31
Motivator

Hello

In this piece of code, i want to add th possibility to display a percent result with + or - before the percent:
if FIELD 1 > FIELD 2 THEN i want to display -xx%
if FIELD 2 > FIELD 1 THEN i want to display +xx%
if FIELD 1 = FIELD 2 THEN i want to display xx%

| foreach mfetp_*_AVG [| eval perc<<MATCHSTR>>_AVG=case('<<FIELD>>'=='<<FIELD>>2',0, '<<FIELD>>'>'<<FIELD>>2', round(('<<FIELD>>'/'<<FIELD>>2')*100,2), true(), round(('<<FIELD>>2'/'<<FIELD>>')*100,2))]

could you help me please??

Tags (1)
0 Karma
1 Solution

FrankVl
Ultra Champion

You should be able to simply add "-". or "+". in front of the round() function to concatenate the + or - sign in front of it. Like so:

| foreach mfetp_*_AVG [| eval perc<<MATCHSTR>>_AVG=case('<<FIELD>>'=='<<FIELD>>2',0, '<<FIELD>>'>'<<FIELD>>2', "-".round(('<<FIELD>>'/'<<FIELD>>2')*100,2), true(), "+".round(('<<FIELD>>2'/'<<FIELD>>')*100,2))]

PS: I'm wondering of you're actually calculating what you want to calculate like this. If you want to show percentual increase or decrease, then you should be calculating (field2-field1)/field1*100.

Try this example:

| makeresults | eval field1=20 | eval field2=40 
| append [ | makeresults | eval field1=40 | eval field2=20 ]
| append [ | makeresults | eval field1=20 | eval field2=20 ]
| eval percent = case(field1=field2,0,field1>field2,"-".round(field1/field2*100,2),field1<field2,"+".round(field2/field1*100,2)) 
| eval percent2 = round((field2-field1)/field1*100,2)

percent column is based on your approach, percent2 column is based on using the difference between the fields. If field1 was 20 and field2 is 40, that is a 100% increase, right? With your way of calculating it shows 200% (because field2 is 200% of field1). If you would follow your logic, you shouldn't use 0 for the case where both fields are equal, you should use 100.

View solution in original post

0 Karma

FrankVl
Ultra Champion

You should be able to simply add "-". or "+". in front of the round() function to concatenate the + or - sign in front of it. Like so:

| foreach mfetp_*_AVG [| eval perc<<MATCHSTR>>_AVG=case('<<FIELD>>'=='<<FIELD>>2',0, '<<FIELD>>'>'<<FIELD>>2', "-".round(('<<FIELD>>'/'<<FIELD>>2')*100,2), true(), "+".round(('<<FIELD>>2'/'<<FIELD>>')*100,2))]

PS: I'm wondering of you're actually calculating what you want to calculate like this. If you want to show percentual increase or decrease, then you should be calculating (field2-field1)/field1*100.

Try this example:

| makeresults | eval field1=20 | eval field2=40 
| append [ | makeresults | eval field1=40 | eval field2=20 ]
| append [ | makeresults | eval field1=20 | eval field2=20 ]
| eval percent = case(field1=field2,0,field1>field2,"-".round(field1/field2*100,2),field1<field2,"+".round(field2/field1*100,2)) 
| eval percent2 = round((field2-field1)/field1*100,2)

percent column is based on your approach, percent2 column is based on using the difference between the fields. If field1 was 20 and field2 is 40, that is a 100% increase, right? With your way of calculating it shows 200% (because field2 is 200% of field1). If you would follow your logic, you shouldn't use 0 for the case where both fields are equal, you should use 100.

0 Karma

jip31
Motivator

thanks franck!

0 Karma

FrankVl
Ultra Champion

Can you please post comments as comments, rather than answers (I think you should still be able to change that now), and then mark the actual answer as accepted? That makes it a lot easier for others to find the correct solution if they stumble upon this discussion in the future 🙂

0 Karma

jip31
Motivator

HI Franck and thanks

If field1 was 20 and field2 is 40, yes in reality it is a 100% increase
But for a trend i find more speaking to consider that 20 = 100% and 40 =200%
by curiosity it s possible to adapt my code with your calcul?
thanks a lot

here is the entire code
index="windows-wmi" sourcetype="wmi:CPUload" host="$field1$" (Name="mfetp/" OR Name="mcshield/") Name=$Service$

| rex field=Name "^(?[^\/]+)[\/]"
| head 10
| stats avg(PercentProcessorTime) as CPU_PercentProcessorTime_AVG, avg(Timestamp_Sys100NS) as CPU_Timestamp_Sys100NS_AVG
| appendcols

[ search index="windows-wmi" sourcetype="wmi:CPUload" host="$field1$" (Name="mfetp/" OR Name="mcshield/") Name=$Service$

| rex field=Name "^(?[^\/]+)[\/]"
| head 10
| stats avg(PercentProcessorTime) as CPU_PercentProcessorTime_AVG2, avg(Timestamp_Sys100NS) as CPU_Timestamp_Sys100NS_AVG2]
| foreach CPU_*_AVG
[| eval perc<>_AVG=case('<>'=='<>2',0, '<>'>'<>2', "-".round(('<>'/'<>2')*100,2), true(), "+".round(('<>2'/'<>')*100,2))]
| table percPercentProcessorTime_AVG, percTimestamp_Sys100NS_AVG
| rename percPercentProcessorTime_AVG as ProcessorTime_Trend, percTimestamp_Sys100NS_AVG as Timestamp_Sys100NS_Trend

0 Karma

FrankVl
Ultra Champion

Presenting it as 200% isn't wrong, it is just different. You're showing the ratio between the fields, which is also fine. To be consistent though, the ration for 2 equal fields should be 1 (100%) not 0. And if you're presenting it as a ratio, it is confusing to label it as + or -, since that indicates you're presenting the increase / decrease, which isn't what you're presenting.
If it is just for yourself, that doesn't matter much, but if others view that dashboard without understanding how it is calculated under the hood, they make interpret it incorrectly.

And yes, my calculation can easily be plugged into your query, using your shortened code for simplicity:

| foreach mfetp_*_AVG [| eval perc<<MATCHSTR>>_AVG=round(('<<FIELD>>2'-'<<FIELD>>')/'<<FIELD>>'*100,2)]

Note, calculating the difference like this will automatically prefix a decrease with a - sign, since the difference will be a negative number. If you still want to also prefix an increase with a + sign, you could add a fieldformat to add that prefix:

| foreach mfetp_*_AVG [| eval perc<<MATCHSTR>>_AVG=round(('<<FIELD>>2'-'<<FIELD>>')/'<<FIELD>>'*100,2) | fieldformat perc<<MATCHSTR>>_AVG = if(perc<<MATCHSTR>>_AVG>0,"+".perc<<MATCHSTR>>_AVG,perc<<MATCHSTR>>_AVG)]
0 Karma

niketn
Legend

@jip31 if it was @FrankVI 's answer which resolved your issue you should unaccept your answer and accept his instead.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...