Splunk Search

Stats count question

willadams
Contributor

I have a requirement to find whether multiple users from the same source IP failed authentication for example. My test case is as follows:

I have an external IP address ==> 1.2.3.4
I have 3 users for example ==> User1, User2, User3

User1, User2 and User3 would normally have their own IP addresses that they log in with. My requirement is to see if all 3 users are coming from the same external IP address

Based on this I created the following search

index=inboundconns event="login found" | stats values(user) as user, count(user) as count by src

This gives me a table as follows (src 1.2.3.4 shows 3 users with a count of 3)

src | user | count
9.8.7.6 | user6 | 1
6.5.4.3 | user 88 | 2
1.2.3.4 | User 1 | 3
| User 2
| User 3

I then try and expand on the search to pick up on the "1.2.3.4" address and omit the "6.5.4.3" address. This is because "6.5.4.3" is only a single user from a single IP address.

I extend my query as follows

index=inboundconns event="login found" | stats values(user) as user, count(user) as count by src | where count > 1

This query then removes the first row in the able which is expected. However I need to be able to enhance this by doing the count by user and not by "count". So "where the user count > 1 for a specific or distinct src" show this and omit all else.

Tags (1)
0 Karma
1 Solution

renjith_nair
Legend

@willadams,

Try dc or distinct_count

index=inboundconns event="login found" | stats values(user) as user,dc(user) as count by src|where count >1
Happy Splunking!

View solution in original post

renjith_nair
Legend

@willadams,

Try dc or distinct_count

index=inboundconns event="login found" | stats values(user) as user,dc(user) as count by src|where count >1
Happy Splunking!
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Watch Now!   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to leverage ...

More Control Over Your Monitoring Costs with Archived Metrics!

What if there was a way you could keep all the metrics data you need while saving on storage costs?This is now ...

New in Observability Cloud - Explicit Bucket Histograms

Splunk introduces native support for histograms as a metric data type within Observability Cloud with Explicit ...