Find the number of days between the earliest and latest event.

[kimberlytrayson]

I need help with an employee travel analysis report.
I have an index containing information about employee office check-ins in various countries. Events have fields Employee, Time, Country

For example

John Doe, 2023-11-15T20:05:31.000+00:00, France
...
John Doe, 2023-11-18T10:00:31.000+00:00, France
...
John Doe, 2023-11-20T10:05:31.000+00:00, United States
...
John Doe, 2023-11-25T20:05:31.000+00:00, United States

At the end I would like to get the result showing duration in days between first checkin and last checkin per employee per country

John Doe, 3d, France
John Doe, 5d, United States

[richgalloway · Accepted solution]

It would help to know what you've tried already so we don't suggest something that doesn't work.

A thing to remember is Splunk can't compare timestamps in text form - they must be converted to epoch (integer) form, first.

| makeresults format=csv data="Employee, Time, Country
John Doe, 2023-11-15T20:05:31.000+00:00, France
John Doe, 2023-11-18T10:00:31.000+00:00, France
John Doe, 2023-11-20T10:05:31.000+00:00, United States
John Doe, 2023-11-25T20:05:31.000+00:00, United States"
``` Above creates demo data.  Delete IRL ```
``` Convert Time to epoch ```
| eval eTime=strptime(Time, "%Y-%m-%dT%H:%M:%S.%3N%:z")
``` Find the lowest and highest time for each employee/country pair ```
| stats min(eTime) as start, max(eTime) as end by Employee,Country
``` Calculate the duration in days.  Add "d" suffix for display ```
| eval duration=round((end-start)/86400,0) . "d"
| table Employee duration Country

[tscroggins]

I often simplify time deltas using stats range():

| stats range(eval(strptime(Time, "%Y-%m-%dT%H:%M:%S.%3N%:z"))) as duration by Employee Country
| fieldformat duration=round(duration/86400, 0)."d"
``` or ```
``` | fieldformat duration=tostring(duration, "duration") ```
``` for Splunk-normalized [D+]HH:MM:SS display ```