topic Re: In what format should the rising_column be? in Splunk Search

In what format should the rising_column be?

joshua_hart — Tue, 18 Jun 2013 13:41:25 GMT

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

Re: In what format should the rising_column be?

ziegfried — Tue, 18 Jun 2013 17:36:04 GMT

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

Re: In what format should the rising_column be?

asimagu — Thu, 20 Jun 2013 04:21:23 GMT

it needs to be an increasing number

Re: In what format should the rising_column be?

joshua_hart — Thu, 20 Jun 2013 12:02:45 GMT

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"

Re: In what format should the rising_column be?

joshua_hart — Mon, 28 Sep 2020 14:08:26 GMT

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.

Re: In what format should the rising_column be?

asimagu — Fri, 21 Jun 2013 02:40:34 GMT

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

Re: In what format should the rising_column be?

steven87vt — Thu, 11 Jul 2013 15:11:40 GMT

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

Re: In what format should the rising_column be?

leppalaa — Mon, 28 Sep 2020 16:23:51 GMT

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.