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

[kalik]

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!

[rjthibod · Accepted solution]

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]

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

[kalik]

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

[jkat54]

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

[kalik]

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