Getting Data In

eval output is incorrect when comparing two fields with numeric values

mmdacutanan
Explorer

I have a query that has an eval statement that assigns 1 to field 'isTrue' if field 'value1' is greater than field 'value2', otherwise assign 0.
My problem is if field value1 has say a value of 300 and I am comparing it to field value2 which has a value of 0.00, 'isTrue' field says '0' instead of '1'.

However, what confuses the heck out of me is when value2 is non-zero, isTrue field is assigned the correct value!

And as if I wasn't confused enough, if I use makeresults to fake out the values, isTrue field gets assigned the right value when comparing field 'value1' that has a greater than zero value against field 'value2' that has a value if 0.00

Can someone out there please help? What am I missing here? I tried adding quotes, double quotes on the field names but to no avail

Here is my full query. The eval statement is at the bottom.

index=uc sourcetype=rcd
| bucket _time span=5m 
| stats latest(Variable10) as Variable10 by _time Variable2 
| stats count(eval(like(Variable10,"Tx%|U|%"))) as U_Count by _time
| streamstats count as pri_key
| streamstats avg(U_Count) as avg, stdev(U_Count) as stdev
| eval avg=round(avg,2)
| eval stdev=round(stdev,2)
| eval lowerBound=(avg-stdev*2)
| eval upperBound=(avg+stdev*2)
| eval time_5m_value=if(pri_key=4,'U_Count',"")
| eval time_15m_prev_upperBound=if(pri_key=3,'upperBound',"")
| eval time_15m_prev_lowerBound=if(pri_key=3,'lowerBound',"")
| eval time_15m_prev_avg=if(pri_key=3,'avg',"")
| eval time_15m_prev_stdev=if(pri_key=3,'stdev',"")
| stats values(time_5m_value) AS value1 values(time_15m_prev_upperBound) AS value2 values(time_15m_prev_lowerBound) AS time_15m_prev_lowerBound values(time_15m_prev_avg) AS time_15m_prev_avg values(time_15m_prev_stdev) AS time_15m_prev_stdev
| eval isTrue=if(value1 > value2, 1, 0)

And here is the makeresults statement that I was testing with that is working just fine when comparing value1 that is greater than 0 against value2 field that is 0.00:

| makeresults count=1 | eval value1=300, value2=0.00, time_15m_prev_lowerBound=0.00, time_15m_prev_avg=0.00, time_15m_prev_stdev=0.00| fields - _time
| eval isTrue=if(value1 > value2,1,0)

Thank you in advance!!

Tags (1)
0 Karma
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

@mmdacutanan ,

It looks like you have a multi value field since you are using values(field) in your stats and there might be "" in your multivalue field.

Try this and see if it works

| eval isTrue=if(value1 > mvindex(value2,0),1,0)

You can test it with below dummy search

| makeresults count=1 | eval value1=300, value2="0.00"| fields - _time|eval value2=mvappend(value2,"")
 | eval isTrue=if(value1 > value2,1,0)
Happy Splunking!

View solution in original post

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@mmdacutanan ,

It looks like you have a multi value field since you are using values(field) in your stats and there might be "" in your multivalue field.

Try this and see if it works

| eval isTrue=if(value1 > mvindex(value2,0),1,0)

You can test it with below dummy search

| makeresults count=1 | eval value1=300, value2="0.00"| fields - _time|eval value2=mvappend(value2,"")
 | eval isTrue=if(value1 > value2,1,0)
Happy Splunking!
0 Karma

mmdacutanan
Explorer

Hello @renjith.nair ! Apologies for the late response. I was actually working on my query on and off and had to test a few things but ultimately, your answer about values(field) being multi-valued field and to use mvindex to do the comparison was the answer to my problem! 😃 I saved the mvindex(x,x) value to another variable first and then used that variable for the eval isTrue if statement comparison.

Here is my final query:

index=cisco sourcetype=rcd earliest=-21m@m latest=-5m@m
| bucket _time span=5m 
| stats latest(Variable10) as Variable10 by _time Variable2 
| stats count(eval(like(Variable10,"Tx%|NS|%"))) as NS_Count by _time
| streamstats count as pri_key
| streamstats avg(NS_Count) as avg, stdev(NS_Count) as stdev
| eval avg=round(avg,2)
| eval stdev=round(stdev,2)
| eval lowerBound=(avg-stdev*2)
| eval upperBound=(avg+stdev*2)
| eval time_5m_value=if(pri_key=4,'NS_Count',"")
| eval time_15m_prev_upperBound=if(pri_key=3,'upperBound',"")
| eval time_15m_prev_lowerBound=if(pri_key=3,'lowerBound',"")
| eval time_15m_prev_avg=if(pri_key=3,'avg',"")
| eval time_15m_prev_stdev=if(pri_key=3,'stdev',"")
| stats values(time_5m_value) AS FiveMinVal values(time_15m_prev_upperBound) AS PrevFifteenMinUpprBound values(time_15m_prev_lowerBound) AS time_15m_prev_lowerBound values(time_15m_prev_avg) AS time_15m_prev_avg values(time_15m_prev_stdev) AS time_15m_prev_stdev
| eval FiveMinVal=mvappend(FiveMinVal,"")
| eval PrevFifteenMinUpprBound=mvappend(PrevFifteenMinUpprBound,"")
| eval pos1=mvindex(FiveMinVal,0) 
| eval pos2=mvindex(FiveMinVal,1)
| eval pos3=mvindex(FiveMinVal,2)
| eval pos4=mvindex(PrevFifteenMinUpprBound,0) 
| eval pos5=mvindex(PrevFifteenMinUpprBound,1)
| eval pos6=mvindex(PrevFifteenMinUpprBound,2)
| eval isTrue=if(pos2 > pos5,1,0)
0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...