how to include only office hours in splunk query search?

[guru89044]

This is my query and its working fine. I want to modify this query to display only official hours data. Example: search should display 9am to 8Pm data from Monday to Friday only.Thank you.

index=x AND host=y AND earliest= "02/12/2018:00:00:00" latest="03/09/2018:00:00:00"
| rex "(?java?.[.\w]+Exception)"
| stats earliest(_time) AS First_MST_Time count BY methodPath,Exception | sort by count
| eval Release_Start_Date =strptime("2018-02-26 20:00:00", "%F")
| where First_MST_Time>=Release_Start_Date
| convert ctime(First_MST_Time)
| convert ctime(Release_Start_Date)

[gcusello]

Hi guru89044
You can insert in your main search the additional rules:

(date_wday=monday OR date_wday=tuesday OR date_wday=wednesday OR date_wday=thursday OR date_wday=friday)  (date_hour>8 date_hour<21)

but in this way you solve only a part of the problem because you still have to manage holydays:
to solve this problem you should create a lookup with all the days of one year and a code to indicate if it's a working day or an holyday, e.g. in a lookup called calendar.csv: 1 is an holyday and 2 is a working day

day,kind
2018-01-01,1
2018-01-02,2
2018-01-03,2
2018-01-04,2
2018-01-05,2
2018-01-06,1
2018-01-07,1
2018-01-08,2

and then use it in your search, eventually creating a macro.

Bye.
Giuseppe

[guru89044]

thank you very much. we can also use it as date_wday!="saturday" date_wday!="sunday"

[gcusello]

following Splunk best practices it's better to use date_wday=... than date_wday!=...
Bye.
Giuseppe

[cmerriman · Accepted solution]

you should be able to add something like

index=x AND host=y AND earliest= "02/12/2018:00:00:00" latest="03/09/2018:00:00:00" date_wday!="saturday" date_wday!="sunday" date_hour>=9 date_hour<=20
| rex "(?java?.[.\w]+Exception)" 
| stats earliest(_time) AS First_MST_Time count BY methodPath,Exception | sort by count
| eval Release_Start_Date =strptime("2018-02-26 20:00:00", "%F")
| where First_MST_Time>=Release_Start_Date 
| convert ctime(First_MST_Time)
| convert ctime(Release_Start_Date)

[guru89044]

@cmerriman Thank you very much.