All Apps and Add-ons

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

carlalldis
Explorer

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

0 Karma

jcoates_splunk
Splunk Employee
Splunk Employee

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

0 Karma

carlalldis
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...