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

[Lindaiyu]

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

[somesoni2 · Accepted solution]

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

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

[Lindaiyu]

it works and thank you very much for you help!

[woodcock]

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

[Lindaiyu]

thank you very much for you help!

[jplumsdaine22]

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/

[Lindaiyu]

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

[sundareshr]

Try this

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

[Lindaiyu]

Thank you very much!