Search comparison not working

[randy_moore]

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. 

[richgalloway · Accepted solution]

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.

[randy_moore]

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!

[Sum_Var]

Appreciate the details. Very Helpful!