Monitoring Splunk

DB Connect: Why am I getting "Error connecting to /servicesNS/admin/dbx/dbx/dbmon: The read operation timed out" setting up Oracle DB input?

sim_tcr
Communicator

Hello,

I successfully setup the oracle db connection. When trying to setup the database input, i get error "Encountered the following error while trying to save: Splunkd daemon is not responding: ('Error connecting to /servicesNS/admin/dbx/dbx/dbmon: The read operation timed out',)"

below is splunkd.log

09-09-2014 03:48:00.001 -0400 WARN  DateParserVerbose - Failed to parse timestamp. Defaulting to timestamp of previous event (Tue Sep  9 03:47:59 2014). Context: source::/apps/splunk/var/log/splunk/jbridge.log|host::vc2crtpb028646n.fmr.com|jbridge|801

09-09-2014 03:49:40.707 -0400 ERROR AdminManagerExternal - Received malformed XML from external handler:\nFailed to validate: com.splunk.config.SplunkConfigurationException: Error validating dbmonTail for monitor=dbmon-tail://b2b/b2b: [b2b] Invalid query "SELECT count(*) from B2B.B2B_PDS where (CREATED_DT > '08-SEP-14' AND CREATED_DT < '09-SEP-14')" without proper {{ ... $rising_column$ > ?}} pattern! with query = \n<eai_error><recognized>false</recognized><type>&lt;class 'spp.java.bridge.JavaBridgeError'&gt;</type><message>Command com.splunk.dbx.monitor.DatabaseMonitorValidator returned status code 17</message><stacktrace>Traceback (most recent call last):\n  File "/apps/splunk/lib/python2.7/site-packages/splunk/admin.py", line 70, in init\n    hand.execute(info)\n  File "/apps/splunk/lib/python2.7/site-packages/splunk/admin.py", line 526, in execute\n    if self.requestedAction == ACTION_CREATE:   self.handleCreate(confInfo)\n  File "/apps/splunk/etc/apps/dbx/bin/spp/config.py", line 230, in handleCreate\n    self.handleModification("create", output)\n  File "/apps/splunk/etc/apps/dbx/bin/spp/config.py", line 218, in handleModification\n    id, props = self.process_modification(id, props, type=type, output=output)\n  File "/apps/splunk/etc/apps/dbx/bin/rest_handler_dbmon.py", line 95, in process_modification\n    self.validateConfig(stanza, props)\n  File "/apps/splunk/etc/apps/dbx/bin/rest_handler_dbmon.py", line 126, in validateConfig\n    executeBridgeCommand("com.splunk.dbx.monitor.DatabaseMonitorValidator", args, checkStatus=True)\n  File "/apps/splunk/etc/apps/dbx/bin/spp/java/bridge.py", line 182, in executeBridgeCommand\n    raise JavaBridgeError("Command %s returned status code %s" % (cmd, ret))\nJavaBridgeError: Command com.splunk.dbx.monitor.DatabaseMonitorValidator returned status code 17\n</stacktrace></eai_error>\n

09-09-2014 03:49:40.707 -0400 ERROR AdminManagerExternal - Unable to xml-parse the following data: Failed to validate: com.splunk.config.SplunkConfigurationException: Error validating dbmonTail for monitor=dbmon-tail://b2b/b2b: ...  See splunkd.log for full data.

09-09-2014 03:58:27.174 -0400 ERROR AdminManagerExternal - Received malformed XML from external handler:\nFailed to validate: com.splunk.config.SplunkConfigurationException: Error validating dbmonTail for monitor=dbmon-tail://b2b/b2b: ORA-01652: unable to extend temp segment by 64 in tablespace ESTT01\n with query = SELECT * FROM B2B.B2B_PDS ORDER BY PDS_ID\n<eai_error><recognized>false</recognized><type>&lt;class 'spp.java.bridge.JavaBridgeError'&gt;</type><message>Command com.splunk.dbx.monitor.DatabaseMonitorValidator returned status code 17</message><stacktrace>Traceback (most recent call last):\n  File "/apps/splunk/lib/python2.7/site-packages/splunk/admin.py", line 70, in init\n    hand.execute(info)\n  File "/apps/splunk/lib/python2.7/site-packages/splunk/admin.py", line 526, in execute\n    if self.requestedAction == ACTION_CREATE:   self.handleCreate(confInfo)\n  File "/apps/splunk/etc/apps/dbx/bin/spp/config.py", line 230, in handleCreate\n    self.handleModification("create", output)\n  File "/apps/splunk/etc/apps/dbx/bin/spp/config.py", line 218, in handleModification\n    id, props = self.process_modification(id, props, type=type, output=output)\n  File "/apps/splunk/etc/apps/dbx/bin/rest_handler_dbmon.py", line 95, in process_modification\n    self.validateConfig(stanza, props)\n  File "/apps/splunk/etc/apps/dbx/bin/rest_handler_dbmon.py", line 126, in validateConfig\n    executeBridgeCommand("com.splunk.dbx.monitor.DatabaseMonitorValidator", args, checkStatus=True)\n  File "/apps/splunk/etc/apps/dbx/bin/spp/java/bridge.py", line 182, in executeBridgeCommand\n    raise JavaBridgeError("Command %s returned status code %s" % (cmd, ret))\nJavaBridgeError: Command com.splunk.dbx.monitor.DatabaseMonitorValidator returned status code 17\n</stacktrace></eai_error>\n

09-09-2014 03:58:27.234 -0400 ERROR AdminManagerExternal - Unable to xml-parse the following data: Failed to validate: com.splunk.config.SplunkConfigurationException: Error validating dbmonTail for monitor=dbmon-tail://b2b/b2b: ...  See splunkd.log for full data.

09-09-2014 03:58:27.235 -0400 WARN  HttpListener - Socket error from 127.0.0.1 while accessing /servicesNS/admin/dbx/dbx/dbmon: Broken pipe

09-09-2014 04:24:10.498 -0400 WARN  DateParserVerbose - Failed to parse timestamp. Defaulting to timestamp of previous event (Tue Sep  9 04:24:09 2014). Context: source::/apps/splunk/var/log/splunk/jbridge.log|host::vc2crtpb028646n.fmr.com|jbridge|803

09-09-2014 04:24:10.498 -0400 WARN  DateParserVerbose - Failed to parse timestamp. Defaulting to timestamp of previous event (Tue Sep  9 04:24:09 2014). Context: source::/apps/splunk/var/log/splunk/jbridge.log|host::vc2crtpb028646n.fmr.com|jbridge|803
0 Karma

pmdba
Builder

I see two problems here:

Error validating dbmonTail for monitor=dbmon-tail://b2b/b2b: [b2b] Invalid query "SELECT count(*) from B2B.B2B_PDS where (CREATED_DT > '08-SEP-14' AND CREATED_DT  ?}} pattern!

Your input is apparently designated as a "tail" on the table, but you didn't specify a rising column that Splunk can use to differentiate old values from new ones - you are returning a row count without a timestamp, which Splunk won't be able to index accurately. Besides the DBX documentation, try the Log File Analysis for Oracle 11g paper for a primer on getting data from Oracle into Splunk.

The second problem is a little further down:

Error validating dbmonTail for monitor=dbmon-tail://b2b/b2b: ORA-01652: unable to extend temp segment by 64 in tablespace ESTT01\n with query = SELECT * FROM B2B.B2B_PDS ORDER BY PDS_ID\n

Oracle is using the ESTT01 tablespace to sort the output of your query on disk (apparently there is too much to do it in memory), and there isn't enough room to grow the file to hold it all. If your data has a timestamp (read the paper for how to handle that so Splunk can correctly interpret it), the you don't need to have an "order by" clause in your data at all - any other fields will be meaningless to the Splunk for indexing purposes.

If you are executing your query directly from the Search interface with "| dbquery" and not indexing the data, and sort order is important, then you (or your DBA if that's not you) either need to:

  1. allocate more memory to the Oracle System Global Area (SGA) so that the sort can take place there, or
  2. allocate more space or more room to grow to the ESTT01 tablespace, or
  3. use the Splunk "sort" command to sort it after it gets to Splunk, or
  4. select a lot less data at once

That said, when loading SQL data into Splunk you should always specify your columns explicitly by name, not implicitly (i.e. don't use "select "). The first column returned should be a timestamp, either based on sysdate or on a date field from the database table, so that Splunk can accurately index your data (*everything Splunk does is based on timestamp!).

pmdba
Builder

The following is an example of a "tail" monitor from the inputs.conf file. Note the timestamp configuration and the explicit column definitions.

[dbmon-tail://orcl/scheduler_job_run_details]
host = localhost
index = oracle_dbx
output.format = kv
output.timestamp = 0
output.timestamp.format = yyyy-MM-dd HH:mm:ss
output.timestamp.parse.format = yyyy-MM-dd HH:mm:ss
query = select to_char(log_date,'YYYY-MM-DD HH24:MI:SS') log_date, log_id, owner, 
job_name, status, error# return_code, to_char(req_start_date,'YYYY-MM-DD 
HH24:MI:SS') req_start_date, to_char(actual_start_date,'YYYY-MM-DD HH24:MI:SS') 
actual_start_date, to_char(run_duration) run_duration, instance_id, session_id, 
to_char(cpu_used) cpu_used, additional_info from dba_scheduler_job_run_details 
{{WHERE $rising_column$ > ?}}
sourcetype = job_run_details
tail.rising.column = LOG_ID
interval = auto
table = scheduler_job_run_details
0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...