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?

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?

Tags (3)
1 Solution
Influencer

Appendpipe is one solution. It allows you to append to reporting commands. In this case, instead of using `table Type Value` use `stats`to 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
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

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)
| fillnull value=E
| where n < median
| fields Type, ValueSign
| rename ValueSign as Value
``````
Influencer

Appendpipe is one solution. It allows you to append to reporting commands. In this case, instead of using `table Type Value` use `stats`to 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
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

Communicator

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

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 🙂
Get Updates on the Splunk Community!

#### .conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

#### Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

#### Troubleshooting the OpenTelemetry Collector

In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...