Splunk Search
Highlighted

How to count by DIFFERENT values of a field?

Path Finder

I am trying to create a query that monitors logins. The logic is that it should alert me if a user (UserId) attempts to login from 2 different cities (City) in a 60 minute time frame or span. So far, I have:

index=whatever sourcetype=whatever | nslookup(ClientIPAddress,ipaddress)
| iplocation ClientIPAddress | stats count(City) as count
status by UserId | where count_status > 1

This query returns a count but it's of all the logins. So for example, if a user has signed in 100 times in the city of Denver but no other city in the last 60 minutes, it'll return 100 instead of 1. Any tips or suggestions? Thanks!

0 Karma
Highlighted

Re: How to count by DIFFERENT values of a field?

Influencer

Use dc instead of count to get the distinct count of cities

| stats dc(City) as countstatus by UserId | where countstatus > 1

View solution in original post

Highlighted

Re: How to count by DIFFERENT values of a field?

Path Finder

great! do you know how I can add span to this and still use stats? I don't want to use timechart or chart.

0 Karma
Highlighted

Re: How to count by DIFFERENT values of a field?

Influencer

This will give you in 60 minute spans

| bucket span=60m _time|stats dc(City) as count_status by UserId,_time | where count_status > 1
0 Karma
Highlighted

Re: How to count by DIFFERENT values of a field?

SplunkTrust
SplunkTrust

You can use dc to get the distinct count - http://docs.splunk.com/Documentation/Splunk/7.1.1/SearchReference/Aggregatefunctions#Description_3

index=whatever sourcetype=whatever | nslookup(ClientIPAddress,ip_address)
| iplocation ClientIPAddress | stats dc(City) as count_status by UserId | where count_status > 1
Highlighted

Re: How to count by DIFFERENT values of a field?

Path Finder

great! do you know how I can add span to this and still use stats? I don't want to use timechart or chart.

0 Karma
Highlighted

Re: How to count by DIFFERENT values of a field?

SplunkTrust
SplunkTrust
 index=whatever sourcetype=whatever | nslookup(ClientIPAddress,ip_address)
 | iplocation ClientIPAddress | bucket span=10m _time|stats dc(City) as count_status by UserId,_time | where count_status > 1
0 Karma
Highlighted

Re: How to count by DIFFERENT values of a field?

Splunk Employee
Splunk Employee

You can change count to dc (for distinct count). This will give you the distinct number of cities that each user has logged in from.

Highlighted

Re: How to count by DIFFERENT values of a field?

Path Finder

great! do you know how I can add span to this and still use stats? I don't want to use timechart or chart.

0 Karma
Highlighted

Re: How to count by DIFFERENT values of a field?

Esteemed Legend

Like this:

index=whatever sourcetype=whatever
| nslookup(ClientIPAddress,ip_address)
| iplocation ClientIPAddress
| streamstats time_window=60m dc(City) as HoulyCityCount by UserId
| where HoulyCityCount > 1
0 Karma