topic Re: How to write a search to get an accurate count of fields with the same name in a single event? in Splunk Search

How to write a search to get an accurate count of fields with the same name in a single event?

Lindaiyu — Wed, 22 Jun 2016 11:29:45 GMT

Hello Splunkers,

Here is my sample event:

ID=000, GROUP="A", GROUP="B", TYPE="NA"
ID=001, GROUP="A", TYPE="NB"

The problem is when I use the search command:

    ...|stats count by GROUP

I will get this result in Splunk:

GROUP          count
A              2

While what I really want to get is:

GROUP          count
A              2
B              1

I think the problem is that the field GROUP can have multiple values per event, and Splunk just takes the first as its value. Since I can't change the source data, what can I do with this situation?

Thank you very much for your attention.
Daiyu

Re: How to write a search to get an accurate count of fields with the same name in a single event?

sundareshr — Wed, 22 Jun 2016 12:10:12 GMT

Try this

.... |  rex max_match=0 "GROUP=\"(?<group>[^\"]+)" | mvexpand group | stats count by group

Re: How to write a search to get an accurate count of fields with the same name in a single event?

jplumsdaine22 — Wed, 22 Jun 2016 12:21:54 GMT

You can try extracting GROUP as a multivalued field with the rex command. This may work:

 <your search> | rex max_match=0 "GROUP=\"(?<group>[^\"])" | mvexpand group | stats count by group

The rex command (http://docs.splunk.com/Documentation/Splunk/6.4.1/SearchReference/Rex) will extract a new field. Setting max_match to 0 means rex will not stop at the first match, and it will combine the results in a multivalued field. In your example, your events will now look like this:

 ID       TYPE    group
-------------------------------
 000       NA       A
                    B
--------------------------------
 001       NB       A

mvexpand (http://docs.splunk.com/Documentation/Splunk/6.4.1/SearchReference/Mvexpand) will split the multivalue fields, so now you will have three events, like so:

 ID       TYPE    group
-------------------------------
 000       NA       A
-------------------------------
 000       NA       B
--------------------------------
 001       NB       A

Now your stats commands will work the way you want. For regex help try https://regex101.com/

Re: How to write a search to get an accurate count of fields with the same name in a single event?

woodcock — Wed, 22 Jun 2016 12:23:54 GMT

If using rex then add max_match=0; if using props.conf, then add MV_ADD=1

Re: How to write a search to get an accurate count of fields with the same name in a single event?

somesoni2 — Wed, 22 Jun 2016 16:08:20 GMT

Give this a try as well (in-line with search)

your base search | table _raw | extract kvdelim="=" mv_add=t | stats count by group

Re: How to write a search to get an accurate count of fields with the same name in a single event?

Lindaiyu — Fri, 24 Jun 2016 13:50:59 GMT

it works and thank you very much for you help!

Re: How to write a search to get an accurate count of fields with the same name in a single event?

Lindaiyu — Fri, 24 Jun 2016 13:51:37 GMT

thank you very much for you help! I really learn something!

Re: How to write a search to get an accurate count of fields with the same name in a single event?

Lindaiyu — Fri, 24 Jun 2016 13:51:47 GMT

thank you very much for you help!

Re: How to write a search to get an accurate count of fields with the same name in a single event?

Lindaiyu — Fri, 24 Jun 2016 13:52:03 GMT

Thank you very much!