Splunk Search

What is the best way to add a set of values in a table, subtract another set of values, then append the result back to the table?

Stevelim
Communicator

I have the following situation:

some commands | table Type, Value which results in:

Type, Value

A, 5
B, 5
C, 1
D, 0

I need to add up A and B, subtract C and D, then append the result back to the table like this as another value Type="E":

Type, Value

A, 5
B, 5
E, 9 <- 5+5-(1+0)

Any ideas?

1 Solution

jplumsdaine22
Influencer

Appendpipe is one solution. It allows you to append to reporting commands. In this case, instead of using table Type Value use statsto sum the values of your types, then use eval to convert C & D to negative values. Then use appendpipe to get the total of your column

... |stats sum(Value) as Value by Type | eval Value=if((type=="C" OR type=="D"),Value*-1,Value) | appendpipe [stats sum(Value) as Value | eval Type = "E"] | search NOT (Type="C" OR Type="D")

You can also use addcoltotals (a simpler method, but appendpipe allows you to do more than simple sum, so it's worth knowing)

... |stats sum(Value) as Value by Type | eval Value=if((type=="C" OR type=="D"),Value*-1,Value) | addcoltotals Value labelfield=Type label=E | search NOT (Type="C" OR Type="D")

See: http://docs.splunk.com/Documentation/Splunk/6.3.3/SearchReference/Appendpipe
See: http://docs.splunk.com/Documentation/Splunk/6.3.3/SearchReference/Addcoltotals
And if you're not familar with the eval function used in these examples see: http://docs.splunk.com/Documentation/Splunk/6.3.3/SearchReference/CommonEvalFunctions

View solution in original post

javiergn
Super Champion

I tried this generic option that should work for any number of values.
I have tested it with your example and it seems to work fine:

| inputcsv mycsv.csv
| sort Type
| streamstats count as n
| eventstats median(n) as median
| eval ValueSign = if(median > n, Value, -Value)
| addcoltotals
| fillnull value=E
| where n < median
| fields Type, ValueSign
| rename ValueSign as Value
0 Karma

jplumsdaine22
Influencer

Appendpipe is one solution. It allows you to append to reporting commands. In this case, instead of using table Type Value use statsto sum the values of your types, then use eval to convert C & D to negative values. Then use appendpipe to get the total of your column

... |stats sum(Value) as Value by Type | eval Value=if((type=="C" OR type=="D"),Value*-1,Value) | appendpipe [stats sum(Value) as Value | eval Type = "E"] | search NOT (Type="C" OR Type="D")

You can also use addcoltotals (a simpler method, but appendpipe allows you to do more than simple sum, so it's worth knowing)

... |stats sum(Value) as Value by Type | eval Value=if((type=="C" OR type=="D"),Value*-1,Value) | addcoltotals Value labelfield=Type label=E | search NOT (Type="C" OR Type="D")

See: http://docs.splunk.com/Documentation/Splunk/6.3.3/SearchReference/Appendpipe
See: http://docs.splunk.com/Documentation/Splunk/6.3.3/SearchReference/Addcoltotals
And if you're not familar with the eval function used in these examples see: http://docs.splunk.com/Documentation/Splunk/6.3.3/SearchReference/CommonEvalFunctions

Stevelim
Communicator

Thank you! That was exactly what I was looking for!

0 Karma

renjith_nair
Legend

Is there any categorization available for A,B and C,D or any other field which can be used to group A,B and C,D?

---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma
Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

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