topic How to calculate Job runtime? in Alerting

How to calculate Job runtime?

MG — Tue, 19 Jul 2022 15:04:11 GMT

I have a field 'JOB_STATUS' with the values as 'STARTING' and 'SUCCESS'. With this I have to calculate the runtime.

runtime=STARTING-SUCCESS

Can you please let me know how to do this

Re: Job runtime

ITWhisperer — Tue, 19 Jul 2022 11:59:41 GMT

Presumably, these are different event?

Are the events link by a common id in another field, which is unique for each job?

Re: Job runtime

richgalloway — Tue, 19 Jul 2022 12:07:26 GMT

Please share some sample STARTING and SUCCESS events.

Re: Job runtime

MG — Tue, 19 Jul 2022 12:49:43 GMT

7/19/22 12:44:40.000 PM
[07/19/2022 08:44:40] CAXXX_I_40245 EVENT: CHANGE_STATUS STATUS: STARTING JOB: PullMg_CreateHistory_job MACHINE: xxxxxxxxxx sourcetype = event_demon

7/19/22 12:47:42.000 PM
[07/19/2022 08:47:42] CAXXX_I_40245 EVENT: CHANGE_STATUS STATUS: SUCCESS JOB: PullMg_CreateHistory_job MACHINE: xxxxxxxxxx EXITCODE: 0 sourcetype = event_demon

Re: Job runtime

MG — Tue, 19 Jul 2022 12:50:32 GMT

There is no common field like jobid or other related items

Re: Job runtime

ITWhisperer — Tue, 19 Jul 2022 13:00:57 GMT

How do you know which SUCCESS status relates to which STARTING status?

Re: Job runtime

MG — Tue, 19 Jul 2022 13:33:51 GMT

Only value is JOB_Name

Re: Job runtime

richgalloway — Tue, 19 Jul 2022 13:41:17 GMT

Assuming the machine name ties starting and finishing events, then this should help.

index=foo ("STARTING" OR "SUCCESS") | rex "JOB: (?<JOB>\S+)" | rex "MACHINE: (?<MACHINE>\S+)" | stats min(_time) as start, max(_time) as end by JOB MACHINE | eval RUNTIME=end-start

Re: Job runtime

MG — Tue, 19 Jul 2022 13:55:18 GMT

I think i'm getting the result. But the time format is not in the actual time format.how to convert it

start end runtime

1658235600

1658235605

Re: Job runtime

ITWhisperer — Tue, 19 Jul 2022 13:57:34 GMT

| fieldformat start=strftime(start,"%F %T") | fieldformat end=strftime(end,"%F %T")

Re: Job runtime

MG — Tue, 19 Jul 2022 14:04:11 GMT

Thanks..It worked

Re: Job runtime

MG — Wed, 20 Jul 2022 06:06:33 GMT

***The query given is clubbing all the start time and endtime for each job and not giving the actual result**

One of the job is running every 5min a day and these are the events.

index=foo JOB_4185_u (STARTING OR SUCCESS)
|table JOB MACHINE _time STATUS

JOB	MACHINE	_time	STATUS
JOB_4185_u	Machine1	20-07-2022 03:31	SUCCESS
JOB_4185_u	Machine2	20-07-2022 00:22	SUCCESS
JOB_4185_u	Machine1	19-07-2022 23:45	STARTING
JOB_4185_u	Machine1	19-07-2022 23:21	SUCCESS
JOB_4185_u	Machine2	19-07-2022 20:31	SUCCESS
JOB_4185_u	Machine1	19-07-2022 11:46	SUCCESS
JOB_4185_u	Machine1	19-07-2022 11:45	STARTING
JOB_4185_u	Machine2	19-07-2022 11:00	STARTING
JOB_4185_u	Machine1	19-07-2022 08:41	SUCCESS
JOB_4185_u	Machine2	20-07-2022 04:07	STARTING

But in the query , it is summing up all the starttime and end time for a job and giving it as a table with only two statistics eventhough there are 557 events matching.

index=foo JOB_4185_u (STARTING OR SUCCESS)
|stats min(_time) as STARTTIME,max(_time) as ENDTIME by JOB MACHINE
|fieldformat STARTTIME=strftime(STARTTIME,"%F %T")
|fieldformat ENDTIME=strftime(ENDTIME,"%F %T")
|eval RUNTIME=ENDTIME-STARTTIME
|table JOB MACHINE STARTTIME ENDTIME RUNTIME STATUS

Complete 557 events (7/19/22 5:00:00.000 AM to 7/20/22 5:18:44.000 AM)

JOB	MACHINE	STARTTIME	ENDTIME	RUNTIME	STATUS
JOB_4185_u	xxxxx	19-07-2022 05:20	20-07-2022 05:16	86197
JOB_4185_u	xxxxxxx	19-07-2022 05:00	20-07-2022 05:11	87103

Re: Job runtime

ITWhisperer — Wed, 20 Jul 2022 06:34:20 GMT

Your example data has inconsistent numbers and orders of STARTING and SUCCESS - assuming this is just a typo and that for each STARTING there will be a SUCCESS, try this

| sort 0 JOB MACHINE _time | streamstats range(_time) as duration first(STATUS) as previousSTATUS window=2 global=f by JOB MACHINE | where STATUS="SUCCESS" AND previousSTATUS="STARTING"

Re: Job runtime

MG — Wed, 20 Jul 2022 07:14:20 GMT

But how to separate start_time and end_time

|table JOB, STARTTIME,ENDTIME,RUNTIME

Re: Job runtime

ITWhisperer — Wed, 20 Jul 2022 07:20:24 GMT

| sort 0 JOB MACHINE _time | streamstats min(_time) as STARTTIME range(_time) as RUNTIME first(STATUS) as previousSTATUS window=2 global=f by JOB MACHINE | where STATUS="SUCCESS" AND previousSTATUS="STARTING" | rename _time as ENDTIME | table JOB STARTTIME ENDTIME RUNTIME