Splunk Search

## How do I group two fields (multivalued)?

Loves-to-Learn Lots

Hello,

I have the following table:

``````User           Group
-------------  -------------
User_A         Group_A
-------------  -------------
User_A         Group_B
-------------  -------------
User_A         Group_C
-------------  -------------
User_B         Group_A
-------------  -------------
User_B         Group_B
-------------  -------------
User_B         Group_C
-------------  -------------
User_C         Group_C
-------------  -------------
User_D         Group_D
-------------  -------------
User_D         Group_E
``````

Which I want to group as follows:

``````User           Group
-------------  -------------
User_A         Group_A
User_B         Group_B
Group_C
-------------  -------------
User_C         Group_C
-------------  -------------
User_D         Group_D
Group_E
``````

I tried:

``````<my results> | stats values(Group) as Group by User | stats values(User) as User by Group
``````

Any suggestions how to achieve this?

Thanks,
-Philipp

Tags (4)
1 Solution
Ultra Champion

Your attempt is close, but you need to apply a little trick to let the second stats work (as the "by Group" won't have the desired effect when Group is a multi valued field). So you first need turn Group into a single valued field, then do the second stats and then split Group out again. Since values() returns a sorted result, this should work and result in the same concatenated strings for users that have the same set of groups.

``````<my results>
| stats values(Group) as Group by User
| eval Group=mvjoin(Group,"|")
| stats values(User) as User by Group
| eval Group=split(Group,"|")
``````

This has the following intermediate results:

After the first stats and eval:

`````` User           Group
-------------  -------------
User_A         Group_A|Group_B|Group_C
-------------  -------------
User_B         Group_A|Group_B|Group_C
-------------  -------------
User_C         Group_C
-------------  -------------
User_D         Group_D|Group_E
``````

After the second stats:

`````` User           Group
-------------  -------------
User_A         Group_A|Group_B|Group_C
User_B
-------------  -------------
User_C         Group_C
-------------  -------------
User_D         Group_D|Group_E
``````

After the second eval:

`````` User           Group
-------------  -------------
User_A         Group_A
User_B         Group_B
Group_C
-------------  -------------
User_C         Group_C
-------------  -------------
User_D         Group_D
Group_E
``````
Ultra Champion

Your attempt is close, but you need to apply a little trick to let the second stats work (as the "by Group" won't have the desired effect when Group is a multi valued field). So you first need turn Group into a single valued field, then do the second stats and then split Group out again. Since values() returns a sorted result, this should work and result in the same concatenated strings for users that have the same set of groups.

``````<my results>
| stats values(Group) as Group by User
| eval Group=mvjoin(Group,"|")
| stats values(User) as User by Group
| eval Group=split(Group,"|")
``````

This has the following intermediate results:

After the first stats and eval:

`````` User           Group
-------------  -------------
User_A         Group_A|Group_B|Group_C
-------------  -------------
User_B         Group_A|Group_B|Group_C
-------------  -------------
User_C         Group_C
-------------  -------------
User_D         Group_D|Group_E
``````

After the second stats:

`````` User           Group
-------------  -------------
User_A         Group_A|Group_B|Group_C
User_B
-------------  -------------
User_C         Group_C
-------------  -------------
User_D         Group_D|Group_E
``````

After the second eval:

`````` User           Group
-------------  -------------
User_A         Group_A
User_B         Group_B
Group_C
-------------  -------------
User_C         Group_C
-------------  -------------
User_D         Group_D
Group_E
``````
Loves-to-Learn Lots

I changed the "mvsplit" to "split" in the second eval.

It works perfect that way! Thanks a lot!

Ultra Champion

You're welcome! And indeed, it should be split not mvsplit, my bad. I've corrected that in my answer as well 🙂

Path Finder
``````just | stats values(Group) as Group by User should be enough
``````
Ultra Champion

No it isn't, as that doesn't group the user field into a multi valued field for users that have the same set of groups.

Get Updates on the Splunk Community!

#### The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

#### Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

#### Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...