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
---
What goes around comes around. If it helps, hit it with Karma 🙂

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
---
What goes around comes around. If it helps, hit it with Karma 🙂
Get Updates on the Splunk Community!

Database Performance Sidebar Panel Now on APM Database Query Performance & Service ...

We’ve streamlined the troubleshooting experience for database-related service issues by adding a database ...

IM Landing Page Filter - Now Available

We’ve added the capability for you to filter across the summary details on the main Infrastructure Monitoring ...

Dynamic Links from Alerts to IM Navigators - New in Observability Cloud

Splunk continues to improve the troubleshooting experience in Observability Cloud with this latest enhancement ...