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
Legend

@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)
---
What goes around comes around. If it helps, hit it with Karma 🙂

View solution in original post

0 Karma

renjith_nair
Legend

@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)
---
What goes around comes around. If it helps, hit it with Karma 🙂
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!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...