Archive

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
``````
SplunkTrust

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.

SplunkTrust

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
``````
.conf21 CFS Extended through 5/20!

Don't miss your chance to share your Splunk wisdom in-person or virtually at .conf21!Call for Speakers hasbeen extended throughThursday, 5/20! Submit Now! >

Get Updates on the Splunk Community!