Splunk Search

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

igordon
New Member

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

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

adonio
Ultra Champion

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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"
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...