Skip to main content
Splunk Lantern

Bank accounts with a negative balance

The table below explains in detail the steps of a Splunk Enterprise or Splunk Cloud Platform search to help you run a regular report that shows which accounts have a negative balance. For more information, review the use case monitoring consumer bank accounts for potential fraud.

Some commands, parameters, and field names in the searches below may need to be adjusted to match your environment. In addition, to optimize the searches shown below, you should specify an index and a time range when appropriate.

Splunk recommends that customers look into using data models, report acceleration, or summary indexing when searching across hundreds of GBs of events in a single search. The searches provided here are a good starting point, but depending on your data, search time range, and other factors, more can be done to ensure that they scale appropriately.  

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.