Splunk Search

Error dbconnect Invalid column name

agentelinux
Explorer

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

0 Karma

agentelinux
Explorer

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.

0 Karma

agentelinux
Explorer
0 Karma

aelliott
Motivator

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

agentelinux
Explorer

Perfect.
thanks

0 Karma

agentelinux
Explorer

One minute, I am try.

0 Karma

aelliott
Motivator

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

agentelinux
Explorer

Humm,

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

Not is possible.

0 Karma

agentelinux
Explorer

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?

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