Splunk Search

How can I search by a count of DIFFERENT values for a field?

New Member

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

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

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

0 Karma

Influencer
0 Karma