How to sum all values for fields that contain a specific string?

[HeinzWaescher]

Hi,

In the events, I have different fields for the products. How can I easily sum all values for these fields when I don't know all exact names?

productA=
productB=
productC=
...

These examples are not working, but I hope it explains my need:

| stats sum(product*) AS total_products

Or to sum up all values per product

| stats sum(product*) AS sum BY product*

Thanks in advance
Heinz

[javiergn · Accepted solution]

If I understand correctly you have several products per event and you don't know the names beforehand right?

Something like:

Event1: Time=123 ProductA=1 ProductB=10 ProductC=100
Event2: Time=456 ProductA=2 ProductH=20 ProductC=200
Event3: Time=789 ProductD=3 ProductB=30 ProductC=300

And you would like to display:

Event1: Subtotal=111
Event2: Subtotal=222
Event3: Subtotal=333
TOTAL=666

But also:

ProductA_subtotal=3
ProductB_subtotal=40
ProductC_subtotal=600
etc

If that's the case then try:

your base search here
| eval subtotal = 0
| foreach product* [ eval subtotal = subtotal + '<<FIELD>>']
| stats sum(subtotal) as TOTAL, sum(product*) as product*_subtotal

Hope that helps. If not please give us more information (sample data, or something like that).

Thanks,
J

[HeinzWaescher]

Hi,

last month I thought that this approach works. I have a new usecase and I'm facing the problem, that it is working with one single event. But for a search returning more than 1 events it does not work. The subtotal is always 0 after the foreach command. Any ideas why this happens?

[javiergn]

Hi, can't really comment without seeing exactly what your data looks like as I'm not quite sure what you mean.

Would you mind raising a new question so that we can look at the new use case separately rather than working on an already-closed one?

[HeinzWaescher]

Hi,

I just found out that the problem seems to be that my fieldnames contain dots:

 Event1: Time=123 Product.A=1 Product.B=10 Product.C=100
 Event2: Time=456 Product.A=2 Product.H=20 Product.C=200
 Event3: Time=789 Product.D=3 Product.B=30 Product.C=300

But of course I can create a new question 🙂

[javiergn]

Hi, even with dots it still seems to be working fine for me. The dots are renamed to _ automatically but that's all.
Maybe you have to fillnull those empty values you might find so that the subtotal works.

See if the following helps:

your base search
| eval subtotal = 0
| fillnull value=0
| foreach Product* [ eval subtotal = subtotal + '<<FIELD>>']
| eventstats sum(subtotal) as TOTAL, sum(Product*) as Product*_subtotal

This is what I get in my lab (see attached picture):

http://i.imgur.com/btUDzLJ.png

If that doesn't work I would suggest for you to raise a new question and provide as much info as you can (log samples, queries you are running, etc).

[HeinzWaescher]

Hi,
I created a standard example as well, which works fine:

index=_internal | head 10

| eval product.A=1
| eval product.B=2
| eval product.C=3

| eval subtotal = 0
| foreach product* [ eval subtotal = subtotal + '<<FIELD>>']

| table subtotal

But you are right, in my real usecase not every event includes every product.x. Fillnull fixes this problem and foreach is working 🙂 Thanks a lot

[gcusello]

Hi HeinzWaescher
try

| stats sum(product*) AS total_products

Bye.
Giuseppe