Product of a Column

[kalverra]

I'm trying to find a simple way to calculate the product of a single column, e.g.

value_a

0.44

0.25

0.67

Ideally, I could use something like this:

| stats product(value_a)

 But this doesn't seem possible.

[kalverra · Accepted solution]

Thank you everyone that replied with helpful comments. I however discovered a math trick with logarithms to accomplish this far more succinctly.

| eval log_value = ln(value_a)
| stats sum(log_value) AS log_sum
| eval product = exp(log_sum)

[tscroggins]

Now you're Splunking! Watch out for approximation errors!

2 * 3 * 4 = 24
exp(ln(2) + ln(3) + ln(4)) ~= 23.999999999999993

[yuanliu]

Another approach is to (almost) use stats:-) as you original proposed.  With a little help of foreach.

| stats values(value_a) as value_a
| eval product = 1
| foreach value_a mode=multivalue
    [eval product = product * <<ITEM>>]

Here is an emulation you can play with and compare with real data:

| makeresults format=csv data="value_a
0.44
0.25
0.67"
``` data emulation above ```

The above search gives this result

value_a	product
0.25 0.44 0.67	0.074

[tscroggins]

I would use list() instead of values() to prevent removal of duplicates and wrap the product in exact() to prevent rounding errors:

| makeresults format=csv data="value_a
0.44
0.25
0.67
0.44" 
| stats list(value_a) as value_a
| eval "product(value_a)"=1
| foreach value_a mode=multivalue [ eval "product(value_a)"=exact('product(value_a)' * <<ITEM>>) ]
| table "product(value_a)"

 =>

product(value_a)

0.032428

[ITWhisperer]

Correct, you can't multiply down columns, but you can with rows, so depending on how many events and columns you want the product of, you could do something like this

| makeresults format=csv data="value_a,value_b,otherValue
0.44,1,10
0.25,2,20
0.67,3,30"
| appendpipe
    [| transpose 0
    | eval product = 1
    | foreach row*
        [eval product = product * '<<FIELD>>']
    | eval {column} = product
    | stats values(value_a) as value_a values(value_b) as value_b]