topic Re: How to search a list of users that have logged in from more than one IP? in Splunk Search

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

lakromani — Sun, 23 Aug 2015 18:34:59 GMT

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

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

MuS — Sun, 23 Aug 2015 19:24:08 GMT

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

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

MuS — Sun, 23 Aug 2015 19:47:36 GMT

Update ping

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

acharlieh — Sun, 23 Aug 2015 20:09:16 GMT

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

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

acharlieh — Sun, 23 Aug 2015 20:10:43 GMT

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?)

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

MuS — Sun, 23 Aug 2015 21:09:33 GMT

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

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

lakromani — Mon, 24 Aug 2015 06:41:04 GMT

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.