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!
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
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
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
Thank you @rjthibod ! Forgot to paste a sample, but your solution worked exactly as I wanted it to!
Please share some sample data so we can figure out how to handle the mv field.
@jkat54 Right, sorry about that! Thankfully @rjthibod answered!