Splunk Search

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

sjringo
Contributor

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 ?

Labels (2)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

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.

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

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
Contributor

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

0 Karma
Get Updates on the Splunk Community!

Demo Day: Strengthen Your SOC with Splunk Enterprise Security 8.1

Today’s threat landscape is more complex than ever. Security operation centers (SOCs) are overwhelmed with ...

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...