All Apps and Add-ons

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

Contributor

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?

1 Solution

Explorer

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).

View solution in original post

Explorer

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.

0 Karma

Path Finder

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

0 Karma

Path Finder

What version of Splunk are you using ?

0 Karma

Champion

@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.

————————————
If this helps, give a like below.
0 Karma

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?

0 Karma

Explorer

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).

View solution in original post

Champion

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

————————————
If this helps, give a like below.
0 Karma

Esteemed Legend

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

0 Karma

Champion

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.

————————————
If this helps, give a like below.
0 Karma

Splunk Employee
Splunk Employee

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.

Esteemed Legend

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.

0 Karma

Champion

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

————————————
If this helps, give a like below.
0 Karma