- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi All,
I need assistance with counting two fields in a single query. I'm trying modify an existing alert, to exclude white noise. I'm trying to achieve the following, if a country count is more than 10, or an Username field is more than 5, they should be excluded.
The query compiles but no data is presented, I suspect the stats count usage is inaccurate.
Example of Query - what I'm trying to achieve
source="**********.log" "NOTICE Passed-Authentication: Authentication succeeded" earliest=-30d@d latest=now
| iplocation src_ip
| stats count as Country_Count by Country , count as Username_Count by User_Name
| where (Username_Count < 5 OR Country_Count < 10)
Output of Data - Country
Country Country_count
United States 8
Eswatini 9
Russia 1
Mozambique 1
Netherlands 1
Zambia 6
Output of Data - Username
User_Name Username_Count
abc@domain.com 1
abc2@domain.com 1
abc3@domain.com 1
abc4@domain.com 4
abc5@domain.com 1
abc6@domain.com 2
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

source="**********.log" "NOTICE Passed-Authentication: Authentication succeeded" earliest=-30d@d latest=now
| iplocation src_ip
| stats count by Country, User_Name
| eventstats sum(count) as Country_Count by Country
| eventstats sum(count) as Username_Count by User_Name
| where (Username_Count <= 5 AND Country_Count <= 10)
Your description say you want to exclude where either country count is more than 10 or if username count is more than 5, which means include when username count <= 5 and country count <= 10
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

source="**********.log" "NOTICE Passed-Authentication: Authentication succeeded" earliest=-30d@d latest=now
| iplocation src_ip
| stats count by Country, User_Name
| eventstats sum(count) as Country_Count by Country
| eventstats sum(count) as Username_Count by User_Name
| where (Username_Count <= 5 AND Country_Count <= 10)
Your description say you want to exclude where either country count is more than 10 or if username count is more than 5, which means include when username count <= 5 and country count <= 10
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks ITWhisperer
Exactly what I was looking for.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content


The stats command can only have a single by clause. Have you tried this query?
source="**********.log" "NOTICE Passed-Authentication: Authentication succeeded" earliest=-30d@d latest=now
| iplocation src_ip
| stats count(Country) as Country_Count, count(User_Name) as Username_Count
| where (Username_Count < 5 OR Country_Count < 10)
If this reply helps you, Karma would be appreciated.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Richgalloway,
I've tried the "stats count(Country)" but not data is displayed under statistics.
