Splunk Search

How to search only events based on values from a field in lookup file?

naraininfy
Explorer

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
Tags (3)
0 Karma
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

@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
Happy Splunking!

View solution in original post

mstjohn_splunk
Splunk Employee
Splunk Employee

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.

renjith_nair
SplunkTrust
SplunkTrust

@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
Happy Splunking!

naraininfy
Explorer

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.

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

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
Happy Splunking!
0 Karma

naraininfy
Explorer

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.

0 Karma

naraininfy
Explorer

Hi @renjith.nair , is this possible, could you please let me know. Thank you.

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@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 
Happy Splunking!
0 Karma

naraininfy
Explorer

Thank you so much!

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...