Dashboards & Visualizations

How do I search to include/exclude weekends based on naming convention of field?

x213217
Explorer

Hello,

I need to calculate the average runtimes for jobs over the last 60 days. For some jobs, i only want to look at weekdays, and for some jobs, I want to include both weekdays & weekends.

For example jobNames that have wkly in the name should include both weekdays & weekends. Everything else just weekdays.

Here is the search so far. Right now, it is a blanket search to exclude weekends for everything. How do I include weekends for when isWeekly = "weekly"

index=autosys source="Jobs" status="RUNNING" jobName=(some jobname filtering) earliest=-60d@d
    | eval isWeekly=if(LIKE(jobName,"*wkly*"),"weekly", "notWeekly")
    | eval day_of_week = strftime(_time,"%A")
    | where NOT (day_of_week="Saturday" OR day_of_week="Sunday")
    | stats avg(runTime) as "Average Runtime in Seconds" count(jobName) as "Run Count" by jobName
    | sort -"Average Runtime in Seconds"
    | sort -_time
    | eval strSecs=tostring('Average Runtime in Seconds',"duration")
    | eval strFormatSecs=if(match(strSecs,"\+"),replace(strSecs,"(\d+)\+(\d+)\:(\d+)\:(\d+)","\1 Day \2 Hr \3 Min \4 Secs"),replace(strSecs,"(\d+)\:(\d+)\:(\d+)","\1 Hr \2 Min \3 Secs")) 
    | rename strFormatSecs AS "$CompletionType$: Average Runtime"
    | rename "Run Count" AS "$CompletionType$: Run Count"
    | fields jobName "$CompletionType$: Average Runtime"
Tags (1)
0 Karma

twinspop
Influencer

One solution: Assign the value(s) you're evaluating to new fields conditionally. Sample:

...| eval DAY=strftime(_time,"%a"), 
      RUNTIME=if(match(DAY,"Sat|Sun") and !match(jobName,"wkly"),null,runTime),
      RUNTIME=if(!match(DAY,"Sat|Sun"),runTime,RUNTIME)
   | stats sum(RUNTIME) as RUNTIME by jobName

If the log is on the weekend and does not have wkly in the name, RUNTIME will be null. When DAY is a weekday, set RUNTIME to runTime, else keep the RUNTIME value from the first eval.

0 Karma

Vijeta
Influencer

Just replace your where condition

| where NOT (day_of_week="Saturday" OR day_of_week="Sunday") with

 | where (isWeekly="notWeekly" AND NOT (day_of_week="Saturday" OR day_of_week="Sunday")) OR isWeekly="weekly"
0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud's AI Assistant in Action Series: Auditing Compliance and ...

This is the third post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Community Badges!

  Hey everyone! Ready to earn some serious bragging rights in the community? Along with our existing badges ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...