Skip to main content

 

Splunk Lantern

Customers with too many bank accounts

You might need to run a regular report to check how many accounts each customer has 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 too many accounts 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 if they have too many accounts above a threshold so they can consolidate. 

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 list(accountID) AS accountID list(action) AS action list(amount) AS amount list(balance) AS balance values(other_accountID) AS other_accountID sum(other_balance) AS Total_Balance BY customer
    |eval count_other_accounts=mvcount(other_accountID)
    |where count_other_accounts>10
    |eval Total_Balance=Total_Balance+balance
    |eval balance=tostring(round(balance, 2),"commas"), Total_Balance=tostring(round(Total_Balance, 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 list(accountID) AS accountID list(action) AS action list(amount) AS amount list(balance) AS balance values(other_accountID) AS other_accountID sum(other_balance) AS Total_Balance 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 count_other_accounts=mvcount(other_accountID) Count the number of other accounts and sum up the total balance. If the number of other_accounts is greater than 10, print results.
|where count_other_accounts>10 Filter results to only those where the number of accounts per customer is greater than 10.
|eval Total_Balance=Total_Balance+balance Obtain the total balance for all the customer's accounts.
|eval balance=tostring(round(balance, 2),"commas"), Total_Balance=tostring(round(Total_Balance, 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. If the number of other accounts is greater than a threshold, in this example 10, you might suggest that they consolidate.

  • Was this article helpful?