topic Search comparison not working in Splunk Search

Search comparison not working

randy_moore — Wed, 24 Nov 2021 15:59:29 GMT

Hi -
I have some data that looks like this, which ingests into splunk with no issues at all

11/24/2021 08:47:21.321,"category":"transaction","tc"="93","amount_approved":"9.99","amount_requested":"493.95" etc etc etc 11/24/2021 08:45:14.121,"category":"transaction","tc"="93","amount_approved":"5.99","amount_requested":"5.99" etc etc etc 11/24/2021 08:45:14.121,"category":"transaction","tc"="01","amount_approved":"6.99","amount_requested":"6.99" etc etc etc

I want to do a a search to filter out the transactions to only see where the amounts differ

index=ABC sourcetype=XZX category=transaction tc=93 amount_approved!=amount_requested

That simple search doesn't work. splunk is not filtering on the amount_approved!=amount_requested comparison. In the example above I would get both "tc=93" transactions from the sample data , instead of just getting the first one.

If I remove the amount_approved!=amount_requested from the search and add it to a where clause like this

index=ABC sourcetype=XZX category=transaction tc=93 |where amount_approved!=amount_requested

it works fine as I only get 1 event back.
What is wrong with my initial search line?

I would like to not read in all of the transactions before I filter, hence the need to put the comparison on the search line.

Re: Search comparison not working

richgalloway — Wed, 24 Nov 2021 16:21:45 GMT

The first query fails because the search command cannot handle a field name on both sides of an expression. The where command, however, does handle such an expression.

Re: Search comparison not working

randy_moore — Wed, 24 Nov 2021 16:54:18 GMT

Thanks @richgalloway I knew it was something simple that I had forgotten about. Says exactly that in the search reference guide, here: Search Ref Guide

Comparing two fields

To compare two fields, do not specify index=myindex fieldA=fieldB or index=myindex fieldA!=fieldB with the search command. When specifying a comparison_expression, the search command expects a <field> compared with a <value>. The search command interprets fieldB as the value, and not as the name of a field.

Use thewherecommand to compare two fields.

Thanks again!

Re: Search comparison not working

Sum_Var — Wed, 24 Nov 2021 19:46:41 GMT

Appreciate the details. Very Helpful!