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!

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...