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!

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...

Index This | What goes away as soon as you talk about it?

May 2025 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this month’s ...

What's New in Splunk Observability Cloud and Splunk AppDynamics - May 2025

This month, we’re delivering several new innovations in Splunk Observability Cloud and Splunk AppDynamics ...