Splunk Search

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

langlv
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.

Could you guys please help me on this case ?

Thank you very much.
Lang

Tags (1)
0 Karma
1 Solution

ehudb
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         

View solution in original post

somesoni2
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

DalJeanis
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
0 Karma

ehudb
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         

langlv
Engager

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

Thanks ehudb.

0 Karma

DalJeanis
Legend

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

0 Karma

ronekarleone
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 
0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

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

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...