I have the following situation:
some commands | table Type, Value which results in:
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":
E, 9 <- 5+5-(1+0)
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?
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")
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
Thank you! That was exactly what I was looking for!
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