Duration between multiple events

[zoebanning]

Hello Splunk Community, 

Can anyone help me build a query based on the below;

I have a batch job that usually starts at 5.30pm everyday and finishes in the early morning of the following day. The batch job has multiple steps logged as separate events and has no unique id to link the step to the batch job.
I want to create a timechart which shows the total duration (step 1 to 5) for each batch job occurring daily. Example of 1 batch job start & end time (Dummy Data Used):

Step	Start_Time	End_Time
1	2021-09-11 17:30:00	2021-09-11 23:45:01
2	2021-09-11 23:45:01	2021-09-12 01:45:20
3	2021-09-12 01:45:20	2021-09-12 02:35:20
4	2021-09-12 02:35:20	2021-09-12 03:04:25
5	2021-09-12 03:04:25	2021-09-12 05:23:06

I hope someone can figure this one out as i have been stuck on it for a few days. 

Many Thanks, 

Zoe

[zoebanning]

No need to worry about the above question, I was able to figure it out! Thanks anyways 🙂 

[ITWhisperer · Accepted solution]

Assume that your job always starts after 17:00 and completes before 17:00 the following day, so subtract 17 hours from the times so they are all in the same day, then bucket/bin the events by day and use the earliest start and latest end to determine the duration

| gentimes start=-2 increment=1h
| rename starttime as Start_Time
| rename endtime as End_Time 



| eval Start_Time=Start_Time-(17*60*60)
| eval End_Time=End_Time-(17*60*60)
| eval _time=Start_Time
| bin _time span=1d
| stats earliest(Start_Time) as Start_Time latest(End_Time) as End_Time by _time
| eval duration=tostring(End_Time-Start_Time,"duration")

[zoebanning]

Thank you @ITWhisperer - this was exactly what i was looking for!

So we have built this query which calculates the total duration over a few days, now how do i create a timechart to show the _time on x-axis and duration on y-axis. 

I think I need to convert the duration from hours to minutes but not sure how to do this.

Below is an example of the output from my original query I am trying to visualise in a timechart;

_time	duration (in hours)
2021-10-12	03:56:30
2021-10-13	04:27:25
2021-10-14	04:21:03
2021-10-18	07:11:04

Can you help?