topic Re: drilldown of timechart changing to epoch time in user's time zone in Splunk Search

drilldown of timechart changing to epoch time in user's time zone

loganramirez — Mon, 20 May 2024 19:31:23 GMT

I have a dbxquery command that queries an Oracle server that has a DATE format value stored in GMT.

My SQL converts it to SQL so I can later use strptime into the _time value for timecharting:

SELECT TO_CHAR(INTERVAL_START_TIME, 'YYYY-MM-DD-hh24-mi-ss') as Time FROM ...

Then at the end of my SPL:

... | eval _time=strptime(TIME,"%Y-%m-%d-%H-%M-%S") | timechart span=1h sum(VALUE) by CATEGORY

On the chart that renders, we see values in GMT (which we want).

My USER TIMEZONE is Central Standard, however, and not GMT.

When I click (drilldown) a value $click.value$, it passes the epoch time CONVERTED TO CST.

As an example, if I click the bar chart that is for 2PM today, my click-action parm is 1715972400.000 which is Friday, May 17, 2024 7:00:00 PM GMT - 5 hours ahead.

I validated this by changing my user tz to GMT and it passes in the epoch time in GMT.

I googled 'splunk timezone' and haven't found anything, yet, that addresses this specifically (did find this thread that is related, but no solution https://community.splunk.com/t5/Dashboards-Visualizations/Drill-down-changes-timezones/m-p/95599)

So wanted to ask here!

It's an issue because the drilldown also relies on dbxquery data, and so my current attack plan is to deal with the incorrect time on the drilldown (in SQL), but I can only support that if all users are in the same timezone.

In conclusion, what would be nice is if I could tell Splunk to 'not change the epoch time' when clicked.

I think!

Re: drilldown of timechart changing to epoch time in user's time zone

gcusello — Tue, 21 May 2024 05:47:47 GMT

Hi @loganramirez ,

usually Splunk displays date in the timezone defined for the user.

to pass a timestamp in a different timezone, use eval and pass the transformed value instead of the original one.

Ciao.

Giuseppe

Re: drilldown of timechart changing to epoch time in user's time zone

loganramirez — Tue, 21 May 2024 14:27:57 GMT

so use eval and transform the epoch value to the desired tz?

i haven't found a built in Splunk function for that, just threads like this that use the offset, but since that changes from 5 to 6 hours with daylight savings, do you know of one that supports 'cst6cdt'?

and thank you! overall that approach makes sense to me. pass something (make something to pass) other than the click.value.

Re: drilldown of timechart changing to epoch time in user's time zone

gcusello — Tue, 21 May 2024 15:06:48 GMT

Hi @loganramirez ,

you can use the solution in the shared link or the fuction relative_time in eval.

Ciao.

Giuseppe

Re: drilldown of timechart changing to epoch time in user's time zone

loganramirez — Tue, 21 May 2024 15:17:43 GMT

The problem with both of those is it does not account for the 5 vs 6 hour shift between CDT and CST.

That is, solutions like this that use relative_time, manually subtract 5 or 6 hours, but do not differentiate when to make that shift (March-ish to November-ish), but Splunk has TZ awareness since the user can set their profile.

Seems like there should be a way (a function?) to tap into that, but something like

relative_time(epoch, "CST6CDT")

doesn't seem exist.

Many thanks for the great conversation as, per usual, learning!