topic Re: Product of a Column in Splunk Search

Product of a Column

kalverra — Mon, 30 Dec 2024 21:16:54 GMT

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.

Re: Product of a Column

ITWhisperer — Tue, 31 Dec 2024 00:19:33 GMT

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]

Re: Product of a Column

yuanliu — Tue, 31 Dec 2024 01:43:48 GMT

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

Re: Product of a Column

tscroggins — Tue, 31 Dec 2024 18:32:49 GMT

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

Re: Product of a Column

kalverra — Fri, 07 Feb 2025 16:54:00 GMT

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)

Re: Product of a Column

tscroggins — Sat, 08 Feb 2025 17:19:56 GMT

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

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