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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...