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

[langlv]

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.

Could you guys please help me on this case ?

Thank you very much.
Lang

[somesoni2]

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

[DalJeanis]

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

[ehudb · Accepted solution]

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         

[langlv]

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

Thanks ehudb.

[DalJeanis]

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

[ronekarleone]

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