How to deal with different timestamp columns from database input data fetched using Splunk DB Connect?

[AditiKulkarni]

In our business case, there are different timestamp columns with different timezones. The timezones are not mentioned explicitly in the incoming data. We need to convert only a few of the incoming timestamp columns into required timezones. From splunk documentation, I have come to know that we can change the timezone of the input timestamp column by making a timezone setting in props.conf file, but with these settings, we can only change the timezones based on host, source or sourcetype. Is there a way to change the timezone of only a particular timestamp column coming from particular host and not of all the timestamp columns from particular host? We are fetching data from a database using DB Connect.

[woodcock]

There are 3 things. First is timestamping which is the encoding of your timestamps inside the Indexers' DB. Splunk encodes all events internally with GMT/Zulu/CDT but you have to teach it exactly how to translate the timestamps in your data by using the correct TZ. If you configure Splunk with a TZ string that includes DST changes, it does this correctly automatically. That is why I said that the very first thing you need to do (and you NEED to do it) is configure the TZ CORRECTLY for the time in each input/source/host (whatever breakdown your timestamps follow). That is so that Splunk encodes each event with a correct time (this can never be changed so you must get it right). So if you use TZ = US/Central, it will go back and forth with DST, just as your data does.

The second thing is how this is presented to users. Go to `->Edit account->Time zone` and set your preferred local time. Splunk will use this TZ to retranslate events to present them back to you. It also uses this value to translate time search parameters that you give it (e.g. what is "today" or when is "-1d@d"). Based on this setting (if you set it correctly), DST will be handled automatically, too.

The last thing is manually interpreting timestamps that are NOT the event's internal _time field (some events have more than 1 time).

Based on these 3 things, the only one that you should need to do manually is the last one.

[jacobwilkins]

Since you are fetching the data using dbconnect, can you modify your query to output the timestamp in a format that includes the timezone?

[AditiKulkarni]

I don't think so, but if we do so, can we do timezone conversions effectively?

[woodcock]

There are 2 different things. First, the settings you discuss apply to the _time value that Splunk assigns to each event and this absolutely must be done correctly. Be sure to read all about it here:

http://docs.splunk.com/Documentation/Splunk/6.2.3/Data/Applytimezoneoffsetstotimestamps

Another option is to re-write the events before you index them to include the TZ values where you'd like them. I think this is a poor option.

The other thing you asked is about converting some times inside the event differently than others, or on the fly. You can do that like this:

... | eval epochtime=strptime(your_current_time_field, "Your-input-strftime") + <your TZ offset, e.g. "300"> | eval desired_time=strftime(epochtime, "Your-output-strftime")

http://docs.splunk.com/Documentation/Splunk/4.3.1/SearchReference/CommonEvalFunctions
http://strftime.org

[AditiKulkarni]

You have mentioned the conversion of timezones on fly like :
... | eval epochtime=strptime(your_current_time_field, "Your-input-strftime") + | eval desired_time=strftime(epochtime, "Your-output-strftime")

Does this mean that there is no inbuilt function in splunk to convert one timezone into another and we need to do it by adding/subtracting the hardcoded timeoffset? So, in this case, during on/off of daylight saving developer need to go to the code and change offset manually and there is no way to handle it automatically in splunk.

Could you please confirm this?

[AditiKulkarni]

From this i understand, timezone conversion is applicable if there is one timezone coming with input data. What if there are multiple timezones coming in different columns with input data? Is there a way to convert all these different timezones in input data to a single timezone?

[woodcock]

You have to know your data well enough to know what TZ is being used for the dates that are inside it; often there are more than 1 TZ being used. Once you teach Splunk about the timezones, it will automatically normalize them to each user's timezone whenever it presents results, as configured by`->Edit account->Time zone`. If you can get the data from your query to to include the TZ, it is easy to tell Splunk to use that incoming TZ value.