Stats count question

[willadams]

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.

[renjith_nair · Accepted solution]

@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