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

[sjringo]

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 ?

[bowesmana · Accepted solution]

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.

[sjringo]

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