Exclude 1 date of duration calculation when jobs ran late.

[ndmaster]

Hello 
here's is my problem, I made a search which calculates duration between two jobs.
Jobs are supposed to run during our ovn. So the first starts around 10pm and the last around 00.30 so +- 2h30 after.
it's working fine but if the job A starts later (e.g.  09/04 at 00.09) then I can't get the calculation and get two empty rows .

09/06/21 02:30:42 21:50:00 00:20:41
09/04/21 00:00:00 03:19:24 03:19:24  <<
09/03/21 00:00:00 00:09:52 00:09:52  <<
09/02/21 02:31:56 21:56:44 00:28:40

it should display only 1 line for that ovn : 09/04/21 03:09:32 00:09:52 03:19:24 

sometimes it's ok i guess it's because job A started very later 4.36AM and after previous Job B run.
09/20/21 02:19:10 22:02:02 00:21:12
09/18/21 02:48:11 04:36:59 07:25:10  <<< ?? 
09/16/21 02:14:33 22:22:41 00:37:13

<query>| tstats
latest(evt_tsk_id) as evt_tsk_id,
latest(evt_tsk_status) as evt_tsk_status,
latest(evt_mes_occ_ts) as evt_mes_occ_ts,
latest(evt_mes_occ_ts_epoch) as evt_mes_occ_ts_epoch
where index=INDEX1 APP_env=ENV1 APP_inst=INSTANCE (evt_tsk_id ="JOB_A" AND evt_tsk_status="1") OR (evt_tsk_id ="JOB_B" AND evt_tsk_status="2") by _time span=1H
| bucket _time span=6H
| stats min(evt_mes_occ_ts_epoch) as start, max(evt_mes_occ_ts_epoch) as end by _time 
| eval N_duration = tostring(round(end-start,0), "duration")
| eval _time = strftime(_time,"%m/%d/%y") | convert timeformat="%H:%M:%S" ctime(start) AS JOB1 | convert timeformat="%H:%M:%S" ctime(end) AS JOB2 
| rename _time as date 
| table date N_duration JOB1 JOB2 | reverse</query>

thanks in advance

[ITWhisperer]

Try adding evt_task_id to the by clause

| tstats
latest(evt_tsk_status) as evt_tsk_status,
latest(evt_mes_occ_ts) as evt_mes_occ_ts,
latest(evt_mes_occ_ts_epoch) as evt_mes_occ_ts_epoch
where index=INDEX1 APP_env=ENV1 APP_inst=INSTANCE (evt_tsk_id ="JOB_A" AND evt_tsk_status="1") OR (evt_tsk_id ="JOB_B" AND evt_tsk_status="2") by _time evt_tsk_id span=1H

[ndmaster]

Hello thanks for your check , 
but it doesn't change the output

09/06/21 02:30:42 21:50:00 00:20:41
09/04/21 00:00:00 03:19:24 03:19:24
09/03/21 00:00:00 00:09:52 00:09:52
09/02/21 02:31:56 21:56:44 00:28:40

identical result 

[ITWhisperer]

Try adding evt_tsk_id to the by clause of the stats command too

[ndmaster]

what's wrong is that splunk ,maybe due to the bucket,  assign a wrong date to the JOB1 

that day job started 09-04-21 0:09:52  so why it giving me date 09-03 in the table 😞 

[ITWhisperer]

Perhaps you need a different approach - how about this

<query>| tstats
latest(evt_tsk_status) as evt_tsk_status,
latest(evt_mes_occ_ts) as evt_mes_occ_ts,
latest(evt_mes_occ_ts_epoch) as evt_mes_occ_ts_epoch
where index=INDEX1 APP_env=ENV1 APP_inst=INSTANCE (evt_tsk_id ="JOB_A" AND evt_tsk_status="1") OR (evt_tsk_id ="JOB_B" AND evt_tsk_status="2") by _time evt_tsk_id span=1H
| sort 0 evt_tsk_id _time
| streamstats values(evt_mes_occ_ts_epoch) as previous_evt_mes_occ_ts_epoch current=f window=1
| streamstats count by evt_tsk_id
| where count%2=0
| eval N_duration = tostring(round(evt_mes_occ_ts_epoch-previous_evt_mes_occ_ts_epoch ,0), "duration")
| eval _time = strftime(_time,"%m/%d/%y") | convert timeformat="%H:%M:%S" ctime(previous_evt_mes_occ_ts_epoch) AS JOB1 | convert timeformat="%H:%M:%S" ctime(evt_mes_occ_ts_epoch) AS JOB2 
| rename _time as date 
| table date N_duration JOB1 JOB2 | reverse</query>

[ndmaster]

Thanks for your reply,  

i'm getting very strange ouput 😕 

date N_duration JOB1 JOB2
09/30/21 1+00:31:05 21:54:47 22:25:52
09/28/21 1+00:12:07 21:53:39 22:05:46
09/24/21 1+00:08:53 21:53:56 22:02:49

[ITWhisperer]

It is me not understanding the nature of your data - what do the events you get from the tstats command look like?

[ndmaster]

_time	start	end	JOBA	JOBB	N_duration	duration
09/02/21	1630612604.476530	1630621720.865211	21:56:44	00:28:40	02:31:56
09/03/21	1630706992.156444	1630706992.156444	00:09:52	00:09:52	00:00:00
09/04/21	1630718364.976370	1630718364.976370	03:19:24	03:19:24	00:00:00
09/06/21	1630957800.383652	1630966841.955423	21:50:00	00:20:41	02:30:42

Here are the data from tstats, as you can see I'm struggling having a time bucket working , or the way I coded it, is not the correct approach to be bullet proof in those specific cases were JOBA and JOBB are running a later than expected .
Even if the start of run of JOBA is after midnight on the 09/04/21  (displayed in time bucket of 09/03/21 here)  duration and unique line for this chain JobA<->JobB should be 09/03/21 

I hope it helps understanding

[ndmaster]

i ll reply monday , i'm not at the office anymore 🙂

[ndmaster]

Just did 
09/04/21 00:00:00 03:19:24 03:19:24
09/03/21 00:00:00 00:09:52 00:09:52
09/02/21 00:00:00 21:56:44 21:56:44
09/02/21 00:00:00 00:28:40 00:28:40

that's worse 🙂