How do I change the axis of a statistic table when using |stats count as "field" multiple times

[igordon]

My current search is:

index=ad memberOf=role1 OR memberOf=role2 NOT memberOf=role3 | stats count as "User Group A" | appendcols [search memberOf=role2 OR memberOf=role3 NOT memberOf=role1 | stats count as "User Group B"] | appendcols [search memberOf=role1 OR memberOf=role3 NOT memberOf=role2 | stats count as "User Group C"]

Current output looks like:

User Group A |User Group B|User Group C
100|200|300

Desired output

User Groups Count
User Group A 100
User Group B 200
User Group C 300

[DalJeanis]

Why not something like this...

| makeresults count=50 | eval myrole= random()%3 + 1 | eval memberOf="role".myrole 
| rename COMMENT as "The above just generates test data."

| table memberOf
| eval "Group A" = If(memberOf="role1" OR memberOf="role2",1,0)
| eval "Group B" = If(memberOf="role2" OR memberOf="role3",1,0)
| eval "Group C" = If(memberOf="role1" OR memberOf="role3",1,0)
| Stats sum(*) as *
| eval temp = 1
| untable temp "User Groups" Count
| fields - temp

Sample output ...

User Groups  Count
Group A      41
Group B      36
Group C      23

Also, as a cross-check you could add this just before the stats command

| eval {memberOf} = 1

...and you'd also get something like these lines...

role1     27
role2     14
role3      9

[adonio]

try this, maybe it will help also with performance as there are no | appendcols and subsearches:

index=ad memberOf=role1 OR memberOf=role2 OR memberOf=role3
| stats count(eval(meberOf="role1")) as "User Group A" count(eval(memberOf="role2")) as"User Group B" count(eval(memberOf="role3")) as "User Group C" 
| transpose | rename column as "User Group" row1 as "User Count"

hope it helps

[somesoni2]

Try this

index=ad memberOf=role1 OR memberOf=role2 OR memberOf=role3
| eval User_Group=case((memberOf="role1" OR memberOf="role2") AND NOT memberOf="role3","User Group A", (memberOf="role2" OR memberOf="role3") AND NOT memberOf="role1","User Group B", (memberOf="role3" OR memberOf="role1") AND NOT memberOf="role2","User Group C",true(),"Unknown") 
| stats count as Count by User_Group | rename User_Group as "User Groups"