Splunk Search

Find latest login per user and IP-Address

Motivator

Hey Splunkers,

I hope someone can help me finalizing my search. I am trying to find out, if there are any users in my environment, that logged in from more than one subnet within 24h. My SPL looks like this:

 sourcetype=accesslog user=* | rex field=src_ip "(?<subnet>\d+\.\d+)" | stats dc(subnet) AS netcount values(src_ip) AS workstations by user | where subnet > 1 | table workstations, user

Not I want to know which was the last time the user logged in to each of the workstations. I tried by integrating latest(logtime) AS last_login to the stats command, but this only shows one time (which assumably is correct, as it is the last timestamp) ...

How can I manage to get the needed information? I'd like to have a table similar to this:

 Workstation    Last Login          User
 10.11.12.13    15-11-01 10:00:00   user1
 10.12.13.14    15-11-01 15:34:02   

Thanks for your help!

Tags (5)
0 Karma
1 Solution

Champion

Is there a reason why you shouldn't use list(logtime)?
It will produce a multivalue field, but that can be expanded with mvexpand.

View solution in original post

Path Finder

Maybe try using dedup to return only the latest event with those fields?

 sourcetype=accesslog user=* 
| rex field=src_ip "(?<subnet>\d+\.\d+)" 
| dedup src_ip, user sortby -_time 
| stats dc(subnet) AS netcount values(src_ip) AS workstations by user 
| where subnet > 1 
| table workstations, user 
0 Karma

Champion

Is there a reason why you shouldn't use list(logtime)?
It will produce a multivalue field, but that can be expanded with mvexpand.

View solution in original post

Motivator

@jeffland Thanks, that helped a bit. I changed my query like this:

 sourcetype=accesslog user=* | rex field=src_ip "(?<subnet>\d+\.\d+)" | stats dc(subnet) AS netcount list(src_ip) AS workstations list(logtime) as last_login by user | where subnet > 1 | table workstations, last_login, user

But now I am getting not only the latest login per workstation but all of them. How do I limit this to the last login?

Edit: My resultset looks like this now:

 Workstation    Last Login          User
 10.11.12.13    15-11-01 10:00:00   user1
 10.12.13.14    15-11-01 09:58:00
 10.11.12.13    15-11-01 09:56:00
 10.11.12.13    15-11-01 09:54:00
 10.12.13.14    15-11-01 09:52:00

I'd like to have the last Login per workstation only.

0 Karma

Champion

Ah, sorry - now I understand (I think). Just to clarify, you want the last login time of each user by workstation? Then you could try eventstats last(logtime) as last_login by user src_ip before the actual stats and list both these new logins as well as the workstations. Side by side, these should have the info you're looking for, although in multivalue field format, which might not be what you want. I believe there may be a better way with subsearches, let me think about that for a second.

0 Karma

Champion

Hold on. Why don't you split your entire stats by both user and src_ip, i.e.

sourcetype=accesslog user=* | rex field=src_ip "(?<subnet>\d+\.\d+)" | stats dc(subnet) AS netcount values(src_ip) AS workstation list(logtime) as last_login by user src_ip | where subnet > 1 | table workstations, user

?

0 Karma

Motivator

If I use the search query as states in the last comment (splitting by user and src_ip) I don't get any results at all!

With using eventstats I have other results. I get the last login by IP, but still multiple times:

 sourcetype=accesslog user=* | rex field=src_ip "(?<subnet>\d+\.\d+)" | eventstats last(logtime) as logintime by user src_ip | stats dc(subnet) AS netcount list(src_ip) AS workstations list(logtime) as last_login by user | where subnet > 1 | table workstations, last_login, user

The result is:

 Workstation    Last Login          User
 10.11.12.13    15-11-01 10:00:00   user1
 10.11.12.13    15-11-01 10:00:00
 10.11.12.13    15-11-01 10:00:00
 10.12.13.14    15-11-01 09:58:00
 10.12.13.14    15-11-01 09:58:00
 10.11.12.13    15-11-01 10:00:00
0 Karma

Champion

Strange that you don't get any results when splitting by both user and src_ip. That would imply that there are no events which have both info.

0 Karma

Motivator

I think I found the mistake: If I split by user and srcip my netcount willl always be 1, as this field is derived from srcip.

I need to find another way to do net netcount here.

0 Karma

Champion

But that's not an error in itself, it's simply a sign that there are no two logins from the same subnet if I'm not mistaken.

0 Karma

Motivator

Not quite ...

Looking at the search, I do a dc(subnet) by user to find out if there is more than one subnet-login by any given user during my search timframe. Now, if I split the dc(subnet) by user AND srcip, I won't have more than one subnet in the results, as they are split by subnet (which is a substring of srcip)

0 Karma

Champion

Oh yes, of course. Sorry, didn't pay enough attention.
Now, from the beginning - wouldn't it work to just do something like this?

sourcetype=accesslog user=* | rex field=src_ip "(?<subnet>\d+\.\d+)" | eventstats dc(subnet) AS netcount by user | where netcount > 1 | eventstats latest(_time) as most_recent by user subnet | where most_recent==_time 

I replaced your stats with eventstats, and did the two filtering steps one after another (once for those using more than one subnet, once in case there is more than one login from a subnet).

Motivator

Awesome, thanks! Now I have exactly what I want. Thanks a ton, you enlightened my day and made my customer happy! 🙂

Note to self: Learn more about the eventstats command!