Can you help me compare two fields with numeric values?

[mmdacutanan]

I have a query where I do a bunch of computations, and then at the end of it, I want to add a new field based on the result of a comparison of the numeric values of 2 other fields. Here is that eval statement:

| eval isTrue=if('5m_value'>'15m_prev_upperBound', 1, 0)

'isTrue field' is always assigned 0 regardless of whether field '5m_value' is greater than or not than '15m_prev_upperBound' field! I don't know what I am doing wrong. I tried using case in the eval, but I still get the same results.

FULL QUERY:

 index=cisco sourcetype=rcd earliest=-20m@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 5m_value=if(pri_key=4,'NS_Count',"")
    | eval 15m_prev_upperBound=if(pri_key=3,'upperBound',"")
    | eval 15m_prev_lowerBound=if(pri_key=3,'lowerBound',"")
    | eval 15m_prev_avg=if(pri_key=3,'avg',"")
    | eval 15m_prev_stdev=if(pri_key=3,'stdev',"")
    | stats values(5m_value) as 5m_value values(15m_prev_upperBound) as 15m_prev_upperBound values(15m_prev_lowerBound) as 15m_prev_lowerBound values(15m_prev_avg) as 15m_prev_avg values(15m_prev_stdev) as 15m_prev_stdev
    | eval isTrue=if('5m_value'>'15m_prev_upperBound', 1, 0)

[whrg]

Hello @mmdacutanan,

I'm not entirely sure. My first thought is this:

"| stats values(5m_value) as 5m_value" will give you a multivalue field. I don't how the exact behavior on how Splunk compares (via >) multivalue fields.

So I suppose you want single values instead of mutlivalues. You could try this:

| stats first(5m_value) as 5m_value first(time_15m_prev_upperBound) AS time_15m_prev_upperBound

[woodcock · Accepted solution]

Do not use field names that start with numbers; these are unclean and reserved namespaces. Like this:

index=cisco sourcetype=rcd earliest=-20m@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 time_5m_value values(time_15m_prev_upperBound) AS time_15m_prev_upperBound 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(time_5m_value > time_15m_prev_upperBound, 1, 0)

[mmdacutanan]

Thank you so so much for the awesome tip. I tried what you gave and at first it didn't work. Then I simplified the name even more (took out the underscore) and that did the trick! So the last 2 lines look like this now:

| stats values(time_5m_value) AS FiveMinCount values(time_15m_prev_upperBound) AS Prev15mUprBnd 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("FiveMinCount">"Prev15mUprBnd", 1, 0)