In what format should the rising_column be?

[joshua_hart]

I'm using DB Connect to extract McAfee ePO events from a MS SQL database and I'm using an auto-increment field as the rising_column field to tail the database. When I do that, I receive the following DB Connect error:

2013-06-18 08:43:14.456 dbx9419:ERROR:TailDatabaseMonitor - Error while executing database monitor: java.sql.SQLException: Invalid column name 'event_id'.
java.sql.SQLException: Invalid column name 'event_id'.
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:203)
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:679)

The event_id column is an alias of the EPOEvents.AutoID column, but when I use any of the following: [EPOEvents].[AutoID], EPOEvents.AutoID, [AutoID], AutoID, [EPOEvents].[event_id], or [event_id], the error reports that the field is not part of the resulting record set.

What is the format for specifying the rising_column field? Thanks!

-Josh

[leppalaa]

Hello,

Just in case you or someone else is still struggling with this:

The problem seems to be in the renaming of the event_id / EPOEvents.AutoID column.

I had the same problem and found a solution by wrapping the custom SQL select into another outer select and then using the event_id as the rising_column. This way, the correct "column" name (event_id) exists in the original data set and the resulting data set.

I did it like this, you can modify any part if needed. This works for me at least:

SELECT * FROM (

.... the query string from TA_mcafee comes here ...

) as outer_table
{{WHERE $rising_column$ > ?}}

I also needed to disable one of the columns that try to write to the login_user field in the mcafee query, because otherwise the outer SELECT would fail (two inputs for the same field name, login_user). You can see this problem by looking at the mcafee SQL query. I disabled the select row by commenting out the SourceUserName column from the SQL statement, like this:
--[EPOEvents].[SourceUserName] as [logon_user],

This seems to do the trick for me at least.

[steven87vt]

did you ever get a solution working? I have the exact same issue.

[joshua_hart]

I'm using a custom SQL query taken from the TA-mcaffee package. The query is found in the mcafee_epo.py.pymssql file. Instead of the top 10000 rows, I'm selecting all rows using the {{WHERE $rising_column$ > ?}} statement.

[asimagu]

it needs to be an increasing number

[asimagu]

Sorry, did not get what you were asking right. Maybe you can do a simple query to test that alias, Select only AutoID from the table and use it as a rising_column. If that works, try to make it more complex bit by bit

[joshua_hart]

Right. What I wondering, is how should I format the field name that I define as my rising_column? The field I'm using is an increasing number, but I always get one of the following errors regardless of how I format the field name: "Field name not found in result set" or "Invalid column name"

[ziegfried]

Please provide more info about the settings you chose for the database input. Are you using a custom query?