eval output is incorrect when comparing two fields with numeric values

[mmdacutanan]

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!!

[renjith_nair · Accepted solution]

@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)

[mmdacutanan]

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)