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!
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.
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?
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
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?
Did you try with the default (leaving the format field blank)? I am pretty sure the default recognizes the standard timestamp format.
Hi chanfoli,
Yes the data type is DATETIME.
Tks!