Splunk Count 2 fields in single query

[Pmeiring]

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

[ITWhisperer · Accepted solution]

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

[Pmeiring]

Thanks ITWhisperer

Exactly what I was looking for.

[richgalloway]

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)

[Pmeiring]

Hi Richgalloway, 

I've tried the "stats count(Country)" but not data is displayed under statistics.