How can my dashboards switch over to the next day at 12AM ETC rather than 12AM UTC?

[adnankhan5133]

All of our Splunk users, including members of our Leadership Team are currently in the US/Eastern time zone. All of the incoming logs are being indexed at UTC, and the indexes are being used to create dashboards for our Leadership Team. There is a desire for the dashboards to switch over to showing data for the next day at 12AM ETC rather than 12AM UTC.

UTC is 4 hours ahead of ETC.

The logic that the team is currently using to "force" two of our dashboards to display data in accordance to ETC time is as follows (the entire SPL isn't included since it may not be relevant to the problem we're trying to solve):

--- Dashboard 1

index=data1 sourcetype=datatype1

| eval epoch_Timestamp=strptime(Timestamp, "%Y-%m-%dT%H:%M:%S.%3QZ")-14400

--- Dashboard 2

index=data2 sourcetype=datatype2

| eval epoch_file_create_date=strptime(file_create_date, "%Y-%m-%d %H:%M:%S.%3Q")-14400, epoch_file_update_date=strptime(file_update_date, "%Y-%m-%d %H:%M:%S.%3Q")-14400

Notice that there is a 4 hour offset in both SPL queries. This is the team's rationale for converting the data associated with incoming logs from UTC to ETC. However, this approach alone isn't causing the dashboards being populated with data to switch over to the next day at 12AM ETC. Instead, the switch-over is occurring at 12AM UTC. So, basically, any data generated starting at 8PM ETC is being categorized over to the next day, when the expectation is that it gets categorized for the current day.

Also important to note is that props.conf on the indexer is currently not configured with a timezone setting to US/Eastern.

What is the best approach to get the dashboards to switch-over at 12AM ETC rather than 12AM UTC? Should props.conf be updated AND the -4 hr offset be removed from the SPL queries? Or, is there a better approach that can be taken?

[thambisetty]

The better approach of indexing events with correct timestamp is to tell Splunk timezone of events.

If you set TZ correctly for your events, you will not need to convert event timestamps for the users in different timezones manually with SPL, instead you can set user TZ in the user profile. This will automatically change timestamp of events.

for example:

event 1 occurred 00:00 UTC

The same event time will be shown 8PM ETC if the profile is set to ETC. 

you mentioned that 8PM ETC is being categorized 12AM UTC and its coming next day.

you can avoid this by setting TZ for your events in props.conf.

[sourcetype]
TZ=GMT-4

when you apply above setting in HF/Indexer,

users in ETC(by the way I dint find this right timezone)  will see that event occurred at 8PM only not 12AM.

[adnankhan5133]

Hi @thambisetty  - To clarify, ETC is basically the US/Eastern TZ (i.e. "US - New York").

So, at the moment, when the search results generate, the "Time" column  appears in ETC while the timestamp in the event log appears as follows - here is a sample log:

Time	Event
8/11/2020 2:23:15:126PM	2020-08-11 18:23:15.126, file_create_date="2020-08-11 18:23:15.128+00", file_update_date="2020-08-11 18:23:15.468+00", filesize="3 MB", id="14", interface_id="Interface1", integration_name="Daily_File_5",

Are you stating that updating props.conf to the correct TZ on the indexers for the sourcetype or index associated with this event log would result in the file_create_date, file_update_date fields, and event timestamp appearing in US/Eastern time ? These fields are currently generating in UTC. 

[to4kawa]

index=_internal 
| head 1 
| fields _raw 
| eval _raw="2020-08-11 18:23:15.126, file_create_date=\"2020-08-11 18:23:15.128+00\", file_update_date=\"2020-08-11 18:23:15.468+00\", filesize=\"3 MB\", id=\"14\", interface_id=\"Interface1\", integration_name=\"Daily_File_5," 
| rex "(?<time>\S+ \S+)," 
| rex max_match=0 "(?<name>\S+)=(?<value>[^,]+)" 
| rex field=name mode=sed "s/(.*)/\"\1\"/g" 
| eval tmp="{".mvjoin(mvzip(name,value,":"),",")."}" 
| spath input=tmp 
| fields - tmp name value _raw  
| table file_* time 
| eval time_epoch=strptime(time,"%F %T.%3Q")
, create_epoch=strptime(file_create_date,"%F %T.%3Q+00")
, update_epoch=strptime(file_update_date,"%F %T.%3Q+00")
| foreach *_epoch [|eval <<MATCHSTR>>_local=strftime(<<FIELD>>,"%c %Z")]
| eval offset = strftime(time_epoch,"%:::z")
| eval tz_ETC="-4"
| eval time_args = tonumber(if( -1 * offset >= 0, "+".substr(offset,2,3), printf("%03d",-1 * offset)))+tz_ETC
| foreach *_epoch [ eval <<MATCHSTR>>_forcedETC=relative_time(<<FIELD>>,time_args."h")
| eval <<MATCHSTR>>_HR=strftime(<<MATCHSTR>>_forcedETC,"%F %T.%3Q")]

1. epoch time is  UTC.
2. _time is epoch time.
2. _time displays local time by user preference.
3. timepicker use local time(user preference time zone).

If user preference time zone is ETC, there is no problem.