Splunk Search

Duration between multiple events

zoebanning
Path Finder

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):

StepStart_TimeEnd_Time
12021-09-11 17:30:002021-09-11 23:45:01
22021-09-11 23:45:012021-09-12 01:45:20
32021-09-12 01:45:202021-09-12 02:35:20
42021-09-12 02:35:202021-09-12 03:04:25
52021-09-12 03:04:252021-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

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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")

View solution in original post

zoebanning
Path Finder

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Path Finder

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-1203:56:30
2021-10-1304:27:25
2021-10-1404:21:03
2021-10-1807:11:04

 

Can you help? 

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...