topic How to sort groups by group total in Splunk Search

How to sort groups by group total

vtalanki — Mon, 14 Oct 2019 20:33:17 GMT

Hi,

I want to display the count of occurrences of logline* for each user per date but sort the groups by total count

My data is something like:

user1 10/10/2019 logline1
user1 10/10/2019 logline2
user1 10/10/2019 logline3
user1 10/11/2019 logline4
user1 10/11/2019 logline5
user2 10/10/2019 logline6
user2 10/11/2019 logline1
user2 10/11/2019 logline2
user2 10/11/2019 logline3
user2 10/11/2019 logline4
user2 10/12/2019 logline5
user2 10/13/2019 logline6
user2 10/13/2019 logline7
user3 10/10/2019 logline1
user3 10/10/2019 logline2
user3 10/11/2019 logline3
user3 10/11/2019 logline4
user3 10/12/2019 logline5
user3 10/12/2019 logline6
user3 10/12/2019 logline7

I want to display the results like:

user2 10/10/2019 1
10/11/2019 4
10/12/2019 1
10/13/2019 2
user3 10/10/2019 2
10/11/2019 2
10/12/2019 3
user1 10/10/2019 3
10/11/2019 2

Here user2 has total 10 entries, user3 has 7 and user1 has 5. so the output should be sorted that way.

Re: How to sort groups by group total

woodcock — Mon, 14 Oct 2019 23:24:29 GMT

Like this:

| makeresults 
| eval raw="user1 10/10/2019 logline1
user1 10/10/2019 logline2
user1 10/10/2019 logline3
user1 10/11/2019 logline4
user1 10/11/2019 logline5
user2 10/10/2019 logline6
user2 10/11/2019 logline1
user2 10/11/2019 logline2
user2 10/11/2019 logline3
user2 10/11/2019 logline4
user2 10/12/2019 logline5
user2 10/13/2019 logline6
user2 10/13/2019 logline7
user3 10/10/2019 logline1
user3 10/10/2019 logline2
user3 10/11/2019 logline3
user3 10/11/2019 logline4
user3 10/12/2019 logline5
user3 10/12/2019 logline6
user3 10/12/2019 logline7"
| makemv delim="
" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<user>\S+)\s+(?<date>\S+)\s+(?<logline>\S+)$"
| eval _time = strptime(date, "%m/%d/%Y")
| sort 0 - _time

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| bin _time span=1d
| eval time = strftime(_time, "%m/%d/%Y")
| stats count BY user time
| stats list(time) AS time list(count) AS count BY user

Re: How to sort groups by group total

sandeepmakkena — Wed, 16 Oct 2019 00:47:25 GMT

| eval Mdate = strftime(_time, "%Y-%m-%d") 
| stats count by user,Mdate
| stats values(Mdate), values(count) by user
| sort -values(c)

hope this helps, Thanks!