Search to compare deposits versus withdrawals in a specific time period

[joeldavideng]

I am trying to create a query that calculates the amount of money a person deposits within an hour and then compares that to the amount of money the person withdraws within the following hour. I've been able to calculate the amounts and display them as a table, but I'd really like to be able to compare the values and alert when they match.

For example, give the following data:

9/24/2017 10:00:00 AM: account=123456789 memberName="Bad guy" command=deposit channel=atm amount=499.00 direction=external
9/24/2017 10:01:00 AM: account=123456789 memberName="Bad guy" command=deposit channel=atm amount=499.00 direction=external
9/24/2017 10:02:00 AM: account=123456789 memberName="Bad guy" command=deposit channel=atm amount=499.00 direction=external
9/24/2017 10:03:00 AM: account=123456789 memberName="Bad guy" command=deposit channel=atm amount=3.00 direction=external
9/24/2017 10:05:00 AM: account=123456789 memberName="Bad guy" command=withdrawal channel=atm amount=500.00 direction=external
9/24/2017 10:06:00 AM: account=123456789 memberName="Bad guy" command=withdrawal channel=atm amount=500.00 direction=external
9/24/2017 10:07:00 AM: account=123456789 memberName="Bad guy" command=withdrawal channel=atm amount=500.00 direction=external
9/24/2017 11:00:00 AM: account=123456789 memberName="Bad guy" command=deposit channel=atm amount=499.00 direction=external
9/24/2017 11:01:00 AM: account=123456789 memberName="Bad guy" command=deposit channel=atm amount=499.00 direction=external
9/24/2017 11:02:00 AM: account=123456789 memberName="Bad guy" command=deposit channel=atm amount=499.00 direction=external
9/24/2017 11:03:00 AM: account=123456789 memberName="Bad guy" command=deposit channel=atm amount=5.00 direction=external
9/24/2017 11:05:00 AM: account=123456789 memberName="Bad guy" command=withdrawal channel=atm amount=500.00 direction=external
9/24/2017 11:06:00 AM: account=123456789 memberName="Bad guy" command=withdrawal channel=atm amount=500.00 direction=external
9/24/2017 11:07:00 AM: account=123456789 memberName="Bad guy" command=withdrawal channel=atm amount=500.00 direction=external

I ran the query:

index=test sourcetype="test_transactions" direction=external
| bin span=1h _time
| stats sum(amount) AS "Total" by _time date_hour account command
| where Total >= 1500

which produced a nice table showing the aggregate amounts per hour per command, but I am still not sure how to compare the deposit and withdrawal totals per hour to each other. I'd appreciate any help you can give me.

Thanks

[DalJeanis · Accepted solution]

Try something like this...

index=test sourcetype="test_transactions" direction=external (command="withdrawal" OR command="deposit")

| sort 0 _time
| eval withAmount=if(command="withdrawal",Amount,null())
| eval depAmount=if(command="deposit",Amount,null())

| rename COMMENT as "eliminate unsuspicious activity here if you want"

| rename COMMENT as "count the deposits over two hours"
| streamstats timewindow=2h count(depAmount) as depCount sum(depAmount) as depTotalAmount by account

| rename COMMENT as "count the withdrawals over one hour"
| streamstats timewindow=1h count(withAmount) as withCount sum(withAmount) as withTotalAmount by account

| rename COMMENT as "Only alert on withdrawal"
| where (command="withdrawal") 
| where depCount>2 AND depTotAmount>=1499 AND 
       withCount>2 AND withTotAmount>=1499

This assumes that you want this testing on an account by account basis.

If you are testing for money laundering, you might consider putting a where clause in the noted spot, to get rid of any transactions that are OVER the reporting requirements, and are therefore unsuspicious by themselves. This might help eliminate some false positives.

[joeldavideng]

DalJeanis, that solution definitely runs faster than mine. Thanks a lot.

[DalJeanis]

@joeldavideng - Happy to oblige.

If your question is answered, then please accept the solution so others will know it has been solved.

[joeldavideng]

So I think I figured out an answer to this in case anyone using Splunk for money laundering detection is interested. I ended up running two queries, one that calculates the running total deposits and one that calculates the running total withdrawals per hour. I appended the results in a table that groups the transactions by hour and then compared the total deposits to total withdrawals. This solution doesn't allow for time in between the deposits and withdrawals, but it's a start.

index=test sourcetype="test_transactions" direction=external command=deposit
| sort _time
| streamstats count sum(amount) AS Total_Deposits by account command channel time_window=55m
| where Total_Deposits >= 1499
| table date_hour account channel count Total_Deposits
| appendcols [search index=test sourcetype="test_transactions" direction=external command=withdrawal
| sort _time
| streamstats count sum(amount) AS Total_Withdrawals by account command channel time_window=55m
| where Total_Withdrawals >= 1499
| table date_hour account channel count Total_Withdrawals]
| where Total_Withdrawals=Total_Deposits

I'd still appreciate it if anyone knows a better way to do this and would be willing to share.