Skip to main content

 

Splunk Lantern

Bank accounts with a negative balance

You might need to run a regular report that shows which accounts have a negative balance when doing the following:

Prerequisites 

In order to execute this procedure in your environment, the following data, services, or apps are required:

Example

Customers having accounts with a negative balance can sometimes cause a compliance issue for banks. A negative balance could indicate an overdraft or account takeover. This report provides just-in-time information so that at the time of an account transaction, you can inform the customer.

To optimize the search shown below, you should specify a time range. You may also need to adjust fields to match what is available in your data source. 

  1. Run the following search:
    |sourcetype=<transaction data source>
    |eval _time=strptime(_time, "%Y/%m/%d %H:%M:%S")
    |sort _time
    |lookup <customer account info file> customer
    |stats last(_time) AS _time last(amount) AS amount last(accountID) AS accountID last(action) AS action last(account_type) AS account_type list(other_balance) AS other_balance list(other_accountID) AS other_accountID  list(other_account_type) AS other_account_type BY customer 
    |search other_balance=*
    |nomv other_balance
    |search other_balance="-*"
    |makemv delim=" " other_balance
    |table _time, customer, amount, accountID, action, account_type, other_accountID, other_account_type, other_balance
    |eval amount=tostring(round(amount, 2),"commas")

Search explanation

The table provides an explanation of what each part of this search achieves. You can adjust this query based on the specifics of your environment.

Splunk Search Explanation
|sourcetype=<transaction data source> Search only your business service data for bank transactions.
|eval _time=strptime(_time, "%Y/%m/%d %H:%M:%S")
 
Parse the transaction time stamp into a UNIX time value.
|sort _time
 
Sort the results from newest to oldest.
|lookup <customer account info file> customer
 
Search the customer field of a lookup file of customer accounts that you have previously uploaded into your Splunk deployment.
|stats last(_time) AS _time last(amount) AS amount last(accountID) AS accountID last(action) AS action last(account_type) AS account_type list(other_balance) AS other_balance list(other_accountID) AS other_accountID  list(other_account_type) AS other_account_type BY customer 
 
Correlate the customer information with the lookup file data that shows the other accounts for the customer, renaming the fields as shown. Group the details by customer.
|search other_balance=*
 
Search the lists in the other_balance field and return events with any result.
|nomv other_balance
 
Convert the lists returned into single value results. 
|search other_balance="-*"
 
Search the results and return any with a negative balance. 
|makemv delim=" " other_balance​​​​​ Combine the customer account balances back into a multi-value field for readability. 
|table _time, customer, amount, accountID, action, account_type, other_accountID, other_account_type, other_balance
 
Display the results in a table with columns in the order shown.
|eval amount=tostring(round(amount, 2),"commas") Convert the amounts to strings rounded to two values, using a comma when needed. 

Result

Every time a customer performs an account transaction, inform them about their other accounts. If any account has a negative balance, let them know so they can take corrective action.