topic Why doesn't a > WHERE clause work when an = does? in Splunk Search

Why doesn't a > WHERE clause work when an = does?

47024 — Fri, 15 Nov 2019 20:24:09 GMT

I cannot seem to get my search to return results when comparing a property with a greater than comparison even though using an equals comparison does work. The 'elements' property in my message is a 0 - x property of the event...meaning it could exist zero times or it could exist multiple times...each element in the event has a 'y' value.

What i'm trying to accomplish is to count each time an event occurs where any of the elements in the event have a y value greater than a value.

example:

This search returns 2 :
index="lab" source="*-test" | eval y='line.message.space-document.design.elements{}.y' | where y="1664" | stats count

This search returns 0 when it should be the same if not more than the above search:
index="lab" source="*-test" | eval y='line.message.space-document.design.elements{}.y' | where y>"1663" | stats count

Re: Why doesn't a > WHERE clause work when an = does?

richgalloway — Fri, 15 Nov 2019 21:13:43 GMT

Splunk doesn't support greater-than / less-than with strings. If y is a number then use ... | where y>1663 | .... If y is a string use tonumber() to convert it.

Re: Why doesn't a > WHERE clause work when an = does?

47024 — Fri, 15 Nov 2019 21:17:53 GMT

Thank you for the suggestion...however when taking the string aspect away, it actually returns 0 results with both equals and greater comparisons. I changed to below and still get 0 results when I should get at least 2.

index="lab" source="*-test" | eval y=tonumber('line.message.space-document.design.elements{}.y') | where y>1663 | stats count

Re: Why doesn't a > WHERE clause work when an = does?

richgalloway — Fri, 15 Nov 2019 23:55:24 GMT

Can you share some sample data? Have you tried the second part of my answer (tonumber())?

Re: Why doesn't a > WHERE clause work when an = does?

to4kawa — Sat, 16 Nov 2019 00:09:37 GMT

index="lab" source="*-test" 
| eval y='line.message.space-document.design.elements{}.y'

It seems that there are multiple y values.

index="lab" source="*-test" 
| eval y='line.message.space-document.design.elements{}.y' 
| eval y=mvindex(y,0)
| stats count(eval(y > 1663)) as count

Re: Why doesn't a > WHERE clause work when an = does?

bowesmana — Sat, 16 Nov 2019 00:27:37 GMT

If y is multivalue, then things get complicated. If you do a

| table y

do you get a single value field for y in all cases, or multivalue?

If you want to filter where any mv value > 1663, then you need to use mvfilter, e.g.

| makeresults 
| eval y=mvappend("100","200","300","400","2000","500")
| eval x=mvfilter(y>1663)
| where mvcount(x)>0

in this case, it will satisfy the condition, but this will not

| makeresults 
| eval y=mvappend("100","200","300","400","1000","500")
| eval x=mvfilter(y>1663)
| where mvcount(x)>0

Re: Why doesn't a > WHERE clause work when an = does?

47024 — Mon, 18 Nov 2019 19:49:40 GMT

This worked! Thank you very much!