Splunk Search
Highlighted

How do I group two fields (multivalued)?

New Member

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

0 Karma
Highlighted

Re: How do I group two fields (multivalued)?

Path Finder
just | stats values(Group) as Group by User should be enough
0 Karma
Highlighted

Re: How do I group two fields (multivalued)?

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.

0 Karma
Highlighted

Re: How do I group two fields (multivalued)?

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

View solution in original post

0 Karma
Highlighted

Re: How do I group two fields (multivalued)?

New Member

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

It works perfect that way! Thanks a lot!

0 Karma
Highlighted

Re: How do I group two fields (multivalued)?

Ultra Champion

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

0 Karma