Splunk Search

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

soundchaos
Path Finder

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...

Tags (5)
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

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.

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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
Path Finder

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 😕

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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

soundchaos
Path Finder

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.

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...