How do I edit my search using multiple static times to compare against job times for an SLA check?

[tondapi]

Hi,

I am trying to create a search to check if my jobs are meeting SLA or not, but I am not able to get the search right.
Requirement is I have 10 jobs and their SLA are different. Please find the sample below.

Job SLA
Kh8000 7:30
Kh1432 8:00
Kh8900 7:30
Kh5606 7:00
Kh4001 8:00
Kh6123 8:00

By using the search below, I am able to return data only for one job ant able to place all jobs. Can you please help me edit this search?

index=ops sourcetype="tws_merged"  host=cplita22.homedepot.com (job_name =kh8900 ) | transaction job_name job_number | eval job_completed=if(match(_raw, "SUCCESSFULLY"), 1, 0) | eval job_did_start=_time | eval job_did_finish=if(job_completed==1, _time+duration, null()) | rename job_cpu_name AS job_server | rename job_stream_name AS job_schedule | eval seconds=job_did_finish-job_did_start | sec2str | rename seconds_human as | sort - seconds | rename job_did_start as _time job_did_finish as end_time job_server as job_cpu_name | eval Dayofweek = strftime(_time,"%A") | eval Start_time =strftime(_time, "%H:%M:%S") | eval DATE=strftime(_time, "%m/%d/%Y") | eval endtime_ep=_time+seconds | eval sla_cutoff=DATE." 07:30:00" | eval sla_cutoff1=strptime(sla_cutoff, "%m/%d/%Y %H:%M:%S") | convert ctime(endtime_ep) as endtime| eval sla_status = if(endtime_ep>sla_cutoff1, "MISS", "OK") | table DATE Dayofweek Start_time Application_Name job_schedule job_name duration endtime sla_status | sort date job_schedule job_name

[somesoni2]

Once you fix issues pointed by @woodcock, try this

1) create a static lookup table file to store the JOB and it's corresponding SLA, say "lookup_job_sla.csv" with field job_name and SLA
2) Update the query as following

index=ops sourcetype="tws_merged" host=cplita22.homedepot.com [|inputlookup lookup_job_sla.csv | table job_name ] | transaction job_name job_number | eval job_completed=if(match(_raw, "SUCCESSFULLY"), 1, 0) | eval job_did_start=_time | eval job_did_finish=if(job_completed==1, _time+duration, null()) | rename job_cpu_name AS job_server | rename job_stream_name AS job_schedule | eval seconds=job_did_finish-job_did_start | sec2str | rename seconds_human as seconds | sort - seconds | rename job_did_start as _time job_did_finish as end_time job_server as job_cpu_name | eval Dayofweek = strftime(_time,"%A") | eval Start_time =strftime(_time, "%H:%M:%S") | eval DATE=strftime(_time, "%m/%d/%Y") | eval endtime_ep=_time+seconds 
| lookup lookup_job_sla.csv job_name OUTPUT SLA
| eval sla_cutoff=DATE." ".SLA | eval sla_cutoff1=strptime(sla_cutoff, "%m/%d/%Y %H:%M") | convert ctime(endtime_ep) as endtime| eval sla_status = if(endtime_ep>sla_cutoff1, "MISS", "OK") | table DATE Dayofweek Start_time Application_Name job_schedule job_name duration endtime sla_status | sort date job_schedule job_name

[tondapi]

Thanks,

Where to create and place the cvs.file ? and how my query and lookup file will link.

Can i keep file in local desktop or need to place in server ?

[woodcock]

Your search is totally broken for at least the following reasons: sec2str is not a Splunk command so I assume it is a macro but it does not have the back-tick/grave characters around it so it will definitely error as-is. You also have | rename seconds_human as | which is also an incomplete command that will error. So I am pretty sure this is not the SPL string that you intended to post with your question.