Splunk DB Connect: Why am I not getting the correct _time in Splunk from a database timestamp column?

[omerr]

Hi,

I am working with the Splunk DB Connect app and always have the same problem.

When I run the query in the preview page, I get the timestamp column in epoch time (I don't know why) with 13 digits.

When you want to create new DB input, you need to fill some parameters and one of them is rising column (if its tail query).

I chose this column for rising column and also for the timestamp column. I need to choose between Epoch Time to Java Date.

Since the data viewed like epoch time, I choose epoch time and then in the last parameter choose Output Timestamp format as YYYY-MM-dd HH:mm:ss.

Example:
My OracleDB with column called: Creation_Date

Rising Column: Creation_Date
Timestamp Column: Creation_Date ; Epoch TIme
Output Timestamp Format: YYYY-MM-dd HH:mm:ss

My Oracle DB: Splunk:
13-May-2015 1431496124000
12-May-2015 1431423563000

The Problems:
1. Splunk doesn't recognize this time as _time
2. If I tried to manually force _time to get this time with |eval Creation_Date=strfime(Creation_Date,"%F %T") | _time=strptime(Creation_Date,"%F %T"), this is doesn't work and the _time is not correct.
3. Splunk 13 digits number is not exactly Epoch time (Epoch is 10 digits: the number of seconds from 1.1.1970) - it gives the time in milliseconds. Maybe because of this, Splunk cannot convert it back to _time.

I will be happy to hear how to handle with those cases.

Omer Rudik.

[jkat54]

1. Splunk don't recognize this time as _time

That's because you're doing it wrong.

2. If I tryied to manually force _time to get this time with |eval Creation_Date=strfime(Creation_Date,"%F %T") | _time=strptime(Creation_Date,"%F %T") - This is doesn't work, the _time is not correct.

Again, this is wrong. I need an example of the raw data to help you fix this. %F = YYYY-MM-DD and it appears you have YYYY-MMM-DD where MMM is three letter abbreviation for month. So it should be something like "%Y-%b-%d" for the date, an the epoch time is another story...

3. Splunk 13 digits number is not exactly Epoch time (Epoch is 10 digits: the number of seconds from 1.1.1970) - it gives the time in milli-seconds - maybe because of this Splunk cannot convert it back to _time.

OK, 13 digit epoch has 3 digits for miliseconds. That's expected, however I see you dont have a decimal point to notate where the miliseconds begin. So you should extract this epoch field and divide by 1000 to get seconds without miliseconds.

I can help you with all of the above, but I need an exact copy of 1 event from your index. Please provide and I will assist further.

http://docs.splunk.com/Documentation/Splunk/6.2.0/SearchReference/Commontimeformatvariables