Lookup file jobsla.csv: Contains start and end batch jobnames for different apps, frequency the jobs will run on(like Mon-Fri in below cases), SLA time before which they need to run
Units AppName FreqStart FreqEnd StartJob EndJob SLA_time
Sales ABC Mon Fri AMGABC01 AMGABC10 18:00
Healthcare EFG Mon Fri AMGEFG65 AMGEFG70 01:00
Events in index_jobruns: Contains job starttime, end time, status of different job runs
JobName Agent JobStartTime JobEndTime JobStatus
AMGABC01 PLINUX03 20180906 0300 20180906 0310 COMPLETE
AMGABC12 PLINUX01 20180906 0300 20180906 0310 FAILED
AMGABC10 PLINUX05 20180906 0400 20180906 0410 COMPLETE
AMGEFG65 PLINUX01 20180906 0300 20180906 0310 COMPLETE
AMGEFG70 PLINUX01 20180906 0400 20180906 0410 FAILED
Output Required: I was looking to display if the jobs listed in lookup file in 'StartJob' and 'EndJob' have completed within the SLA_time listed. If its a Saturday or Sunday, the status below should be N/A
Units AppName SLA_Status
Sales ABC Met
Healthcare EFG Missed
Started with the below code to format the date and time was not sure how to proceed. Would appreciate any help. Thank you
index="index_jobruns"
[| inputlookup jobsla.csv | table StartJob ]
| eval JobStartTime=strftime(strptime(JobStartTime, "%Y%m%d %H%M"),"%d.%m.%Y %H:%M")
| eval JobEndTime=strftime(strptime(JobEndTime, "%Y%m%d %H%M"),"%d.%m.%Y %H:%M")
| table Units, AppName, SLA_Status
@naraininfy,
Try this. Dont worry about the length, its just the evals and formatting 🙂 . Change the "weekend" conditions according to your requirement
index=index_jobruns
|eval end_time=strptime(JobEndTime,"%Y%m%d %H%M")
|eval HR=tonumber(strftime(end_time,"%H"))*60,
MN=tonumber(strftime(end_time,"%M")),
TOTAL_END=HR+MN
|lookup sla.csv StartJob as JobName OUTPUTNEW SLA_time as StartSLA,
AppName as StartAppName,
Units as StartUnits,
FreqStart as StartFreqStart,
FreqEnd as StartFreqEnd
|lookup sla.csv EndJob as JobName OUTPUTNEW SLA_time as EndSLA,
AppName as EndAppName,
Units as EndUnits,
FreqStart as EndFreqStart,
FreqEnd as EndFreqEnd
|eval SLA=coalesce(StartSLA,EndSLA),
Units=coalesce(StartUnits,EndUnits),
AppName=coalesce(StartAppName,EndAppName),
FreqStart=coalesce(StartFreqStart,EndFreqStart),
FreqEnd=coalesce(StartFreqEnd,EndFreqEnd)
|eval SLA_HR_MN=strptime(SLA,"%H:%M"),
SLA_HR=tonumber(strftime(SLA_HR_MN,"%H"))*60,
SLA_MIN=tonumber(strftime(SLA_HR_MN,"%M")),
SLA_TOTAL=SLA_HR+SLA_MIN
|eval SLA_Status=if(JobStatus=="COMPLETE" AND TOTAL_END < SLA_TOTAL,"Met","Missed")
|eval SLA_Status=if(FreqStart=="Sat" OR FreqStart=="Sun" OR FreqEnd=="Sat" OR FreqEnd=="Sun","NA",SLA_Status)
|stats values(Units) as Units,values(SLA_Status) as SLA_Status by AppName
Hi @naraininfy - Did the answer below help provide a solution to your question? If yes, please click “Accept” below the answer to resolve this post and upvote anything that was helpful. If no, please leave a comment with more feedback. Thanks.
@naraininfy,
Try this. Dont worry about the length, its just the evals and formatting 🙂 . Change the "weekend" conditions according to your requirement
index=index_jobruns
|eval end_time=strptime(JobEndTime,"%Y%m%d %H%M")
|eval HR=tonumber(strftime(end_time,"%H"))*60,
MN=tonumber(strftime(end_time,"%M")),
TOTAL_END=HR+MN
|lookup sla.csv StartJob as JobName OUTPUTNEW SLA_time as StartSLA,
AppName as StartAppName,
Units as StartUnits,
FreqStart as StartFreqStart,
FreqEnd as StartFreqEnd
|lookup sla.csv EndJob as JobName OUTPUTNEW SLA_time as EndSLA,
AppName as EndAppName,
Units as EndUnits,
FreqStart as EndFreqStart,
FreqEnd as EndFreqEnd
|eval SLA=coalesce(StartSLA,EndSLA),
Units=coalesce(StartUnits,EndUnits),
AppName=coalesce(StartAppName,EndAppName),
FreqStart=coalesce(StartFreqStart,EndFreqStart),
FreqEnd=coalesce(StartFreqEnd,EndFreqEnd)
|eval SLA_HR_MN=strptime(SLA,"%H:%M"),
SLA_HR=tonumber(strftime(SLA_HR_MN,"%H"))*60,
SLA_MIN=tonumber(strftime(SLA_HR_MN,"%M")),
SLA_TOTAL=SLA_HR+SLA_MIN
|eval SLA_Status=if(JobStatus=="COMPLETE" AND TOTAL_END < SLA_TOTAL,"Met","Missed")
|eval SLA_Status=if(FreqStart=="Sat" OR FreqStart=="Sun" OR FreqEnd=="Sat" OR FreqEnd=="Sun","NA",SLA_Status)
|stats values(Units) as Units,values(SLA_Status) as SLA_Status by AppName
Is it possible to consider the below point:
1) I need to display all the Units in output from jobsla.csv even if some of the jobs are not found in the index. For eg, the jobs AMGABC01 and AMGABC10 present against 'Sales' in the lookup might not have run that day and will not be present in events for the day, in that case i want to mention the 'SLA_Status' as 'Missed' for it, if the current time is past the SLA_time.
It's easy if you could change your lookup file to have a separate record for each job instead of StartJob/EndJob.
for eg.
Units AppName FreqStart FreqEnd JobName SLA_time
Sales ABC Mon Fri AMGABC01 18:00
Sales ABC Mon Fri AMGABC10 18:00
I was looking to have one entry for an AppName in the output, is it possible to do so if I change the lookup file like you shared, to have separate record for each jobname? If so, then I can change the lookup file like you mentioned. Sorry i should have made this clear the first time i posted the question. Thank you.
Hi @renjith.nair , is this possible, could you please let me know. Thank you.
@naraininfy,
Sorry missed your comment. Yes possible. Below is my data and search
index_jobruns
AMGABC01,PLINUX03,20180906 0300,20180906 0310,COMPLETE
AMGABC12,PLINUX01,20180906 0300,20180906 0310,FAILED
AMGABC10,PLINUX05,20180906 0400,20180906 0410,COMPLETE
AMGEFG65,PLINUX01,20180906 0300,20180906 0310,COMPLETE
AMGEFG70,PLINUX01,20180906 0400,20180906 0410,FAILED
jobsla.csv
Units,AppName,FreqStart,FreqEnd,JobName,SLA_time
Sales,ABC,Mon,Fri,AMGABC01,18:00
Sales,ABC,Mon,Fri,AMGABC10,18:00
Healthcare,EFG,Mon,Fri,AMGEFG65,01:00
Healthcare,EFG,Mon,Fri,AMGEFG70,01:00
Automotive,XYZ,Mon,Fri,AMGXYZ100,02:00
Automotive,XYZ,Mon,Fri,AMGXYZ150,02:00
Note:automotive was added to SLA but is not present in my index
Search
| inputlookup jobsla.csv |eval SLA_HR_MN=strptime(SLA_time,"%H:%M"),
SLA_HR=tonumber(strftime(SLA_HR_MN,"%H"))*60,
SLA_MIN=tonumber(strftime(SLA_HR_MN,"%M")),
SlaTimeSec=SLA_HR+SLA_MIN
| table AppName,JobName,Units,FreqStart,FreqEnd,SlaTimeSec
| join JobName type=outer [search index="index_jobruns" sourcetype="job_runs"
|eval end_time=strptime(JobEndTime,"%Y%m%d %H%M")|eval HR=tonumber(strftime(end_time,"%H"))*60,MN=tonumber(strftime(end_time,"%M")),EndTimeSec=HR+MN
|table JobName,JobStatus,EndTimeSec
]
| eval SLA_Status=if(JobStatus=="COMPLETE" AND EndTimeSec < SlaTimeSec,"Met","Missed")
| eval SLA_Status=if(FreqStart=="Sat" OR FreqStart=="Sun" OR FreqEnd=="Sat" OR FreqEnd=="Sun","NA",SLA_Status)
| stats values(Units) as Units,values(SLA_Status) as SLA_Status by AppName
Result
AppName Units SLA_Status
ABC Sales Met
EFG Healthcare Missed
XYZ Automotive Missed
Thank you so much!