Splunk Search

How to count by DIFFERENT values of a field?

JustRoot
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,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
1 Solution

pradeepkumarg
Influencer

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

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

View solution in original post

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
0 Karma

woodcock
Esteemed Legend

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

0 Karma

kmorris_splunk
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.

JustRoot
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

renjith_nair
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
Happy Splunking!

JustRoot
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

renjith_nair
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
Happy Splunking!
0 Karma

pradeepkumarg
Influencer

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

pradeepkumarg
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...