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!
Please share some sample data so we can figure out how to handle the mv field.
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
Thank you @rjthibod ! Forgot to paste a sample, but your solution worked exactly as I wanted it to!
Slight adjustment to eliminate an unnecessary pass of the data by
eventstats, and to avoid leaving a variable named as
...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