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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...