Splunk Search

Find latest login per user and IP-Address

DMohn
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

jeffland
SplunkTrust
SplunkTrust

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

vasildavid
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

jeffland
SplunkTrust
SplunkTrust

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

DMohn
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

jeffland
SplunkTrust
SplunkTrust

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

jeffland
SplunkTrust
SplunkTrust

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

DMohn
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

jeffland
SplunkTrust
SplunkTrust

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

DMohn
Motivator

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

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

0 Karma

jeffland
SplunkTrust
SplunkTrust

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

DMohn
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 src_ip, I won't have more than one subnet in the results, as they are split by subnet (which is a substring of src_ip)

0 Karma

jeffland
SplunkTrust
SplunkTrust

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

DMohn
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!

Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...