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!
Is there a reason why you shouldn't use list(logtime)
?
It will produce a multivalue field, but that can be expanded with mvexpand
.
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
Is there a reason why you shouldn't use list(logtime)
?
It will produce a multivalue field, but that can be expanded with mvexpand
.
@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.
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.
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
?
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
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.
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.
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.
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)
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).
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!