Splunk Search

Show individual counts for each item in a multivalue field in a single stats row

kalik
Explorer

I am trying to get a count for individual items in a multivalue field. Here's my current search:

| stats count(_time) as Full_Count, values(user) as Users, by SourceIP

And here's the output I'm trying to get:

 ____________________________________
| SourceIP    | Full_Count | Users   |
|_____________|____________|_________|
| 10.10.10.10 | 10         | Jack:3  |
|             |            | John:5  |
|             |            | Mick:2  |
| 10.20.20.20 | 50         | Craig:1 |
|             |            | Mark:40 |
|             |            | Peter:9 |
| 10.30.30.30 | 5          | Jack:4  |
|             |            | Greg:1  |
|_____________|____________|_________|

In my search it's going to be the count of failed logins for each user grouped by the source IP of the login.

I checked some other topics but I couldn't really find an answer - I've got an idea that I might have to use two stats commands to first get the count for each user and then use the results in a second search but I fear it might show incorrect results as for example Jack is appearing for 2 different IP's.

Any guidance will be much appreciated!

Tags (2)
0 Karma
1 Solution

rjthibod
Champion

Without having more details about your search, here is a solution based solely on what you shared.

| stats count as count by user SourceIP | eventstats sum(count) as Full_Count by SourceIP | eval User_cnt = user + ":" + count | stats max(Full_Count) as Full_Count, values(User_cnt) as Users by SourceIP

View solution in original post

rjthibod
Champion

Without having more details about your search, here is a solution based solely on what you shared.

| stats count as count by user SourceIP | eventstats sum(count) as Full_Count by SourceIP | eval User_cnt = user + ":" + count | stats max(Full_Count) as Full_Count, values(User_cnt) as Users by SourceIP

DalJeanis
SplunkTrust
SplunkTrust

Slight adjustment to eliminate an unnecessary pass of the data by eventstats, and to avoid leaving a variable named as count ...

 ...your search that creates one record per event...    
| table SourceIP user 
| stats count as mycount by user SourceIP 
| eval User_cnt=user.": ".mycount
| stats sum(mycount) as Full_Count, values(User_cnt) as Users by SourceIP
0 Karma

kalik
Explorer

Thank you @rjthibod ! Forgot to paste a sample, but your solution worked exactly as I wanted it to!

0 Karma

jkat54
SplunkTrust
SplunkTrust

Please share some sample data so we can figure out how to handle the mv field.

kalik
Explorer

@jkat54 Right, sorry about that! Thankfully @rjthibod answered!

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