Hi everyone,
I am currently facing an issue so I'm coming here to ask for your help. My issue is basic :
I get the data from differents JOBS. Here is an example of the data i'm getting :
Name="JOB1" StartTime="2021-09-16 05:10:45+02" EndTime="2021-09-16 06:10:45+02"
Name="JOB2" StartTime="2021-09-16 06:08:45+02" EndTime="2021-09-16 09:10:45+02"
As you can see, JOB1 start sooner than JOB2 but, JOB2 finishes later. I would like to have in a table in THE SAME ROAD the difference bewteen StartTime of JOB1 and EndTime of JOB2, in this case, the result should be then around 4H or 280 minutes (format Hour or minutes doesn't matter)
I can't figure out how to it, so thank you all in advance 😉
| eval starttime=if(jobname="job1",strptime(StartTime,"%Y-%m-%d %H:%M:%S"),null())
| eval endtime=if(jobname="job2",strptime(EndTime,"%Y-%m-%d %H:%M:%S"),null())
| stats min(starttime) as starttime max(endtime) as endtime by date job
| eval difference=tostring(endtime-starttime,"duration")
Thank you for your answer, I should specify :
I get the data from the last 8 week and the jobs are running daily, which means that I get a table of data like this :
Jobs are always running the same day, so you always get job1 start and end, job 2 start and end,
So what I would like to get is a table showing something like :
JOB1 | JOB2 | JOB1 START TIME | JOB2 END TIME | JOB2ENDTIME - JOB1 STARTTIME | DATE OF RUN (ex 09-22-2021) |
etc | etc | etc | etc | etc | |
I also get the date of run from the data, I just didn't show it in the screenshot
| eval starttime=if(jobname="job1",strptime(StartTime,"%Y-%m-%d %H:%M:%S"),null())
| eval endtime=if(jobname="job2",strptime(EndTime,"%Y-%m-%d %H:%M:%S"),null())
| stats min(starttime) as starttime max(endtime) as endtime by date job
| eval difference=tostring(endtime-starttime,"duration")
It looks like it is doing the job, thank you so much 🙂
| eval starttime=strptime(StartTime,"%Y-%m-%d %H:%M:%S")
| eval endtime=strptime(EndTime,"%Y-%m-%d %H:%M:%S")
| stats min(starttime) as starttime max(endtime) as endtime
| eval difference=tostring(endtime-starttime,"duration")
I'm not sure if I understand correctly, but I tnink that OP would like to calculate "aggregate start time" and stop time for multiple series of overlapping jobs. Not just global start/end.