Splunk Search

How to search a list of users that have logged in from more than one IP?

lakromani
Builder

I have a log some like this:

Aug 23 19:22:19 server1 Peter logged in from 192.168.1.20
Aug 23 19:22:15 server1 Oleg logged in from 192.168.1.80
Aug 23 19:21:44 server1 Hans logged in from 192.168.1.242
Aug 23 19:20:50 server1 Peter logged in from 192.168.1.20
Aug 23 19:20:34 server1 Hans logged in from 192.168.1.66
Aug 23 19:19:36 server1 Oleg logged in from 192.168.1.80

Where user = Peter, Oleg etc
And user_ip = 192.168.1.20, 192.168.1.80 etc

I know how to use dedup etc to find unique data, but how do I do to get a list of users that have logged in from more than one IP?

So for this data, I'd like to get :

Hans 192.168.1.242
Hans 192.168.1.66
0 Karma
1 Solution

acharlieh
Influencer

I think @MuS is on the right track here, but I want to throw some other thoughts onto the table, for example:

... | stats values(user_ip) as user_ip by user | where mvcount(user_ip) > 1

This search one gets the set of distinct ip addresses for each user, and keeps those who have multiple ip addresses.

Now MuS uses count but depending on framing you may need distinct_count. For example Peter and Oleg who logged in twice from the same IP with using count (if he was splitting only by user) would get a count of 2 (since they have 2 results with a user_ip field, but they each have only 1 value in that user ip field. The docs on functions for stats and functions for eval and where are really comprehensive resources.

Something MuS hints at which could be important is that there is that there is a time component to this as well, how do we know / estimate that a session is ended? For example if Hans logs in from home, then goes to the local coffee shop and logs in again he could have two IPs legitimately. Or is your timeframe short enough that 2 different IPs within this frame we would want to detect. As Hans has 2 different IPs at 2 different times, MuS's solution as written wouldn't work, but if we bin time, that could work. (although the by user_ip is also strange to me).

View solution in original post

acharlieh
Influencer

I think @MuS is on the right track here, but I want to throw some other thoughts onto the table, for example:

... | stats values(user_ip) as user_ip by user | where mvcount(user_ip) > 1

This search one gets the set of distinct ip addresses for each user, and keeps those who have multiple ip addresses.

Now MuS uses count but depending on framing you may need distinct_count. For example Peter and Oleg who logged in twice from the same IP with using count (if he was splitting only by user) would get a count of 2 (since they have 2 results with a user_ip field, but they each have only 1 value in that user ip field. The docs on functions for stats and functions for eval and where are really comprehensive resources.

Something MuS hints at which could be important is that there is that there is a time component to this as well, how do we know / estimate that a session is ended? For example if Hans logs in from home, then goes to the local coffee shop and logs in again he could have two IPs legitimately. Or is your timeframe short enough that 2 different IPs within this frame we would want to detect. As Hans has 2 different IPs at 2 different times, MuS's solution as written wouldn't work, but if we bin time, that could work. (although the by user_ip is also strange to me).

lakromani
Builder

Thanks acharlieh, this did the trick 🙂

It gives me some insight on how user logs inn to our system. I do see that there may be normal that a user has logged inn to the server with two differnet IP. Some user does have PPPOE so that every new session gets new IP.

0 Karma

MuS
SplunkTrust
SplunkTrust

HeHe, I'm excused 😉 wrote that at the airport after a 24 hour plane trip 🙂

MuS
SplunkTrust
SplunkTrust

Hi lakromani,

Try to count the IP by user and filter for more than one count like this:

your base search here | stats count(user_ip) AS ipCount by user, user_ip, _time | where ipCount >=1 | table _time, user, user_ip 

cheers, MuS

acharlieh
Influencer

I think as currently written, this would get those users who logged in multiple times from the same IP at the same exact time currently? (As opposed to users who logged in from different ips within a short timeframe?)

0 Karma

MuS
SplunkTrust
SplunkTrust

Update ping

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