Skip to main content

 

Splunk Lantern

Bank accounts with zero balance

You might need to run a regular report that shows which accounts have a zero 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 zero balance can sometimes cause a compliance issue for banks. 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 
    |eval is_match=mvfilter(match(other_balance, "0"))
    |search is_match=*
    |fields - is_match
    |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 oldest to newest. 

|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.
|eval is_match=mvfilter(match(other_balance, "0" )) Filter results to those that have a 0 in the other_balance field and put them in a field called is_match.
|search is_match=* Return any event that has a value in the is_match field.
|fields - is_match Remove the is_match field from the results.
|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 balances 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, especially, one that that has a zero balance, so they can take corrective action.

  • Was this article helpful?