Archive

how to include only office hours in splunk query search?

Explorer

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)

0 Karma
1 Solution

Super Champion

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)

View solution in original post

SplunkTrust
SplunkTrust

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

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Super Champion

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)

View solution in original post

Explorer

@cmerriman Thank you very much.

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!