Splunk Search
Highlighted

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

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
Highlighted

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

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
Highlighted

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

Path Finder

Thank a million Woodcock, that's exactly it.

0 Karma