Splunk DB Connect 2: Why is Splunk not indexing SQL data that has epoch timestamps?

[carlalldis]

Hi,

I am having an issue with Splunk not indexing SQL data (from Sybase SQL Anywhere 11) that has epoch timestamps. Here is an example of the results returned by the SQL query:

STATUS_AVG   STATUS_LOW   TOTIME       MOINSTID   FROMTIME     STATUS_HIGH
0           0           1425240000   12669    1425236400   0
0           0           1425240000   12666    1425236400   0
0           0           1425243600   12673    1425240000   0
0           0           1425243600   12672    1425240000   0
0           0           1425243600   12671    1425240000   0
0           0           1425243600   12670    1425240000   0
0           0           1425243600   12669    1425240000   0
0           0           1425243600   12668    1425240000   0
0           0           1425243600   12667    1425240000   0
0           0           1425243600   12666    1425240000   0

In the gui, I configured the input as follows (also, the GUI validates the connection as ok, and the query runs successfully):

Name: PNET07_WIN_CPU
App: Splunk DB Connect v2
Connection: PNET07
Advanced Query: SELECT * FROM "storm_CCA-SCI-PNET07"."dba"."_PATROL__MS_HW_MAIN_MS_HW_CPUCORE_CONT_MS_HW_CPUCORE_RT_VIEW"
Type: Batch Input
Max Rows: 100
Timestamp: Choose Column
Specify Timestamp Column: TOTIME
    Timestamp Format: Epoch time
Output Timestamp Format: Epoch time
Execution Frequency: 300
Source: PNET07_WIN_CPU
Sourcetype: WIN_CPU
Index: pnet07

If I look at the stanza generated in inputs.conf, it is as follows:

[mi_input://PNET07_WIN_CPU]
connection = PNET07
index = pnet07
input_timestamp_column_name = TOTIME
input_timestamp_column_number = 3
interval = 300
max_rows = 100
mode = batch
output_timestamp_format = epoch
query = SELECT * FROM "storm_CCA-SCI-PNET07"."dba"."_PATROL__MS_HW_MAIN_MS_HW_CPUCORE_CONT_MS_HW_CPUCORE_RT_VIEW"
source = PNET07_WIN_CPU
sourcetype = WIN_CPU
ui_query_catalog = storm_CCA-SCI-PNET07
ui_query_mode = advanced
ui_query_schema = dba
ui_query_table = _PATROL__MS_HW_MAIN_MS_HW_ENCLOSURE_MS_HW_LOGICALDISK_CONT_MS_HW_LOGICALDISK_STATS_19

This all seems to be ok, however, when the input runs on the schedule specified, the following is logged in dbx2.log:

09/17/2015 10:26:14 [ERROR] [ws.py] [DBInput Service] ERROR: Output timestamp format is invalid as [Illegal pattern character 'e']..
09/17/2015 10:26:14 [ERROR] [websocket.py] ERROR: Output timestamp format is invalid as [Illegal pattern character 'e']..

I have discovered that this seems to be related to the inputs.conf line output_timestamp_format = epoch, as if I change the value from epoch to test in the conf, restart Splunk, the error in dbx2.log is then:

09/17/2015 10:26:14 [ERROR] [ws.py] [DBInput Service] ERROR: Output timestamp format is invalid as [Illegal pattern character 't']..
09/17/2015 10:26:14 [ERROR] [websocket.py] ERROR: Output timestamp format is invalid as [Illegal pattern character 't']..

Interestingly, this issue does not affect my other inputs; these are not attempting to extract timestamp information from the rows, but just use the index time, however, have the exact same config for output_timestamp_format. Example stanzas:

[mi_input://PNET07_VIEWS]
connection = PNET07_DB
index = pnet07
interval = 86400
max_rows = 1000000
mode = batch
output_timestamp_format = epoch
query = SELECT * FROM "storm_CCA-SCI-PNET07"."sys"."SYSVIEWS"
source = sysviews
sourcetype = views
ui_query_catalog = storm_CCA-SCI-PNET07
ui_query_mode = simple
ui_query_schema = sys
ui_query_table = SYSVIEWS

[mi_input://PNET07_TABLES]
connection = PNET07_DB
index = pnet07
interval = 86400
max_rows = 1000000
mode = batch
output_timestamp_format = epoch
query = select table_name, count from systable where primary_root<>0 and creator=1 order by 1
source = systable
sourcetype = tables
ui_query_catalog = storm_CCA-SCI-PNET07
ui_query_mode = advanced
ui_query_schema = sys
ui_query_table = SYSVIEWS

Has anyone come across this bizarre issue before?

Thank you,

Carl

[jcoates_splunk]

hm... only thing that springs to mind is that the input time format isn't set. Try forcing that? http://docs.splunk.com/Documentation/DBX/2.0.4/DeployDBX/inputsspec

[carlalldis]

Thanks for the replace. I tried forcing this in the conf (input_timestamp_format = epoch) however then I just get the error:

09/21/2015 10:26:18 [ERROR] [ws.py] [DBInput Service] ERROR: Input timestamp format is invalid as [Illegal pattern character 'e']..
09/21/2015 10:26:18 [ERROR] [websocket.py] ERROR: Input timestamp format is invalid as [Illegal pattern character 'e']..

From the spec you posted, it looks like the _timestam_format fields only accept a java datetime, of which 'epoch' is not. I assume that to specify epoch you have to omit the field from the conf altogether. I adjusted the conf accordingly, removing both fields:

[mi_input://PNET07_WIN_CPU]
connection = PNET07
index = pnet07
input_timestamp_column_name = TOTIME
input_timestamp_column_number = 3
interval = 300
max_rows = 100
mode = batch
query = SELECT * FROM "storm_CCA-SCI-PNET07"."dba"."PATROLMS_HW_MAIN_MS_HW_CPUCORE_CONT_MS_HW_CPUCORE_RT_VIEW"
source = PNET07_WIN_CPU
sourcetype = WIN_CPU
ui_query_catalog = storm_CCA-SCI-PNET07
ui_query_mode = advanced
ui_query_schema = dba
ui_query_table = _PATROL_MS_HW_MAIN_MS_HW_ENCLOSURE_MS_HW_LOGICALDISK_CONT_MS_HW_LOGICALDISK_STATS_19

however now I get:

09/21/2015 10:33:37 [ERROR] [ws.py] [DBInput Service] ERROR: The datatype of output timestamp column number [3] is invalid as [int]..
09/21/2015 10:33:37 [ERROR] [websocket.py] ERROR: The datatype of output timestamp column number [3] is invalid as [int]..

so I then removed the input_timestamp_column_number fields (although the above specifies output_timestamp_column_number, which is actually commented out in the python code from what I can see?) however I still get the same issue:

09/21/2015 10:50:38 [ERROR] [ws.py] [DBInput Service] ERROR: The datatype of output timestamp column number [3] is invalid as [int]..
09/21/2015 10:50:38 [ERROR] [websocket.py] ERROR: The datatype of output timestamp column number [3] is invalid as [int]..

Thus I still have no idea why this is failing. As I say, I looked through some of the code and can find no active references for 'output_timestamp' or 'output timestamp'

Thanks