Splunk DB Connect: How to set correct timezone for Splunk DB Connect feeds?

[evelenke]

Hi Splunkers,

we have DB with events in UTC which differs from local timezone.
Setting up TZ (timezone) in props.conf for Splunk DB Connect 3.01 sources doesn't work (upd. worked in prev. version)

Here's configuration:

[source::my_source]
TZ = UTC
TIME_FORMAT = %y-%m-%d %H:%M:%S.%3N

I may create a new field with timestamp value needed, but is there any way to convert time of events to correct TZ for Splunk DB Connect?

[anatoliikostin]

Time zone can be defined for every connection:
Configuration -> Databases -> Connections -> "your connection" (Timezone dropdown)

It worked for my case where DB has UTC and local user UTC +2.

[AnilPujar]

Yes, the timezone is set to Asia/Dubai, but still the data time is 4 hour less.

[itrimble1]

What version of Splunk are you using ?

[thambisetty]

@princemanto2580

Logs are logging with GMT. Since I am from GMT+4 added 4 hours to match my local timezone. I believe mcafee logs event with GMT find your timezone and add/substract hours based on your timezone.

[princemanto2580]

Hi,

I tried with "SELECT dateadd(HOUR, 4, [EPOEvents].[ReceivedUTC]) as [timestamp]," but still is showing the difference with 4 hours. Can you help on this?

[chrismewett · Accepted solution]

On does not simply set a timezone flag in props.conf for dbconnect inputs - the app appears to do timestamping before it gets to props.conf processing.

Add the following to the JVM options in the configuration tab of the DB connect app:
-Duser.timezone=GMT

A challenge with this approach is that it means that all database logs on this forwarder are ingested in GMT/UTC, so if you have different databases logging in different timezones, you'll need a different dbconnect app / forwarder combination for each one.

(Ideally we'd be able to set it on a per connection basis instead of per JVM, but it is not this day).

[thambisetty]

Hi Adam,

Thanks for your reqply..
I have gone through the db connect document and modified SQL query. Instead of applying TZ in props.conf

[woodcock]

You should click Accept on this answer to close the question.

[thambisetty]

Hi,

There is known bug in DB connect. props can't be overridden.

Reference: DB connect release notes: link text

Here is the solution which I have come up with. you can use if you like this.

My McAfee logs in UTC & My Splunk server is running in UTC+4.

I have added below line to query it self.

SELECT dateadd (hour , 4 , [EPOEvents].[ReceivedUTC]) AS [timestamp] from xyz

you can look for sql functions as per your database & I found this is best solution as of now.

[abalogh_splunk]

There is an issue with DBX 3.0.2 that it does not honor props.conf. I have not tested version 3.0.3 yet.

[woodcock]

This definitely should work. Try deploying this on both your forwarders and your indexers. Starting with v6.0 the Forwarders will pass this setting to the Indexers and the Indexers will honor it. This means you will only have to restart Splunk on your Forwarders. If this doesn't work, then deploy the setting to your Indexers but you will need to restart Splunk on your Indexers to activate it. And even then, only your newly-forwarded events will be modified; the pre-fix events will stay broken forever.

[thambisetty]

Hi,
This is not working.. I tried applying the above mentioned settings in HF and Indexer. but , there is no luck