Splunk DB Connect 2.0.6: Why am I getting "ERROR: The datatype of output timestamp column number [1] is invalid as [BIGINT]" trying to import an epoch timestamp?

[wdane]

Hi all,

I would like to import the create_date as timestamp from a database. When I select my epoch value in the Splunk DB Connect 2 app and run the import, nothing gets imported and I get a error. Am I doing something wrong? Hope someone can help 🙂

I am using version: 2.0.6 of Splunk DB Connect and version 6.3 of Splunk.

SQL output:
create_time id
1 1210326957 3

error log:

10/21/2015 16:13:25 [ERROR] [ws.py] [DBInput Service] ERROR: The datatype of output timestamp column number [1] is invalid as [BIGINT]..
10/21/2015 16:13:25 [ERROR] [websocket.py] ERROR: The datatype of output timestamp column number [1] is invalid as [BIGINT]..
10/21/2015 16:14:20 [ERROR] [ws.py] [DBInput Service] ERROR: The datatype of output timestamp column number [1] is invalid as [BIGINT]..
10/21/2015 16:14:20 [ERROR] [websocket.py] ERROR: The datatype of output timestamp column number [1] is invalid as [BIGINT]..
10/21/2015 16:15:21 [ERROR] [ws.py] [DBInput Service] ERROR: The datatype of output timestamp column number [1] is invalid as [BIGINT]..
10/21/2015 16:15:21 [ERROR] [websocket.py] ERROR: The datatype of output timestamp column number [1] is invalid as [BIGINT]..

config:

[mi_input://test]
connection = otrs_db
index = main
input_timestamp_column_name = create_time
input_timestamp_column_number = 1
interval = 60
max_rows = 100
mode = batch
output_timestamp_format = YYYY-MM-dd HH:mm:ss
query = select TIMESTAMP( ticket_history.create_time ) create_time,\
ticket_history.id as id\
from ticket_history;
source = test
sourcetype = test
ui_query_mode = advanced
ui_query_schema = NULL
ui_query_table = NULL

[jcoates_splunk]

because it's trying to interpret a BIGINT as a DATETIME. Try CASTing create_time as DATETIME in your SQL.

[jsilverbears]

I am getting the same activity through the interface itself.
In the Set Parameters section of input creation, you can specify a unix timestamp as your Timestamp. I would like that to be displayed as yyyy-MM-DD HH:mm:ss as I specified in Output Timestamp Format selection. I would think that it would take the unix timestamp column that I specified and convert that to the appropriate format.

Is this not the case and if not, why does it not give an error here?

[wdane]

I am getting the exactly the same error when i use datatime in my sql select.

config
[mi_input://otrs_ticket_history_test]
connection = otrs_db
index = otrs
interval = 60
max_rows = 4
mode = batch
output_timestamp_format = YYYY-MM-dd HH:mm:ss
query = select DATE_FORMAT(create_time, '%Y-%m-%d') as create_time, tn from ticket limit 10;
source = otrs_ticket_history
sourcetype = mysql
ui_query_mode = advanced
ui_query_schema = NULL
ui_query_table = NULL
disabled = 0
input_timestamp_column_name = create_time
input_timestamp_column_number = 1
input_timestamp_format = yyyy-MM-dd
ui_query_catalog = reporting

error log:

10/26/2015 05:32:07 [ERROR] [ws.py] [DBInput Service] ERROR: The datatype of output timestamp column number [1] is invalid as [BIGINT]..
10/26/2015 05:32:07 [ERROR] [websocket.py] ERROR: The datatype of output timestamp column number [1] is invalid as [BIGINT]..
10/26/2015 05:33:07 [ERROR] [ws.py] [DBInput Service] ERROR: The datatype of output timestamp column number [1] is invalid as [BIGINT]..
10/26/2015 05:33:07 [ERROR] [websocket.py] ERROR: The datatype of output timestamp column number [1] is invalid as [BIGINT]..