Splunk Search

How to compare values across rows and columns

maingirl
New Member

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 |                  

Labels (2)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

See if this helps.

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 ]
| stats values(*) as * by company, mid, card_number
| where returns != sales
| table company mid card_number returns
---
If this reply helps you, Karma would be appreciated.
0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try this

 index=X sourcetype=Y  (transaction_type= “refund" earliest=-1d@d latest=@d) OR (transaction_type= “sale" earliest=-30d@d latest=@d) 
| eval sales=if(transaction_type= “sale”, total,0) 
| eval returns=if(transaction_type= “ refund”, total,0)
| stats values(sales) as sales sum(returns) as returns by company_name, mid, card_number
| where sales!=returns
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...