Splunk Search

Search to compare deposits versus withdrawals in a specific time period

Path Finder

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

0 Karma
1 Solution

SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

Path Finder

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

SplunkTrust
SplunkTrust

@joeldavideng - Happy to oblige.

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

0 Karma

Path Finder

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="testtransactions" direction=external command=deposit
| sort _time
| streamstats count sum(amount) AS Total
Deposits by account command channel timewindow=55m
| where Total
Deposits >= 1499
| table datehour account channel count TotalDeposits
| appendcols [search index=test sourcetype="testtransactions" direction=external command=withdrawal
| sort _time
| streamstats count sum(amount) AS Total
Withdrawals by account command channel timewindow=55m
| where Total
Withdrawals >= 1499
| table datehour account channel count TotalWithdrawals]
| where TotalWithdrawals=TotalDeposits

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

0 Karma