How do I ensure that the timezone of a database input from Splunk DB Connect querying a server in an other timezone normalizes and is recorded as UTC in my indexers?

[RJ_Grayson]

I'm using Splunk DB Connect to pull data from an MS SQL database that is sitting on a server in the US Eastern time zone. The Splunk server with Splunk DB Connect is configured in UTC time. The time stamp column I'm using to extract the time stamp from the event is in Eastern time. All of the data I forward to Splunk is interpreted into UTC time but I'm having issues getting this Eastern time stamp from the database to be correctly indexed with a UTC time stamp for _time. We also use an intermediary heavy forwarder to receive the events from the Splunk DB Connect server before the events are forwarded to the indexers. It looks something like this:

MS SQL Database Server (EST) <--- Splunk DB Connect Server (UTC) ---> Intermediary Heavy Forwarder (UTC) ---> Indexer pool (UTC)

I've tried adding the following props.conf stanzas to both the Splunk DB Connect server and the Heavy Forwarder server but the events are still being indexes with an Eastern timezone time stamp.

[source::mi_input://database_input1]
TZ = US/Eastern

[source::mi_input://database_input2]
TZ = US/Eastern

We are using the output time stamp format of epoch with the following inputs.conf stanza.

output_timestamp_format = epoch

Could this be causing Splunk to automatically assume the epoch time is already in UTC? Perhaps I'm not fully understanding the the function of the TZ stanza.

How can I get Splunk to index the event from the database with a converted timestamp from EST to UTC?

Using Splunk 6.5.1 and Splunk DB Connect 2.4.0

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

[RJ_Grayson · Accepted solution]

So after pulling the majority of my hair out last week I finally figured this out.

TLDR; DBConnect does not play nice with a "Lightweight Forwarder"

The short version of the long story is that I did not realize the server we were trying to use as the DBConnect Splunk server was running Splunk with the Lightweight forwarder enabled. DBConnect was working in all capacity from what I could see and since we do all of our parsing at a heavy forwarding tier it took me a while to determine what the ultimate root cause of the timestamping issue was.

So the lightweight forwarder doesn't do any parsing which should have been taken care of by the heavy forwarding tier. The interesting thing was that all of the default time fields that are usually extracted with data, the same time fields that Splunk uses to adjust _time based on timezone, were all null. The only thing I can ascertain is that DBConnect wasn't playing nice since the lightweight forwarder app was enabled and wrote out the events with null time fields. This was causing ALL of the timezone configurations, regardless of where they were, to fail. The _time was never properly recorded since there wasn't any default time values for the calculation to be performed on in the first place.

Once I discovered this I abandoned the lightweight forwarder server and installed DBConnect on a full instance of Splunk on another server. I used the exact same configurations and everything worked as expected. All of the default time fields were populated and present and the timezone configurations were correctly utilized and correctly altered the value of _time based on those configurations.

[gjanders]

Since all our Oracle servers (or a similar type) are all in UTC timezone, I just set the sourcetype in the props.conf to a:

TZ = UTC 

And that seems to work fine.
I have not tested on a per source basis as you are doing, but would sourcetype be an option or do you need a more granular control?

[RJ_Grayson]

While pulling my hair out over this problem I discovered something that's pretty odd. ALL of my database inputs are being indexed with a blank date_zone field; regardless of any props.conf timestamping or timezone changes, either on the host with DBConnect or on the heavy forwarders. The date_zone field is blank, not even a "0" or "local".

I'm not sure what to make of that. I thought the date_zone field should always be generated/present when the event is parsed/indexed.

[RJ_Grayson]

To make things even weirder....none of the default datetime "date_*" fields are present in the database input data.

Completely missing:
date_hour, date_mday, date_minute, date_month, date_second, date_wday, date_year, date_zone

It almost looks like Splunk isn't performing any of the timestamping operations it's supposed to be after it's pulled the data from the database.

[RJ_Grayson]

So I've tried a handful of configuration combinations and haven't had any luck so far. I'm starting to think that either DBConnect somehow overrides Splunks native timestamp extraction configurations or there is a bug. Below are the combinations I've tried thus far as well as my DB input from the DBConnect inputs.conf.

DBConnect Server: No Timezone specified in props.conf (custom app)
Heavy Forwarders: Timezone by Sourcetype in props.conf (custom app)
Result: Doesn't change indexed _time

DBConnect Server: Timezone by sourcetype in props.conf (custom app)
Heavy Forwarders: Timezone by sourcetype in props.conf (custom app)
Result: Doesn't change indexed _time

DBConnect Server: No Timezone specified in props.conf, DATETIME_CONFIG = NONE instead (custom app)
Heavy Forwarders: Timezone by sourcetype in props.conf (custom app)
Result: Doesn't change indexed _time

DBConnect Server: Timezone by sourcetype in props.conf (system local)
Heavy Forwarders: Timezone by Sourcetype in props.conf (custom app)
Result: Doesn't change indexed _time

[mi_input://my_DB_input]
connection = Database
disabled = 0
enable_query_wrapping = 1
index = DatabaseIndex
input_timestamp_column_fullname = (001) my_querytable.Time.datetime
input_timestamp_column_name = Time
interval = 120
max_rows = 10000
mode = advanced
output_timestamp_format = yyyy-MM-dd HH:mm:ss
query = SELECT Time,Field1,Field2,Field3,Field4,Field5,Field6,Field7,Filed8,Field9 FROM "db"."db"."my_querytable" WHERE ID > ?
sourcetype = myCustomSourcetype
tail_rising_column_fullname = (002) my_querytable.ID.bigint
tail_rising_column_name = ID
ui_query_catalog = my_catalog
ui_query_mode = advanced
ui_query_schema = db
ui_query_table = my_querytable
tail_rising_column_checkpoint_value = 2214228803
fetch_size = 1000

[woodcock]

EXCELLENT QUESTION! I would test it out manually to check but I suspect that your theory is correct: it would make perfect sense for Splunk to assume that epoch is exactly that and to ignore any other timestamping settings. If that's the case, you will have to pull the time in as some other form and at that point, I know that you can effect timestamping with TZ (and other) settings (if deployed in the correct place).

[RJ_Grayson]

So I changed the timestamp output to "yyyy-MM-dd HH:mm:ss" and added the props.conf stanzas at both the heavy forwarder and on the server running DB Connect. It's still interpreting the time stamp literally and indexes it in EST.

I'll keep messing around with different configurations and see if I can find the Goldilocks zone.

[jcoates_splunk]

http://docs.splunk.com/Documentation/AddOns/released/Overview/Syslogandtimestamps

and

http://docs.splunk.com/Documentation/DBX/3.0.0/DeployDBX/Troubleshooting#Incorrect_timestamp_behavio...

might help as well.

[RJ_Grayson]

I just read the "incorrect_timestamp_behavior" portion of that page you linked. That sounds very much like what I'm seeing. I'm surprised I missed that in the docs. I'll give that a shot and let you all know if it works.