How can I show segments of user login counts sorted by groups of ten per segment (in a pie chart)?

[robettinger]

Hi,

I am trying to get a pie chart which shows the Top 10 users logon count as a single slice, then the next 10 following users (one per slice) and then the others....

Is there any way to do this?

Thank you!

[niketn · Accepted solution]

@robettinger, please try the following.

1) First get top 20 users then
2) Use eventstats to get a list of Users against all 20 events as mv_users (we will require only the 10th value later)
3) Use accum to add counter to 1-20 results
4) Use accum to get cumulative total for all 20 events as Total field (we will required only cumulative total till 10th value later)
5) Use search to filter results 1-9 since we are interested in fields 10th onward.
6) For the first row of the result i.e. 10th value
(i) Get the user name as multivalued list of users from 1 to 10th index of mv_users list, generated in step 2, using mvindex() fuction. Otherwise leave as users.
(ii) Get the cumulative total from 1-10 using Total field generated in Step 4 as count. Otherwise leave as count.
7) Use | table users count to prepare data for pie chart.

Following is the query:

<YourBaseSearch>
| top 20 users
| table users count
| eventstats list(users) as mv_users
| eval counter=1
| accum counter
| accum count as Total
| search counter>9
| eval users=if(counter=10,mvindex(mv_users,0,9),users)
| eval count=if(counter=10,Total,count)
| table users count

PS: Since my logic is working with accum, I am sure there might be a better way to do this using streamstats.

[gcusello]

Hi
I'm not sure to correctly understood your answer, try something like this

index=your_index 
| stats count by user 
| sort -count 
| bin count AS cnt span=10 
| stats sum(count) AS count by cnt 
| sort cnt

Bye.
Giuseppe