topic Re: DB Connect tail question in Splunk Search

DB Connect tail question

theouhuios — Mon, 10 Feb 2014 16:57:50 GMT

Hello

This is my DB tail config which I am trying to get the data from. But I get few errors in the dbx log. I guess I am making a mistake in placing the rising_column value. Any help would be great.

[dbmon-tail://test/test1]
output.format = kv
output.timestamp = 1
output.timestamp.column = change_time
output.timestamp.format = s ***(The time is epcch time . Eg:1391446921.000)***
query = SELECT t.change_time ,t.create_time , t.tn , t.title , q.name , u.login , ts.name FROM ticket t, queue q, users u, ticket_state ts where t.queue_id = q.id and t.user_id = u.id and t.ticket_state_id = ts.id {{AND $rising_column$ > ?}} order by tn
sourcetype = test_new
tail.rising.column = change_time
interval = auto
table = xxxx
disabled = 0

The data looks like this

    change_time     create_time     tn  title   name    login
1   1391446921.000  1386634163.000  ABC AAA ccc 12345
2   1391446921.000  1386634165.000  ABC AAA ccc 12345
3   1391446921.000  1386634167.000  ABC AAA ccc 12345

Re: DB Connect tail question

araitz — Mon, 10 Feb 2014 17:25:15 GMT

It would be easier for us to assist you if you open a support case. Without seeing your data, it will be difficult. If you want help here, can you share what some rows in your table look like?

Re: DB Connect tail question

richgalloway — Mon, 10 Feb 2014 17:27:17 GMT

What errors are you getting in the log?

Re: DB Connect tail question

theouhuios — Mon, 28 Sep 2020 15:50:53 GMT

The error which I get is

2014-02-10 13:34:22.116 dbx481:ERROR:TailDatabaseMonitor - Error while executing database monitor: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'change_time' in where clause is ambiguous
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'change_time' in where clause is ambiguous

Re: DB Connect tail question

theouhuios — Mon, 10 Feb 2014 18:43:50 GMT

@araitz - I was planning to do that after trying to see if I did any mistake on my end. I have updated the question with an example of how the data looks like.

Re: DB Connect tail question

btsay_splunk — Mon, 28 Sep 2020 15:51:18 GMT

from what the error I saw "tail.rising.column = change_time", Column 'change_time' is ambiguous, you should use qualified column name such as ticket.change_time instead.

Re: DB Connect tail question

theouhuios — Mon, 28 Sep 2020 15:51:52 GMT

It works now. Was giving error even with ticket.change_time. Somehow splunk isn't handling mysql DB Connections as good as what it does with oracle. Anyways, I changed the $rising_column$ value to ticket.$rising_column$ with rising_column as change_time.

I had to do this because splunk wasn't listing the column name as ticket/t.change_time. Is was listing it only as change_time ( Never saw this issue when trying to connect to oracle Db).

Re: DB Connect tail question

ziegfried — Tue, 11 Feb 2014 16:34:21 GMT

Also note that you don't need to use the $rising_column$ placeholder necessarily. You can also use specify the plain/qualified column name in the query.

SELECT t.change_time ,t.create_time , t.tn , t.title , q.name , u.login , ts.name FROM ticket t, queue q, users u, ticket_state ts where t.queue_id = q.id and t.user_id = u.id and t.ticket_state_id = ts.id {{AND ticket.change_time > ?}} order by tn

The only thing that is important is that the name of rising_column matches what the database returns in the query results.

Re: DB Connect tail question

theouhuios — Tue, 11 Feb 2014 16:39:13 GMT

Thanks @ziegfried. Good to know about that.

Re: DB Connect tail question

johann_brajer — Tue, 23 Dec 2014 09:02:12 GMT

@Ziegfried: but is it possible to use {{AND ticket.change_time > ?}} also from the DBX GUI in SQL Query window? or does this not work?

i always get an Error when trying to save 😞

Do I have to edit the DBTail Config instead??

get the error

"12-23-2014 10:03:22.622 +0100 ERROR AdminManagerExternal - Unable to xml-parse the following data: Failed to validate: com.splunk.config.SplunkConfigurationException: Error validating dbmonTail for monitor=dbmon-tail://xxxxxx...  See splunkd.log for full data.
tail: „ERROR:“ kann nicht zum Lesen geöffnet werden: Datei oder Verzeichnis nicht gefunden
tail: „column“ kann nicht zum Lesen geöffnet werden: Datei oder Verzeichnis nicht gefunden
tail: „reference“ kann nicht zum Lesen geöffnet werden: Datei oder Verzeichnis nicht gefunden
tail: „oid“ kann nicht zum Lesen geöffnet werden: Datei oder Verzeichnis nicht gefunden
tail: „is“ kann nicht zum Lesen geöffnet werden: Datei oder Verzeichnis nicht gefunden
tail: „ambiguous“ kann nicht zum Lesen geöffnet werden: Datei oder Verzeichnis nicht gefunden

and

12-23-2014 10:24:55.720 +0100 ERROR AdminManagerExternal - Received malformed XML from external handler:\nFailed to validate: com.splunk.config.SplunkConfigurationException: Error validating dbmonTail for monitor=dbmon-tail://xxxx/test__sqlQuery: [xxx__sqlQuery] Invalid query "select \r\n      scheduled_job_run.started_at,\r\n    job_run_stat.oid,\r\n    ......
.......
from 
        job_run_stat,
        scheduled_job_run
where 
...
{{AND job_run_stat.oid > ?}}\r\n" 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 "/opt/splunk/lib/python2.7/site-packages/splunk/admin.py", line 70, in init\n    hand.execute(info)\n  File "/opt/splunk/lib/python2.7/site-packages/splunk/admin.py", line 528, in execute\n    if self.requestedAction == ACTION_EDIT:     self.handleEdit(confInfo)\n  File "/opt/splunk/etc/apps/dbx/bin/spp/config.py", line 238, in handleEdit\n    self.handleModification("edit", output)\n  File "/opt/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 "/opt/splunk/etc/apps/dbx/bin/rest_handler_dbmon.py", line 95, in process_modification\n    self.validateConfig(stanza, props)\n  File "/opt/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 "/opt/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

THX