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
Only value is JOB_Name
Please share some sample STARTING and SUCCESS events.
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
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
***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 |
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"
But how to separate start_time and end_time
|table JOB, STARTTIME,ENDTIME,RUNTIME
| 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
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 |
| fieldformat start=strftime(start,"%F %T")
| fieldformat end=strftime(end,"%F %T")
Thanks..It worked
Presumably, these are different event?
Are the events link by a common id in another field, which is unique for each job?
There is no common field like jobid or other related items
How do you know which SUCCESS status relates to which STARTING status?