All Apps and Add-ons
Highlighted

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?

Path Finder

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

Highlighted

Re: 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?

Esteemed Legend

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

0 Karma
Highlighted

Re: 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?

Champion
0 Karma
Highlighted

Re: 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?

Path Finder

I just read the "incorrecttimestampbehavior" 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.

0 Karma
Highlighted

Re: 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?

Path Finder

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.

0 Karma
Highlighted

Re: 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?

Path Finder

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

[miinput://myDBinput]
connection = Database
disabled = 0
enable
querywrapping = 1
index = DatabaseIndex
input
timestampcolumnfullname = (001) myquerytable.Time.datetime
input
timestampcolumnname = Time
interval = 120
maxrows = 10000
mode = advanced
output
timestampformat = 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
tailrisingcolumnfullname = (002) myquerytable.ID.bigint
tailrisingcolumnname = ID
ui
querycatalog = mycatalog
uiquerymode = advanced
uiqueryschema = db
uiquerytable = myquerytable
tail
risingcolumncheckpointvalue = 2214228803
fetch
size = 1000

0 Karma
Highlighted

Re: 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?

Path Finder

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 datezone field; regardless of any props.conf timestamping or timezone changes, either on the host with DBConnect or on the heavy forwarders. The datezone 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.

0 Karma
Highlighted

Re: 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?

Path Finder

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

Completely missing:
datehour, datemday, dateminute, datemonth, datesecond, datewday, dateyear, datezone

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.

0 Karma
Highlighted

Re: 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?

SplunkTrust
SplunkTrust

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?

0 Karma
Highlighted

Re: 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?

Path Finder

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.

View solution in original post

Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.