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
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
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
I changed the "mvsplit" to "split" in the second eval.
It works perfect that way! Thanks a lot!
You're welcome! And indeed, it should be split not mvsplit, my bad. I've corrected that in my answer as well 🙂
just | stats values(Group) as Group by User should be enough
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.