How to convert the reported Pacific Time (PT) from one source to Universal Time Coordinated (UTC) within my search?

[bretai2k]

I am currently working on a report with 3 different data sources. Two of these sources report events in Universal Time Coordinated (UTC), and the third reports in Pacific Time (PT). My reports purpose is to take the events coming from the UTC sources, and then compare when those events occurred against the third source, which is reporting in PT. This is throwing the results of my report off. Is there any way I can convert the PT reported time to UTC within the search?

Thank you,
Ryan

[cmerriman]

If you take the PT time and just convert it with mktime, it will shift it to epoch (UTC). Change the timeformat as needed. To account for DST, use the date_zone field to shift the new epoch time field. This is what we've done, but to a further extent and this is just a snippet of it, and it seems to work.

| convert mktime(PTTimeField) as epochPTDateTime timeformat="%Y-%m-%d %H:%M:%S.%6N %:z" 
| eval shifted_PT_time=PTTimeField-(abs(date_zone)*60)

[woodcock]

You need to fix your PT source by adding TZ=PST for the host in props.conf and sending this to the forwarder and restarting splunk there. The problem is that the timestamp is WRONG in splunk. Fix this and the report will work fine.

[bretai2k]

My source (from what I understand), is a log file generated by a device that is not a server. I'm not involved with the ingestion piece of the data, just asked to build solutions on the data to provide the information my customers want. I'm not sure if there is a host or not. I'll have to check on the ingestion process to see if this can be configured.

[woodcock]

There is really no sense in leaving it broken. There is only one "time" in Splunk and it is UTC. Splunk converts to UTC and then adjusts how it presents data to users based on each one's Time zone setting in the preferences. You absolutely HAVE to get the time interpreted correctly on the way in or your events are trash. If you cannot access your forwarders, you can put the same props.conf file with the same TZ setting for this host on your Indexers and handle it that way. The point is that you MUST get it right on the way in.

[bretai2k]

Sorry for the delays in response, I have been working on other non-Splunk related projects. Unfortunately, I have very little to do with anything involved with Splunk. The indexers are owned by one group, the devices generating data another group. I'm trying to work with them to get the data sorted out. From what I can tell, there is nothing generated by these devices which identifies the TZ they are currently active in. I also don't know what other reporting and/or dashboards these reports are being used for, so I'm not even sure if I can simply get the data changed. This might take a little time to get what I need so I can get the proper data in there.

Thanks for your help so far, and I'll keep you all updated on my progress.

[woodcock]

The best way is to put the TZ into the event data and adjust the TIME_FORMAT to use it. If this cannot be done, then you need to use a TZ= setting (probably based off of the host values) in props.conf to say ServerX is TZY.

[DalJeanis]

Sure. As long as the time zone is part of the timestamp, then strptime will convert to UTC for you. Use the %Z option.

Assuming that your local PST time stamp field, myPSTtime had no time zone in it, and a date of "03/11/2017 13:21:17", you'd convert using a method like this run-anywhere code.

| makeresults
| eval myPSTtime="03/11/2017 13:21:17"
| eval myPSTtimeEpoch=strptime(myPSTtime,"%m/%d/%Y %H:%M:%S")
| eval myUTCtimeEpoch=strptime(myPSTtime." PST","%m/%d/%Y %H:%M:%S %Z")
| eval myUTCtime=strftime(myUTCtimeEpoch,"%m/%d/%Y %H:%M:%S %Z")
| table myPSTtime myPSTtimeEpoch myUTCtime myUTCtimeEpoch

---

Two things to note here... first, PST is a valid TZ, PT is not.

Second, epoch time is implicitly in UTC, so an epoch-time-formatted variable that contains anything other than UTC is... wrong.

| eval myPSTtimeWithWrongTZ=strftime(myPSTtimeEpoch,"%m/%d/%Y %H:%M:%S %Z")

[bretai2k]

Thank you DalJeanis, I will try this out tomorrow. Question though, does the solution you provide account for DST?

[cmerriman]

do your sources have date_zone not equal to 'local'?