Splunk Search

DB Connect tail question

theouhuios
Motivator

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
1 Solution

btsay_splunk
Splunk Employee
Splunk Employee

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.

View solution in original post

johann_brajer
New Member

@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

0 Karma

theouhuios
Motivator

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

0 Karma

theouhuios
Motivator

Thanks @ziegfried. Good to know about that.

0 Karma

ziegfried
Influencer

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.

0 Karma

btsay_splunk
Splunk Employee
Splunk Employee

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.

theouhuios
Motivator

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

0 Karma

theouhuios
Motivator

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

What errors are you getting in the log?

---
If this reply helps you, Karma would be appreciated.
0 Karma

araitz
Splunk Employee
Splunk Employee

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?

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...