Indexing Epoch times in DB Connect

[justinfranks]

I have a MySQL DB with event data stored that I would like to index. I have no problems indexing the data but nothing I do will make Splunk recognise the date field.

The date is stored in an INT field as Epoch Time. e.g. "1397249090" or "1397433109". Note: It does not store it with the millisecond suffix. Just a straight 10-digit INT.

I have tried to use MYSQL to convert it but nothing seems to work;

• FROM_UNIXTIME(date) just adds '.000' to the end of the data. e.g. "1397249090.000" or "1397433109.000" and is still not recognised by Splunk when indexed.
• cast(datefield as date) OR cast(datefield as datetime) OR cast(datefield as timestamp) all return nothing from a DB Query.
• I would try a MYSQL convert but I am not sure on the syntax to get it work.

I think the issue is that it is an INT and not stored as a DATE. However, given this lack of success I have tried to setup a props.conf input.

My props.conf stanza is:

[xcart_shop]
TIME_FORMAT = %s

However, when I tried my DB Connect tail, I get this from the log file:

2014-04-15 08:34:00.399 dbx2849:WARN:ResultSetOutputPayload - Unrecognized timestamp format: '1397249090'
2014-04-15 08:34:00.399 dbx2849:WARN:ResultSetOutputPayload - Unrecognized timestamp format: '1397249090'
2014-04-15 08:34:00.399 dbx2849:WARN:ResultSetOutputPayload - Unrecognized timestamp format: '1397249090'
2014-04-15 08:34:00.399 dbx2849:WARN:ResultSetOutputPayload - Unrecognized timestamp format: '1397266447'
2014-04-15 08:34:00.399 dbx2849:WARN:ResultSetOutputPayload - Unrecognized timestamp format: '1397281370'
2014-04-15 08:34:00.399 dbx2849:WARN:ResultSetOutputPayload - Unrecognized timestamp format: '1397301307'
2014-04-15 08:34:00.399 dbx2849:WARN:ResultSetOutputPayload - Unrecognized timestamp format: '1397340247'
2014-04-15 08:34:00.399 dbx2849:WARN:ResultSetOutputPayload - Unrecognized timestamp format: '1397380188'
2014-04-15 08:34:00.399 dbx2849:WARN:ResultSetOutputPayload - Unrecognized timestamp format: '1397380299'
2014-04-15 08:34:00.399 dbx2849:WARN:ResultSetOutputPayload - Unrecognized timestamp format: '1397433109'
2014-04-15 08:34:00.399 dbx2849:WARN:ResultSetOutputPayload - Unrecognized timestamp format: '1397435663'
2014-04-15 08:34:00.399 dbx2849:WARN:ResultSetOutputPayload - Unrecognized timestamp format: '1397438935'

Anyone have any ideas or point me in the right direction?

[justinfranks · Accepted solution]

Ok, I was able to resolve this one myself.... eventually, by using:

http://docs.splunk.com/Documentation/DBX/1.1.3/DeployDBX/Configuredatabasemonitoring

and

http://docs.splunk.com/Documentation/DBX/1.1.3/DeployDBX/Troubleshoot#If_your_timestamp_is_not_of_ty...

Additionally, by following MaverickT's response from http://answers.splunk.com/answers/98601/db-connect-timestamp-issues

Here is my resolved setup:

props.conf ($SPLUNK_HOME$/etc/apps/dbx/local/props.conf😞

[xcart_shop]
TIME_FORMAT=%Y-%m-%d %H:%M:%S
MAX_DAYS_AGO = 3650

inputs.conf ($SPLUNK_HOME$/etc/apps/dbx/local/inputs.conf😞

[dbmon-tail://Xcart_Shop_DB/etv_xcart_shop_orders]
host = webmysql.screenrights.org
interval = 15m
output.format = kv
output.timestamp = 1
output.timestamp.column = the_date
output.timestamp.format = yyyy-MM-dd HH:mm:ss
output.timestamp.parse.format = yyyy-MM-dd HH:mm:ss
query = select FROM_UNIXTIME(o.`date`) as the_date, o.orderid, d.productid, d.productcode, d.amount\r\nfrom xcart_orders o, xcart_order_details d  \r\nwhere o.orderid = d.orderid {{and o.$rising_column$ > ?}}
sourcetype = xcart_shop
tail.rising.column = orderid
disabled = 0
table = etv_xcart_shop_orders

[justinfranks]

UPDATE:

I was able to change the MYSQL command to change the format of the date.

select FROM_UNIXTIME(o.`date`, '%Y-%m-%d %H:%i:%s') the_date, o.orderid, d.productid, d.amount
from xcart_orders o, xcart_order_details d  
where o.orderid = d.orderid {{and o.$rising_column$ > ?}}

My rising column is: orderid

My timestamp column is: the_date

My timestamp format is: "yyyy-MM-dd HH:mm:ss"

However, when I try to index I get a bunch of errors the same.

2014-04-15 14:30:12.096 dbx1309:WARN:ResultSetOutputPayload - Unrecognized timestamp format: '2008-10-07 11:26:07'

[justinfranks]

No. For the rising column I am using a orderid field.

[araitz]

Can you clarify the use case a bit? Are you using the timestamp values as a rising column for your input? If so, double-check the values in inputs.conf:

http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/inputsspec

http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Configuredatabasemonitoring#About_timestam...

This might or might not help:

http://answers.splunk.com/answers/98601/db-connect-timestamp-issues