Splunk Search

## Sum field value by different fields and merge together in 1 table

Engager

Hi,

I have a list of events here from an account management system.

"_time",dr_account,cr_account,amount
"2017-02-09T17:20:19.000+0700",111111,222222,1500000
"2017-02-09T17:20:19.000+0700",111111,333333,1000000
"2017-02-09T17:20:19.000+0700",222222,555555,11070000
"2017-02-09T17:20:19.000+0700",333333,111111,3000000
"2017-02-09T17:20:19.000+0700",444444,111111,1250000
"2017-02-09T17:20:19.000+0700",111111,555555,3000000

I am going to make a aggregation report by sum the amount field by both dr_account and cr_account and, then display the total debit, total credit and balance (=total debit - total credit) on a table for each account, for example:

account,total_debit,total_credit,balance
111111,5500000,4250000,1250000
222222,11070000,1500000,9570000
...

I am trying to use stats sum(amount) as total_debit by dr_account | appendcols [stats sum(amount) as total_credit by cr_account] but no luck.

Thank you very much.
Lang

Tags (1)
1 Solution
Contributor

Try this one:

|appendpipe [stats sum(amount) as total_debit by dr_account |rename dr_account as account]
|appendpipe [stats sum(amount) as total_credit by cr_account |rename cr_account as account]
|stats values(total*) as total* by account
|eval balance=total_debit-total_credit

Result:

account total_credit    total_debit balance
--------------------------------------------------
111111  4250000       5500000       1250000
222222  1500000       11070000      9570000
333333  1000000       3000000       2000000
444444                    1250000
555555  14070000
Revered Legend

Try like this (everything before | eval temp is just to generate sample data, replace it with your search)

| gentimes start=-1 | eval host="2017-02-09T17:20:19.000+0700,111111,222222,1500000 2017-02-09T17:20:19.000+0700,111111,333333,1000000 2017-02-09T17:20:19.000+0700,222222,555555,11070000 2017-02-09T17:20:19.000+0700,333333,111111,3000000 2017-02-09T17:20:19.000+0700,444444,111111,1250000 2017-02-09T17:20:19.000+0700,111111,555555,3000000"| table host | makemv host | mvexpand host | rex field=host "^(?<time>[^,]+),(?<dr_account>[^,]+),(?<cr_account>[^,]+),(?<amount>[^,]+)"  | eval _time=strptime(time,"%Y-%m-%dT%H:%M:%S.%N%z")| fields - host time
| eval temp=dr_account.":debit:".amount."##".cr_account.":credit:".amount | table temp | makemv temp delim="##" | mvexpand temp | rex field=temp "(?<account>[^\:]+)\:(?<action>[^\:]+)\:(?<amount>.+)" | fields - temp | chart sum(amount) over account by action | fillnull value=0| eval balance=debit-credit
Legend

Extra credit for showing the sample data. Just missed a perfect score by the field order and name.

| table account debit credit balance
| rename debit as total_debit credit as total_credit
Contributor

Try this one:

|appendpipe [stats sum(amount) as total_debit by dr_account |rename dr_account as account]
|appendpipe [stats sum(amount) as total_credit by cr_account |rename cr_account as account]
|stats values(total*) as total* by account
|eval balance=total_debit-total_credit

Result:

account total_credit    total_debit balance
--------------------------------------------------
111111  4250000       5500000       1250000
222222  1500000       11070000      9570000
333333  1000000       3000000       2000000
444444                    1250000
555555  14070000
Engager

It works like a charm, add some isnull() check and i can now have balance for all accounts.

Thanks ehudb.

Legend

Works fine. Maybe a little more readable than somesoni2's.

Explorer

Hi! Try to use this search:

|stats sum(amount) as total_debit by dr_account | rename  dr_account  AS account
|append [stats sum(amount) as total_credit by cr_account | rename  cr_account  AS account]
|stats value(total_debit) AS total_debit, value(total_credit) AS total_credit by account | eval balance=total_debit-total_credit

Also you can use appendpipe command for search optimization like this:

|rename  dr_account  AS account | stats sum(amount) as total_debit by account
|appendpipe [stats sum(amount) as total_credit by cr_account | rename  cr_account  AS account]
|stats value(total_debit) AS total_debit, value(total_credit) AS total_credit by account |eval balance=total_debit-total_credit
Get Updates on the Splunk Community!

#### Autoscaling Kubernetes Workloads with Splunk

About What if you had the ability to scale your Kubernetes pods as a result of information output from Splunk? ...

#### Discover SplunkTrust and MVP Articles, Instant Translation, and More on Splunk ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

#### Integrating Kubernetes and Splunk Observability Cloud

We need end-to-end insight into our application environments to confidently ensure everything is up and ...