Hi,
I did some minor changes to the lookup, current structure is below,
Business AppName RunDays JobName AvgBatchStartTime JobType SLA_time SameDayFlag
Retail App1 Monday APP1T001 21:30 BatchStart 03:00 1
Retail App1 Monday APP1T099 21:30 BatchEnd 03:00 1
Retail App1 Tuesday APP1T001 21:30 BatchStart 03:00 1
Retail App1 Tuesday APP1T099 21:30 BatchEnd 03:00 1
Medicine App2 Saturday APP2T002 20:00 BatchStart 23:00 0
Medicine App2 Saturday APP2T099 20:00 BatchEnd 23:00 0
Medicine App2 Sunday APP2T002 20:00 BatchStart 23:00 0
Medicine App2 Sunday APP2T099 20:00 BatchEnd 23:00 0
Also I Indexed your data in main index. Below is the sample query. I did it only for SLA status "Running Late". Other status you can code similarly.
index="main"
| table JobName,JobStartTime,JobEndTime,JobStatus
| lookup sla_jobs JobName as JobName OUTPUTNEW Business,AppName,RunDays,JobType,AvgBatchStartTime,SLA_time,SameDayFlag
| eval AppName=mvdedup(AppName), AvgBatchStartTime=mvdedup(AvgBatchStartTime), Business=mvdedup(Business),JobType=mvdedup(JobType),SLA_time=mvdedup(SLA_time),SameDayFlag=mvdedup(SameDayFlag)
| rex field=JobStartTime "(?<job_start_day>\d{8})\s+(?<job_start_hr>\d{2})(?<job_start_mi>\d{2})"
| rex field=JobEndTime "(?<job_end_day>\d{8})\s+(?<job_end_hr>\d{2})(?<job_end_mi>\d{2})"
| eval job_start_hr_mi = job_start_hr + ":" + job_start_mi, job_start_hr_mi_epoc = strptime(job_start_hr_mi,"%H:%M"),job_end_hr_mi = job_end_hr + ":" + job_end_mi, job_end_hr_mi_epoc = strptime(job_end_hr_mi,"%H:%M"), SLA_time_epoc = strptime(SLA_time,"%H:%M"), batch_start_time_epoc = strptime(AvgBatchStartTime,"%H:%M")
| eval job_start_weekday=strftime(strptime(job_start_day,"%Y%m%d"),"%A"), job_end_weekday=strftime(strptime(job_end_day,"%Y%m%d"),"%A")
| eval SLA_status = case(job_start_hr_mi_epoc > batch_start_time_epoc,"Running Late")
| table Business,AppName,job_start_weekday,SLA_status
| search job_start_weekday = "*"
| rename job_start_weekday as "Batch Start Day"
Sid
... View more