Timestamp recognition with DBCONNECT app.

[jlhamlet]

Hi,

I am using DB CONNECT app to collect data coming from a MySQL database.

The table I am querying has 4 fields. Two of these fields are dates (in different formats). After being indexed in Splunk, the events look as follows:

**datetime1=1404374979 field2=value **datetime2="2014/07/04 - 23:45:00" field4=value

I wish to force Splunk to use datetime1 (Epoch format) as Timestamp.

What options do i have to apply to my props.conf to do so ?

I am thinking on TIME_FORMAT and TIME_PREFIX, but not sure of what option to use.

Also, do i have to clean my index so that changes can apply ?

Thank you for your help.

Regards

[musskopf]

I don't think you need to edit your props.conf. Have a look in your dbx app Input config (splunk/etc/apps/dbx/local/inputs.conf). Most of the configuration can be made via the GUI as well. You should have something like that:

[dbmon-tail://MyDB/dbTail01]
index = my_dbTail01<br> interval = 15 * * * *
output.format = kv
output.timestamp = 1
output.timestamp.column = datetime1
query = SELECT datetime1, field2, datetime2, field4 from TABLE1 {{WHERE $rising_column$ > ?}}
sourcetype = something
table = TABLE1
tail.rising.column = datetime1
disabled = 0

Also there is an additional parameter you might want use if DB Connect doesn't understand the "datetime1", that is:

output.timestamp.format = yyyy-MM-dd' 'HH:mm:ss.SX

where you specify the date format you DB will spit out. You might need to use it together with your Query. Look for the function "FROM_UNIXTIME" from MySQL doc.

Also DBX output.timestamp.format uses the Java SimpleDateFormat (sorry I the system didn't allow me to post the link but just google it if you need).

[sroback_splunk]

Hi. You might try to have your SQL query do the conversion using CAST or CONVERT to TIMESTAMP.

For more info on working with timestamps in DB Connect, see:
http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Configuredatabasemonitoring#About_timestam...

For general info on configuring Splunk timestamp recognition, see: http://docs.splunk.com/Documentation/Splunk/latest/Data/Configuretimestamprecognition