topic Re: Find the number of days between the earliest and latest event. in Splunk Search

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

kimberlytrayson — Sat, 25 Nov 2023 20:24:11 GMT

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

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

richgalloway — Sat, 25 Nov 2023 20:58:45 GMT

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

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

tscroggins — Sat, 25 Nov 2023 21:25:22 GMT

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 ```