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.
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")
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")
Thank a million Woodcock, that's exactly it.