How to configure indexing of historical data from a database to be based off timestamp in rising column?

[ronak]

Setup

• I've a db table job_run with five different timestamps (TS1 ~ TS5).
  
• Total fields in table to be pulled into Splunk are 8.
• The rising column is TS1 (first column) and is in yyyy-mm-dd hh24:mi:ss format .
• The remaining TS columns are either EPOCH or yyyy-mm-dd or hh24:mi
• I've specified TS1 as timestamp column in DB Input form where I define the db input with all the details

Need

What I'm trying to achieve is,

1. Incremental pull happens based on rising column TS1
2. When data is indexed, the column TS1 is used for indexing
3. When I pull historical data, the indexing considers content of TS1 for indexing as opposed to indexing the records at the time of the pull (in which case, entire of historical data gets indexed with pull time as opposed to actual record generation time in database which is indicated by TS1)

Issues I'm facing

1. When I pull historical data, the index timestamp becomes that of pull time instead of TS1 . Same behavior is observed when incremental runs happen.

The impact of this behavior is that, I cannot do historical pull as searches will not work with time picker. Search will not display the results because search will not find the data for historical duration say last two weeks, as all the historical data is indexes with pull time which is now.

How do I overcome this issue?

[musskopf]

Hello Ronak,

I'm assuming you're using the DB Connect App, right? If that's the case, have a look on a similar question:

http://answers.splunk.com/answers/183660/db-connect-why-datetime-field-in-mssql-is-imported.html#ans...

It's tailored for MS SQL Server but the idea of configuring the timestamp parsing format is the same for any DB.

Cheers

[musskopf]

Here another similar answer: http://answers.splunk.com/answers/143775/timestamp-recognition-with-dbconnect-app.html#answer-143903