OK, this is NOT a complete answer; it is maybe 92%. In any case, it should give you all of the examples and tools that you will need to walk it to the finish line. Be aware that the | append ... lines would be better as a | lookup sla_jobs.csv JobName AS BatchStartJob ... but this was easier for me to work with:
|makeresults | eval raw="JobName=APP1T099,JobEndTime=20190130T0130,JobStatus=COMPLETED JobName=APP1T099,JobStartTime=20190130T0100,JobStatus=STARTED JobName=APP1T001,JobEndTime=20190129T2230,JobStatus=COMPLETED JobName=APP1T001,JobStartTime=20190129T2200,JobStatus=STARTED"
| rename COMMENT AS "As and when the jobs start and complete, the events show with JobStatus as 'STARTED', 'COMPLETED' or 'FAILED'."
| makemv raw
| mvexpand raw
| rename raw AS _raw
| kv
| eval which="EVENTS"
| foreach Job*Time [ rex field=<<FIELD>> mode=sed "s/T/ /" | eval _time=coalesce(_time, strptime(<<FIELD>>, "%Y%m%d %H%M")) ]
| sort 0 - _time
| eval RunDay = strftime(_time, "%A")
| stats first(*) AS * BY JobName
| eval Run_time = strptime(JobEndTime, "%Y%m%d %H%M") - strptime(JobStartTime, "%Y%m%d %H%M")
| append [|makeresults | eval raw="Business=Retail,AppName=App1,RunDays=Monday,BatchStartJob=APP1T001,AvgBatchStartTime=21:30,BatchEndJob=APP1T099,SLA_time=03:00,SameDayFlag=1 Business=Retail,AppName=App1,RunDays=Tuesday,BatchStartJob=APP1T001,AvgBatchStartTime=21:30,BatchEndJob=APP1T099,SLA_time=03:00,SameDayFlag=1 Business=Medicine,AppName=App2,RunDays=Saturday,BatchStartJob=APP2T002,AvgBatchStartTime=20:00,BatchEndJob=APP2T099,SLA_time=23:00,SameDayFlag=0 Business=Medicine,AppName=App2,RunDays=Sunday,BatchStartJob=APP2T002,AvgBatchStartTime=20:00,BatchEndJob=APP2T099,SLA_time=23:00,SameDayFlag=0"
| rename COMMENT AS "Lookup file file sla_jobs.csv contains the SLA times for each appname, business and the respective start, end jobs
for each of the batch and the day of week they run.
SameDayFlag indicates if the SLA_time is for same day or if it can fall to next day.
For example, for App1 the batch starts usually at 21:30 and runs only on Mon and Tues,
but the SLA is till next day 03:00. So for Tuesday's batch the SLA is till Wed 03:00."
| makemv raw
| mvexpand raw
| rename raw AS _raw
| rex field=SLA_time "^(?<hours>\d+):(?<minutes>\d+)$"
| eval SLA_time = 60 * (hours + (60 * minutes))
| eval which="LOOKUP"]
| kv
| fields - _raw _time
| rex field=SLA_time "^(?<hours>\d+):(?<minutes>\d+)$"
| eval SLA_time = coalesce(60 * (minutes + (60 * hours)), SLA_time)
| fields - hours minutes
| multireport
[ eval JOINER = coalesce(JobName, BatchStartJob) | stats list(*) AS * BY JOINER | eval which="BatchStartJob" ]
[ eval JOINER = coalesce(JobName, BatchEndJob) | stats list(*) AS * BY JOINER | eval which="BatchEndJob" ]
| where isnotnull(JobStatus) AND isnotnull(AppName)
| eval zeroBasePos=mvfind(RunDays, RunDay)
| foreach A* B* RunDays SLA_time SameDayFlag [ eval <<FIELD>> = mvindex(<<FIELD>>, zeroBasePos) ]
| rex field=JobStartTime mode=sed "s/^\d+\s+//"
| rex field=AvgBatchStartTime mode=sed "s/://"
| eval Status = case(
(JobStartTime > AvgBatchStartTime) AND (Run_time > SLA_time), "Running Late AND Not Met",
(JobStartTime > AvgBatchStartTime) AND (Run_time <= SLA_time), "Running Late BUT Met",
Run_time > SLA_time, "Not Met",
true(), "Met")
... View more
