Need help with a search with join, append or appendcols

[tungpx]

Hello, I need help with a search query, that at first seem easy but suprising difficult to execute. I have a money transaction db between 2 person, now I have to find which person send out more money than they receive, and output all of their transaction (both send and receive). 

My query is like so 

index=myindex 
|eventstats sum(AMOUNT) as total_sent by ACCOUNT_FROM
|eval temp=ACCOUNT_FROM
|table _time ACCOUNT_TO ACCOUNT_FROM TRACE total_sent INFO temp
|join type=inner temp
[search index=myindex
|stats sum(AMOUNT) as total_received by ACCOUNT_TO
|eval temp=ACCOUNT_TO] 
|where total_sent > total_receive 

This query only produce the transaction at which that account sending out but not the transaction that that account receive. How do I go about this. I'm thinking about output the temp as an csv and inputlookup again in the db.

[NevilleRadcliff]

Try using eventstats instead of join to keep both sent and received transactions. coalesce helps handle null values. This approach avoids lookup and maintains full data visibility while ensuring the correct filtering of accounts. I work in an animation studio, and transferring large video files was always a challenge. We tried cloud storage, but it was slow and required sign-ups. Filemail solved all our problems—it’s fast, secure, and lets us send huge files without forcing the recipient to create an account. If you’re in the creative industry, this is a must-have!

[ITWhisperer]

Try something like this

| eventstats sum(AMOUNT) as total_sent by ACCOUNT_FROM
| eventstats sum(AMOUNT) as total_received by ACCOUNT_TO
| table _time ACCOUNT_TO ACCOUNT_FROM TRACE total_sent total_received INFO AMOUNT
| where total_sent > total_received

[tungpx]

But it doesn't right, does it?
Your query produce total_sent is for ACCOUNT_FROM, and total_received is for ACCOUNT_TO. Since ACCOUNT_FROM and ACCOUNT_TO are two different person then where total_sent > total_received is not make sense. 

[ITWhisperer]

You are right - I misunderstood what you were trying to do - try this

| eval row=mvrange(0,2) 
| mvexpand row
| eval sent=if(row=0,AMOUNT,null())
| eval received=if(row=1,AMOUNT,null())
| eval account=if(row=0,ACCOUNT_FROM,ACCOUNT_TO)
| eventstats sum(sent) as total_sent sum(received) as total_received by account
| fillnull value=0 total_sent total_received
| where total_sent > total_received

[tungpx]

What if I want to add the requirement that the amount received have to be above 250 and the number of reviced transaction have to be above 10.  The original query is

index=myindex AMOUNT>=250
|eventstats sum(AMOUNT) as total_sent count as receive by ACCOUNT_FROM
|eval temp=ACCOUNT_FROM
|where receive >10
|table _time ACCOUNT_TO ACCOUNT_FROM TRACE total_sent INFO temp
|join type=inner temp
[search index=myindex
|stats sum(AMOUNT) as total_received by ACCOUNT_TO
|eval temp=ACCOUNT_TO] 
|where total_sent > total_receive 

[ITWhisperer]

| eval row=mvrange(0,2) 
| mvexpand row
| eval sent=if(row=0,AMOUNT,null())
| where isnull(sent) OR sent>=250
| eval received=if(row=1,AMOUNT,null())
| eval account=if(row=0,ACCOUNT_FROM,ACCOUNT_TO)
| eventstats sum(sent) as total_sent sum(received) as total_received count(received) as count by account
| fillnull value=0 total_sent total_received
| where total_sent > total_received AND count > 10