Splunk Search

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

jnicoara11
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,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!

0 Karma

woodcock
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

pradeepkumarg
Influencer
0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!