db connect datetime fields - not timestamp

[jamesmonico]

Hello experts,

I am using DB Connect to pull in data from a MySQL database table. The tail works and the field i set to be the timestamp works as expected.

the issue comes from other fields that are set to MySQL DATETIME field type. When these are imported Splunk they are turning up in this format "updated_at=1375083603.000" apposed to "2013-03-07 00:06:00" in the database (DATETIME) field type.

I am at a loss of what is going on here, I added in the datetime formatting for the output section of the DB connect but think that only works for the timestamp which works ok.

below is a copy on an event with private data removed, as you can see the scheduled and updated_at fields are DATETIME within MySQL...

2013-07-29T08:35:02.000
id=5260
item=xxxxxxx
status=finished
metadata=xxxxxxxxx
schedule_type=scheduled
scheduled=1375086900.000
no_reboot=true
deleted_on=
deleted_status=
updated_at=1375083603.000
number=xxxx
name=xxxxxx
label=xxxxxx

Where would I be going wrong, any tips or guidance would be muchly received.

Cheers

[pmdba]

Another possibility is to use a database function during your original query to convert the DATETIME data type into a character string format that Splunk will recognize as a time. In Oracle I use the "to_char" function something like this:

select to_char(scheduled,'YYYY-MM-DD HH24:MI:SS') scheduledtime from table;

I'm assuming that MySQL would have something similar.

[lukejadamec]

The reason is because the ctime extraction is being used at index time only for the event time.

You need to configure Splunk to recognize other time fields as time, because otherwise Spunk just assumes you want the number.

An example of a search time field extraction for epoch time is:
search | convert timeformat="%H:%M:%S" ctime(scheduled) AS ScheduledTime |

I've never actually tried this, but it should work according to the documentation.
Documentation/Splunk/5.0.3/SearchReference/Convert