topic How to find the most recent log event for each user ? in Splunk Search

How to find the most recent log event for each user ?

sjringo — Tue, 14 Nov 2023 19:32:54 GMT

Here is what I am attempting to write SPL to show. I will have users logged into several hosts all using a web application. I want to see the last (most recent) activity performed for each user logged in.

Here is what I have so far:

index=anIndex sourcetype=aSourcetype

| rex field=_raw "^(?:[^,\n]*,){2}(?P<aLoginID>[^,]+)"
| rex field=_raw "^\w+\s+\d+_\w+_\w+\s+:\s+\w+\.\w+\.\w+\.\w+\.\w+\.\w+\.\w+\.\w+,(?P<anAction>\w+)"
| search aLoginID!=null
| stats max(_time) AS lastAttempt BY host aLoginID
| eval aTime = strftime(lastAttempt, "%Y-%m-%d %H:%M:%S %p ")
| sort -aTime
| table host aLoginID aTime
| rename host AS "Host", aLoginID AS "User ID", aTime AS "User Last Activity Time"

I am getting my data as expected by host aLoginID but want to only see the most recent anAction ?

When I add in my BY clause host aLoginID anAction I start seeing the userID repeated in my results as I would expect as each anAction "name" is different but I am only seeing one row for each anAction name.

I think I am on the right 'path' but I want to only see 1 row for each user not 1 row for each userID & action ?

Re: How to find the most recent log event for each user ?

bowesmana — Wed, 15 Nov 2023 01:39:35 GMT

Do you want to see the latest action by host AND login id or just the last action by login id?

Anyway, the way to do this is by doing

| stats max(_time) AS lastAttempt latest(anAction) as lastAction BY host aLoginID

rather than putting action into the split by.

Re: How to find the most recent log event for each user ?

sjringo — Wed, 15 Nov 2023 02:14:22 GMT

I know I tried latest(...) but like you mentioned I removed anAction from the split and am now seeing only the latest action for each user with no duplicated user ID in the results.

Thanks!!!