Splunk Search

How do you extract timestamp in Mysql database column?

dfigurello
Communicator

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
Builder

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.

0 Karma

dfigurello
Communicator

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?

0 Karma

musskopf
Builder

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

0 Karma

dfigurello
Communicator

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?

0 Karma

chanfoli
Builder

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

0 Karma

dfigurello
Communicator

Hi chanfoli,

Yes the data type is DATETIME.

Tks!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...