How to produce hourly stats by day of the week in Pacific Time?

[ShagVT]

I've been asked to produce a report with typical hourly volumes for our application on Fridays. So I put together this snappy search.

index=prod event_name="LOGIN" date_wday=friday 
   | eval hour=strftime(_time, "%H")
   | timechart cont=false span=1d limit=0 useother=false count by hour

I ran this year to date. Things are close but not quite right because my data are collected in pacific time but I'm in eastern time. I would like to generate the report in pacific time (which the data were written/collected in.)

It seems like all three lines of my search might be wrong:
- Does date_wday look at Friday pacific time or Friday eastern time?
- I'm pretty sure the eval is printing the hour in eastern time
- Do I need to change my timechart with something like aligntime=@d-3h?

When I run my search I see hours 3-23 showing up on Friday and hours 0-2 showing up on Saturday.

[grittonc · Accepted solution]

Do you know what your timezone is set to in your user settings? Splunk stores _time in UNIX time but displays it for you in your timezone. So yes, your hour calculation is going to be based on your timezone. See https://docs.splunk.com/Documentation/Splunk/7.2.6/Knowledge/Usedefaultfields#Internal_fields and https://docs.splunk.com/Documentation/Splunk/7.2.6/SearchReference/DateandTimeFunctions#time.28.29

To see how Splunk would convert a UNIX time according to your user settings, try:

| makeresults 
| eval right_now=1559154449, my_hour=strftime(right_now, "%H %Z")

Does date_wday look at Friday pacific time or Friday eastern time?
date_wday is a default datetime field, which, according to the documentation, are literal values from the event.

The datetime values are the literal values parsed from the event when it is indexed, regardless of its timezone. So, a string such as 05:22:21 will be parsed into indexed fields: date_hour::5 date_minute::22 date_second::21.

But, if you have a date_wday field, do you have a date_zone field as well? That should tell you what TZ was used for date_wday.
https://docs.splunk.com/Documentation/Splunk/7.2.6/Knowledge/Usedefaultfields#Default_datetime_field...

I'm pretty sure the eval is printing the hour in eastern time
Yes, it probably is if you have your TZ set to eastern. This probably won't help unless you want to convert to Pacific.

Do i need to change my timechart with something like aligntime=@d-3h?
If you're doing an hourly timechart, why not use span=1h@h? I don't understand having a span of 1 day and then counting by a calculated hour field. If you have date_wday in Pacific time, and Pacific time is how you want to report, have you tried | chart count by date_hour?

[ShagVT]

So changing my account to pacific time, removing the eval line, and using date_hour gave me the results I was looking for, which is good.

Do you mind explaining what span=1h@h does? I'm not familiar with that notation.

[grittonc]

Sorry, the span=1h@h was a red herring. According to the docs, the snap-to option (the @) is only used with the week span. If you try some timecharts with your own data, with either span=1w and span=1w@w, you'll see what the difference is.

As for producing the results that you want without changing your timezone settings, that's an understandable want. Based on @woodcock 's answer here: https://answers.splunk.com/answers/321361/how-to-search-accounting-transactions-based-on-the.html You could try something like this: | eval hour_my_tz=strftime(_time, "%H %z"), wd_my_tz=strftime(_time, "%A"), hour_pacific_tz=strftime(_time-3*60*60, "%H"), wd_pacific_tz=strftime(_time-3*60*60, "%A") which should work despite daylight savings time because the difference is always 3 hours. You would just have to make sure to pick a large enough time range for your search to get all of the PT events if your system is set to ET. If you add the eval statement above to your search you'll see where the wd_my_tz is not the same as wd_pacific_tz.

[ShagVT]

Also - is there anyway to designate in my query that I want everything in pacific time, rather than having to change my account settings?