Trying to create a calculated field to indicate if an event occured in a specific time window in a specific time zone regardless of where the user is running the search from - so no adjustment to local timezone.
First off, this is my first submitted question as I am a new SPLUNK user...so not used to the whole world of SPLUNK yet.
Here's our issue, we have a need to indicate and account for events that are happening in certain time windows in a specific time zone.
For instance we want to calculate the average CPU utilization from 10 to 11 Central Time for the previous Day/Week/Month for use in a management report.
I created a calculated field that looks like this: PEAKHOUR = if((strftime(_time, "%a") ="Sat" or strftime(_time, "%a") ="Sun"),"NO",if(tonumber(strftime(_time, "%H"))>=10,if(tonumber(strftime(_time, "%H"))<11,"YES","NO"),"NO"))
and we include it in a dashboard which we select using a drop down, previous day/week/month and then filter for PEAKHOUR in our search like this: index=performance_stats PEAKHOUR="YES" | blah blah blah | table HIGHCPU, AVERAGECPU
The issue we currently have is if someone in Eastern time runs it, they end up looking at a time window an hour before 10 - 11 Central and someone in Pacific time sees events for 2 hours after that window. It would seem that SPLUNK is adjusting the calculated field to local time before doing the check for the correct hour...we need it to always check to see if it's 10 to 11 Central.
I was thinking that we need to convert the event time to epoch time and then check for the time window (Would Daylight Savings affect things??)...thought I would ask here before I go that route in case I'm missing something obvious.
Splunk always stores the timestamp in the index in epoch time - but it always adjusts and uses the user's settings when running a search or displaying a dashboard. If the user's profile is set to EST, they will see EST in their results and EST will be used to select the data for the dashboard or search.
So if you want to see things in a particular timezone, the only way I know is this:
- write the search using the PEAKHOUR field as you have defined it (or whatever makes sense - see below)
- change the user profile to CST if you want to see the results in terms of CST, change to EST if you want results in EST etc.
AFAIK, there is no way to determine what the user's current time zone setting is, nor is there any way to ignore or override it.
However, IF there is a timestamp that appears in the raw event data, Splunk will automatically create fields named date_* (date_wday, date_mday, date_hour, etc.) IF you have those fields, they will always use the time stamp of the event; these fields are not adjusted to UTC or to the user's time zone setting. So you may be able to do what you want by using those fields instead of _time, like this:
PEAKHOUR = case(date_wday="Saturday" or date_wday="Sunday","NO",
(I think the case function is easier to read than the if function.) I would not create this as a calculated field, because then PEAKHOUR will be calculated whenever the data is returned from any search - that's some overhead. Instead, I would use the peak hour calculation within my search, something like this:
yousearchhere earliest=somethingEarlier latest=somethingLater
| where date_wday!="Saturday" and date_wday!="Sunday" and date_hour =10
In this case, just make sure that the earliest and latest for search will capture all the data that you want, as you will be generating the specific subset in the second step.