Splunk Search

How to create a table with the earliest and latest event times of individual Users?

Aaron_Fogarty
Path Finder

I am trying to create a table that will show the earliest and latest event times of every user in my search. The "First Login"column will be their very first event time and the "Last Login" column will be their last event created.

This search will return every event's _time for each user but the "Last Login" is the same for every User. With this search I can see the times I want but I want to dedup the Users.

index=* sourcetype=*  User | eval User=lower(User) | lookup local=true lookup1.csv user_id as User | eval Name=display_name  | table _time User Name| streamstats earliest(_time) as "First Login" latest(_time) as "Last Login" | convert ctime("First Login") ctime("Last Login")

This search will return single User event's _time but it is not finding the earliest event _time for the "First Login" and the "Last Login" is the same for every User .

    index=* sourcetype=*  User | eval User=lower(User) | lookup local=true lookup1.csv user_id as User | eval Name=display_name  | table _time User Name| streamstats earliest(_time) as "First Login" latest(_time) as "Last Login" | convert ctime("First Login") ctime("Last Login") | dedup Name

Is there a better way to achieve what I am looking to do?

Thanks.

0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

 index=* sourcetype=*  User | eval User=lower(User) | lookup local=true lookup1.csv user_id as User | eval Name=display_name 
| stats earliest(_time) AS "First Login" latest(_time) AS "Last Login" BY Name | convert ctime("First Login") ctime("Last Login")

View solution in original post

0 Karma

woodcock
Esteemed Legend

Like this:

 index=* sourcetype=*  User | eval User=lower(User) | lookup local=true lookup1.csv user_id as User | eval Name=display_name 
| stats earliest(_time) AS "First Login" latest(_time) AS "Last Login" BY Name | convert ctime("First Login") ctime("Last Login")
0 Karma

Aaron_Fogarty
Path Finder

Thank a million Woodcock, that's exactly it.

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...