Splunk Enterprise

Splunk Count 2 fields in single query

Pmeiring
Explorer

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

Labels (2)
0 Karma
1 Solution

ITWhisperer
Legend
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

View solution in original post

0 Karma

ITWhisperer
Legend
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

View solution in original post

0 Karma

Pmeiring
Explorer

Thanks ITWhisperer

Exactly what I was looking for.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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, an upvote would be appreciated.
0 Karma

Pmeiring
Explorer

Hi Richgalloway, 

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

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!