topic Re: db connect datetime fields - not timestamp in Splunk Search

db connect datetime fields - not timestamp

jamesmonico — Mon, 29 Jul 2013 11:26:21 GMT

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

Re: db connect datetime fields - not timestamp

lukejadamec — Mon, 29 Jul 2013 14:45:12 GMT

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

Re: db connect datetime fields - not timestamp

pmdba — Wed, 31 Jul 2013 16:40:17 GMT

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.