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
... View more