Hi,
let's say we have events with fields like:
Event A:
payload.productName1:
payload.productName2:
Event B:
payload.productName2:
payload.productName3:
How can I get the table below with one single "stats count By"?
product | eventCount
payload.productName1: 1
payload.productName2: 2
payload.productName:3 1
I thought of an approach to create a multivaluefield that includes all fieldnames like payload.productName* (the exact amount and names are unkown so I have to use a pattern) and group by this new MV afterwards
MV={payload.productName1, payload.productName2, payload.productName3}
...| stats count by MV
Thanks in advance
Your data is JSON so just used spath
like this:
| makeresults
| eval raw = "{\"buildersAmount\":1,\"campLevel\":2,\"payload\":{\"hordeComposition\":{\"Config_Zombie_Chubby\":4,\"Config_Zombie_Exploder\":4,\"Config_Zombie_Floater\":1,\"Config_Zombie_Walker\":9}},\"timestamp\":1495017137}:::{\"buildersAmount\":1,\"campLevel\":2,\"payload\":{\"hordeComposition\":{\"Config_Zombie_Chubby\":1,\"Config_Zombie_Exploder\":2,\"Config_Zombie_Floater\":3,\"Config_Zombie_Walker\":4}},\"timestamp\":1495017137}"
| makemv delim=":::" raw
| mvexpand raw
| rename raw AS _raw
| rename COMMENT AS "Everything above fakes your data; everything below is your solution"
| spath
| fields - _time _raw
| fields payload*
| addtotals row=f col=t
| tail 1
| transpose
| rename column AS Product "row 1" AS Total
| rex field=Product mode=sed "s/^payload\.//"
Like this (you might need to fix the RegEx
pattern):
YOUR BASE SEARCH HERE | rex max_match=0 "(?<product>payload\.\S+)" | stats count AS eventCount BY product
Thanks for your answer, but this approach does not work for me. I tried to create a search example to check:
index=_internal | head 1
| eval payload.productName1="A"
| eval payload.productName2="B"
| rex max_match=0 "(?<product>payload\.\S+)"
| table product, payload.productName1, payload.productName2
Unfortunately I'm not familiar with rex commands
Why are you not testing it on your real data? Your test approach is wrong in several ways. This proves that it does work:
| makeresults
| eval _raw = "In the beginning God created the heavans and the Earth payload.productName1=\"A\" Four score and sever years ago payload.productName2=\"B\" We the people in order to form a more perfect union"
| rex max_match=0 "(?<product>payload\.productName\d+)"
| rex max_match=0 "(?<MV>payload\.productName[^:]*:)"
I tried it out on the real data, but without success. So I wanted to test a general example and obviously I did it wrong 🙂
So my real _raw data looks like. Perhaps you can help directly on that:
{"buildersAmount":1,"campLevel":2,"payload":{"hordeComposition":{"Config_Zombie_Chubby":4,"Config_Zombie_Exploder":4,"Config_Zombie_Floater":1,"Config_Zombie_Walker":9}},"timestamp":1495017137}
The bold parts are the ones I want to extract and group by.
Now /that/ is a totally different beast. See my new answer.
This all works, but it does not help to solve my problem. I'm sure it's simply because I not able to describe my problem/need properly. But I found an ugly workaround to answer the problem... 🙂
Thanks anyway for your input!
You could try extracting all these fields and then get count-
| rex max_match=0 "(?<Product>payload\.productName\d+)"
So, to use OP's field name, include the colon in the value and get anything before the colon that isn't a colon:
| rex max_match=0 "(?<MV>payload\.productName[^:]*:)"
| stats count by MV
Thanks for your answers, but the commands does not work for me. I created an easy search example to check it
index=_internal | head 1
| eval payload.productName1="A"
| eval payload.productName2="B"
| rex max_match=0 "(?<Product>payload\.productName\d+)"
| rex max_match=0 "(?<MV>payload\.productName[^:]*:)"
| table MV, Product, payload.productName1, payload.productName2
Unfortunately I'm not familiar with rex commands
Here's a different way that should work for you.
| makeresults count=4
| streamstats count as recno
| eval payload.productName1=if(recno<4,"A",null())
| eval payload.productName2=if(recno%2=0,"B",null())
| eval payload.productName3=if(recno=4,"C",null())
| eval payload.productNameX=if(recno>2,"D",null())
| rename COMMENT as "The above just creates test data for you"
| table payload.*
| eval temp=1
| untable temp fieldname fieldvalue
| stats count by fieldname
The prior version was assuming the key values pairs existed in the _raw data, like this...
| makeresults count=2
| streamstats count as recno
| eval _raw = if(recno=1,"payload.productName1=A payload.productName2=B","payload.productName3=C payload.productName2=B")
| rex max_match=0 "(?<product>payload\.\S+)="
| stats count by product
This all works, but it does not help to solve my problem. I'm sure it's simply because I not able to describe my problem/need properly. But I found an ugly workaround to answer the problem... 🙂
Thanks anyway for your input!