How to create a multivaluefield from fieldnames with a specific pattern?

[HeinzWaescher]

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

[woodcock]

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\.//"

[woodcock]

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

[HeinzWaescher]

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

[woodcock]

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[^:]*:)"

[HeinzWaescher]

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.

[woodcock]

Now /that/ is a totally different beast. See my new answer.

[HeinzWaescher]

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!

[dineshraj9]

You could try extracting all these fields and then get count-

| rex max_match=0  "(?<Product>payload\.productName\d+)"

[DalJeanis]

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

[HeinzWaescher]

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

[DalJeanis]

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

[HeinzWaescher]

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!