Splunk Search

How do I group two fields (multivalued)?

philippbloch
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

0 Karma
1 Solution

FrankVl
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

FrankVl
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
0 Karma

philippbloch
Loves-to-Learn Lots

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

It works perfect that way! Thanks a lot!

0 Karma

FrankVl
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

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

FrankVl
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
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...