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
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
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
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?
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?
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 🙂
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).
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
Hi HeinzWaescher
try
| stats sum(product*) AS total_products
Bye.
Giuseppe