I have two searches with three fields in common but two field that differ. I'm trying to find returns that don't have a matching sale for that company_name, mid, and card_number. The return and sales fields are both dollar amounts. "total" is the dollar amount of the transaction, return or sale index=X sourcetype=Y earliest=-1d@d latest=@d | where transaction_type= "refund" | stats values(total) as returns by company_name, mid, card_number | append [ search index=X sourcetype=Y earliest=-30d@d latest=@d | where transaction_type= "sale" | stats values(total) as sales by company_name, mid, card_number ] Currently I have this search that pulls all return transactions from the past day, as well as every sale from the past month, the results look like this company. MID. card num. returns sales +-------------------------------------------------------------------+ | company A | 1234 | 1234***7890 | 50.00 | | | company B | 1254 | 1234***1234 | 80.00 | | | company C | 1236 | 1234***1230 | 75.00 | | | company A | 1234 | 1234***7890 | | 50.00 | | company B | 1254 | 1234***1234 | | 30.00 | | company C | 1236 | 1234***1230 | | 75.00 | You can see company B has refunded the card number 1234***1234 for the amount of $80.00, but there was not a sale to that card in that amount. I would like my search to exclusively display the rows (with the return amount only, not any sale numbers) where this happens. so Ideally the search would have returned just one row: company. MID. card num. returns +------------------------------------------------------+ | company B | 1254 | 1234***1234 | 80.00 |
... View more
