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!

Splunk Education - Fast Start Program!

Welcome to Splunk Education! Splunk training programs are designed to enable you to get started quickly and ...

Five Subtly Different Ways of Adding Manual Instrumentation in Java

You can find the code of this example on GitHub here. Please feel free to star the repository to keep in ...

New Splunk APM Enhancements Help Troubleshoot Your MySQL and NoSQL Databases Faster

Splunk Observability has two new enhancements to make it quicker and easier to troubleshoot slow or frequently ...