How do you extract timestamp in Mysql database column?

[dfigurello]

Hi splunkers,

I am collecting data using dbconnect and I am studying about this application, then I installed mysql server 5.6 and installed a sample database called sakali.
There is a table called rental with sample values to study.

The structure is:
rental_id | rental_date | inventory_id | customer_id | return_date | staff_id | last_update

I am trying to index data, but I've had problem with splunk timestamp.

Follows above the data input configuration:

input type: Tail
database: sakali
x Specify SQL Query: select * from rental {{WHERE $rising_column$ > ?}}
rissing_collumn: rental_id
sourcetype: database-sakali
index: main
host: serverbd

Output Format : key-value format
Timestamp column: rental_date
Timestamp format: yyyy-MM-dd HH:mm:ss
interval: auto

When I runs the search, the timestamp just time is correct and the date is wrong.

For example:

Timestamp Splunk: events
7/8/14 11:59:57.000 PM || 2005-06-20 23:59:57 rental_id=3194 inventory_id=1835

7/8/14 this date is wrong.

Could you explain me about that and How to control the configuration ?

Cheers!

[chanfoli]

Hello, is the column rental_date a DATETIME data type? If not perhaps you could add a CAST to your SELECT. The documentation seems to indicate that DBX likes timestamp columns to be of this type.

[dfigurello]

Hey musskopf,

I did, but not works.
First I edited the ...dbx\local\inputs.conf

[dbmon-tail://sakila/teste-splunk]
host = testesplunk
index = main
output.format = kv
output.timestamp = 1
output.timestamp.column = rental_date
output.timestamp.parse.format = yyyy-MM-dd'T'HH:mm:ss.SSS
query = select * from rental {{WHERE $rising_column$ > ?}}
sourcetype = testesplunk
tail.rising.column = rental_id

Then I restarted the splunkd service.

Timestamp splunk || events
7/9/14 11:59:57.000 PM 2005-06-20T23:59:57.000 rental_id=31(..)

The correct date is 2005-06-20.

any idea?

[musskopf]

You could edit you dbx/local/inputs.conf and add the option:

output.timestamp.parse.format, something like:

output.timestamp.parse.format = yyyy-MM-dd'T'HH:mm:ss.SSS
Format reference: http://docs.oracle.com/javase/6/docs/api/java/text/SimpleDateFormat.html

[dfigurello]

Yes, I did.

input type: Tail
database: sakali
x Specify SQL Query: select * from rental {{WHERE $rising_column$ > ?}}
rissing_collumn: rental_id
sourcetype: database-sakali
index: main
host: serverbd

Output Format : key-value format
Timestamp column: rental_date
Timestamp format:
interval: auto

There is a result:

Splunk Timestamp event
7/9/14 11:59:57.000 PM || 2005-06-20T23:59:57.000 rental_id=3194 inventory_id=1835 customer_id=432 (..)

any idea?

[chanfoli]

Did you try with the default (leaving the format field blank)? I am pretty sure the default recognizes the standard timestamp format.

[dfigurello]

Hi chanfoli,

Yes the data type is DATETIME.

Tks!