All Apps and Add-ons

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

evelenke
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

chrismewett
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

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

AnilPujar
Path Finder

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

0 Karma

itrimble1
Path Finder

What version of Splunk are you using ?

0 Karma

thambisetty
Super 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

princemanto2580
Explorer

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

chrismewett
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

thambisetty
Super 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

woodcock
Esteemed Legend

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

0 Karma

thambisetty
Super 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

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

woodcock
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

thambisetty
Super 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
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.