topic Re: Error dbconnect Invalid column name in Splunk Search

Error dbconnect Invalid column name

agentelinux — Mon, 17 Feb 2014 18:44:13 GMT

My query in dbconnect DatabaseInput is:

SELECT b.modifielddate AS [Modfielddate], a.name, b.amount
FROM sales b
inner join users a b.id=a.id

Rising Column = Modfielddate
Timestamp column = Modfielddate

LOG error:

2014-02-17 15:14:27.001 dbx1031:INFO:TailDatabaseMonitor - Database monitor=[dbmon-tail://sales/pro] finished with status=false resultCount=0 in duration=311 ms
2014-02-17 15:14:27.002 dbx1031:INFO:ExecutionContext - Execution finished in duration=312 ms
2014-02-17 15:14:27.002 monsch1:INFO:Scheduler - Execution of input=[dbmon-tail://sales/pro] finished in duration=311 ms with resultCount=0 success=false continueMonitoring=true
2014-02-17 15:16:34.859 dbx6559:INFO:Splunkd - Splunkd REST Keep-alive successful for user splunk-system-user
2014-02-17 15:16:34.859 dbx6559:INFO:ExecutionContext - Execution finished in duration=7 ms
2014-02-17 15:21:34.872 dbx7179:INFO:Splunkd - Splunkd REST Keep-alive successful for user splunk-system-user
2014-02-17 15:21:34.872 dbx7179:INFO:ExecutionContext - Execution finished in duration=8 ms
2014-02-17 15:24:28.121 dbx2379:INFO:TailDatabaseMonitor - Executing database monitor=[dbmon-tail://sales/pro]
2014-02-17 15:24:28.278 dbx2379:INFO:TailDatabaseMonitor - Applying latest tail.rising.column value=2014-02-14 19:00:22.8
2014-02-17 15:24:28.433 dbx2379:ERROR:TailDatabaseMonitor - Error while executing database monitor: java.sql.SQLException: Invalid column name 'ModifieddateA'.
java.sql.SQLException: Invalid column name 'ModifieddateA'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:776)
at com.splunk.dbx.sql.Database.query(Database.java:257)
at com.splunk.dbx.monitor.impl.TailDatabaseMonitor.performMonitoring(TailDatabaseMonitor.java:113)
at com.splunk.dbx.monitor.DatabaseMonitorExecutor.executeMonitor(DatabaseMonitorExecutor.java:126)
at com.splunk.dbx.monitor.DatabaseMonitorExecutor.call(DatabaseMonitorExecutor.java:102)
at com.splunk.dbx.monitor.DatabaseMonitorExecutor.call(DatabaseMonitorExecutor.java:37)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
at java.util.concurrent.FutureTask.run(FutureTask.java:166)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
at java.util.concurrent.FutureTask.run(FutureTask.java:166)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
at java.util.concurrent.FutureTask.run(FutureTask.java:166)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
at java.lang.Thread.run(Thread.java:636)
2014-02-17 15:24:28.434 dbx2379:INFO:TailDatabaseMonitor - Database monitor=[dbmon-tail://sales/pro] finished with status=false resultCount=0 in duration=313 ms
2

Re: Error dbconnect Invalid column name

aelliott — Mon, 17 Feb 2014 18:53:14 GMT

you will need to change this to:

WITH myTable as (SELECT b.modifielddate AS [Modfielddate], a.name, b.amount
FROM sales b
inner join users a b.id=a.id)
select * from myTable

Re: Error dbconnect Invalid column name

agentelinux — Mon, 17 Feb 2014 19:00:39 GMT

hummm

WITH myTable as (
SELECT b.modifielddate AS [Modfielddate], a.name, b.amount
FROM sales b
inner join users a b.id=a.id
{{where $rising_column$ > ?}} )
select * from myTable

correct?

Re: Error dbconnect Invalid column name

agentelinux — Mon, 17 Feb 2014 19:05:02 GMT

Humm,

WITH myTable as (
SELECT ... 3 millions registers )
select * from myTable
{{where $rising_column$ > ?}}

Not is possible.

Re: Error dbconnect Invalid column name

aelliott — Mon, 17 Feb 2014 19:07:05 GMT

Possible is... selecting * from that query that is returned is simply just running that query, but there is a bug in splunk db connect with naming tables... such as a and b.. so that is the workaround. Here is more info on this issue:
http://docs.splunk.com/Documentation/DBX/1.1.2/DeployDBX/Releasenotes

Re: Error dbconnect Invalid column name

agentelinux — Mon, 17 Feb 2014 19:20:58 GMT

One minute, I am try.

Re: Error dbconnect Invalid column name

agentelinux — Mon, 17 Feb 2014 19:58:04 GMT

Perfect.
thanks

Re: Error dbconnect Invalid column name

agentelinux — Tue, 18 Feb 2014 20:17:44 GMT

Not solution.

Look:
http://answers.splunk.com/answers/123382/dbx801errortaildatabasemonitor-the-column-xxx-is-not-present-in-the-result-set

Re: Error dbconnect Invalid column name

agentelinux — Tue, 18 Feb 2014 20:30:06 GMT

Ok, the query is correct:

SELECT b.modifieddate , a.name, b.amount
FROM sales b
inner join users a b.id=a.id

After of new version no is necessary more alias.