How to count by DIFFERENT values of a field?

[JustRoot]

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,ip_address)
| 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!

[woodcock]

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

[woodcock]

This solution gives you a sliding 60-minute window instead of rigid every-hour buckets.

[kmorris_splunk]

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.

[JustRoot]

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

[renjith_nair]

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

[JustRoot]

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

[renjith_nair]

 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

[pradeepkumarg · Accepted solution]

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

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

[JustRoot]

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

[pradeepkumarg]

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