Sorting the stats values results by count, and include count in results

[soundchaos]

I am trying to get a search result that shows a single IP associated with all of its user agents, but I would like the IP's sorted by the overall amount of hits rather than sorted by numerical order. I would also like to be able to see the count of hits in the end result as well. Thanks in advance!

My current search:

index="logs" source="mywebsite.com" | stats values(cs_User_Agent_) as cs_User_Agent_ by c_ip

Right now it results:

IP: 11.00.00.00

User Agent: 1. Mozilla/5.0...
2. Mozilaa/4.0...

IP: 22.00.00.00

User Agent: 1. Mozilla/5.0...

IP: 33.00.00.00

User Agent: 1. Mozilla/5.0...
2. Mozilaa/4.0...
3. Mozilla/5.0...

I am looking to get results like:

IP: 64.00.00.00 - Count: 13451

User Agent: 1. Mozilla/5.0...
2. Mozilaa/4.0...

IP: 109.00.00.00 - Count: 636

User Agent: 1. Mozilla/5.0...

IP: 72.00.00.00 - Count: 122

User Agent: 1. Mozilla/5.0...
2. Mozilaa/4.0...
3. Mozilla/5.0...

[martin_mueller · Accepted solution]

Try something like this:

index="logs" source="mywebsite.com" | stats count values(cs_User_Agent_) as cs_User_Agent_ by c_ip

You'll get three fields - the IP, the count per IP, and the user agents.

[martin_mueller]

Keep the values(cs_User_Agent_) untouched. That way you get the distinct count and the values, sort by distinct count, throw away the distinct count.

[soundchaos]

If I am doing it right, that lists just the number of agents used, instead of each individual agent? I was hoping to be able to sort exactly like this, except for showing the full text of each user agent, rather than just the number of how many. Sorry that I am using the comment for an entirely different question 😕

[martin_mueller]

You can add dc(cs_User_Agent_) as dc to the stats and run | sort - dc | fields - dc at the end.

[soundchaos]

Thanks! That was way easier than everything I have been trying for the last hour... Would you also have any ideas on how I might sort these results based on how many user agents each IP has, without changing the format of the results? I thought something like | sort -mvcount(cs_User_Agent_) might work, but it does not.