How to calculate Job runtime?

[MG]

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

[MG]

Only value is JOB_Name

[richgalloway]

Please share some sample STARTING and SUCCESS events.

[MG]

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

[richgalloway · Accepted solution]

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

[MG]

***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

[ITWhisperer]

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"

[MG]

But how to separate start_time and end_time

|table JOB, STARTTIME,ENDTIME,RUNTIME

[ITWhisperer]

| 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

[MG]

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

5

[ITWhisperer]

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

[MG]

Thanks..It worked

[ITWhisperer]

Presumably, these are different event?

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

[MG]

There is no common field like jobid or other related items

[ITWhisperer]

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