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!

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Announcing the General Availability of Splunk Enterprise Security 8.1!

We are pleased to announce the general availability of Splunk Enterprise Security 8.1. Splunk becomes the only ...

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...