Configuring Time Zone of Source

[timgajewski]

Hello, I am indexing data from an MS SQL database using the DB Connect App. The time format is in Unix epoch and is being entered in the database wrong (instead of UTC, it is in UTC + 5:00). Splunk thinks it is in UTC, so it subtracts 5 hours for my timezone (Eastern). While I am working with the application administrator to figure out why time is being entered wrong, I want to tell Splunk the data is in UTC + 5:00, so I see the correct time an event happens - it currently shows that is in the future.

I read all of the articles about configuring the time zone in props.conf. I edited the existing props.conf in /opt/splunk/etc/apps/dbx/default to include the following:

[source::dbmon-tail://database name/index name]
TZ = Asia/Karachi

I then restarted the Splunk server, both through the web interface and via command line. The data is still being indexed 5 hours in the future. I tried entering the timezone as GMT+5:00, different Asian time zones, etc. Does anybody have any suggestions?

[xbudahx]

I had the same issue in DB Connect, our DB is in CST but Splunk was indexing that data at GMT. I ended up just converting in the SQL as shown below.

EVENT_TIMESTAMP + (SUBSTR(TZ_OFFSET('Canada/Central'),3,1))/24 AS EVENT_TIMESTAMP_GMT

[vanvan]

Mmm, this will work ONLY if your DB is Oracle...

For me the solution was to specify the Timezone value in the Connection setting in DB Connect app configuration page. It works for the entire DB, which means that all timestamps in the connection will be treated with the same time zone.

If you want to have different Timezone for different tables in the same DB then you can have a workaround by setting up different connections in DB connect app to the same DB, only using different timezone setting and name.

[kristian_kolb]

As martin_mueller points out, the timestamp extraction takes place before any regex transforms of the raw data.

http://wiki.splunk.com/Community:HowIndexingWorks

/k

[linu1988]

yes, at last we want the value in search

[martin_mueller]

Calculated fields are processed at search time.

As for modifying the timestamp before Indexing, thats going to require external processing... See one of my Questions on that topic.

[linu1988]

As per Martin's comment if the TZ will be skipped, why don't you give eval a try?

EVAL-<fieldname> = <eval statement>
* Use this to automatically run the <eval statement> and assign the 
  value of the output to <fieldname>.  This feature is referred to as 'calculated fields'.

it would calculate the difference for you.

[timgajewski]

Assuming that the TZ parameter has no impact to time in epoch form - is there a way to modify the timestamp before indexing? For example, it looks at the time field - which is receivedutc=, then it takes that number and subtracts a value to make the timestamp correct, finally indexes the data? I've been looking for a way to modify data before it gets indexed and I'm not coming up with much. TIA

[martin_mueller]

The doc on the TZ parameter doesn't say so, but I wouldn't be surprised if timezone modification were skipped for epoch timestamps - after all, those are in UTC already.

[timgajewski]

Correct. My suspicion is that the application writing the data is set to a different timezone than the server itself - without those admins here, I would like to 'fix' the data being received by giving it a different timezone - GMT+5:00.

[kristian_kolb]

Weird... I thought epoch was UTC and UTC only. Or well not really UTC technically (handling of leap seconds), but for most intents and purposes it is.

Does this mean that 'epoch' written to your database tables is epoch + 3600 * 5 ?