Splunk DB Connect 1: Setting up a dbmon-tail for an MSSQL database with Elmah based error logging, how do I properly include the rising column in the query?

[rickyholland87]

Hello,

I'm currently trying to connect Splunk to an MSSQL based database that contains Elmah based error logging using Splunk DB Connect 1.

When selecting the input type "Full Dump", the connection is working fine, but a full dump is not really suitable as it's causing duplicate events.

When trying to use a "Tail" option then I am being given the following error;

Encountered the following error while
trying to save: In handler
'dbx-monitors': Unable to xml-parse
the following data: Failed to
validate:
com.splunk.config.SplunkConfigurationException:
Error validating dbmonTail for
monitor=dbmon-tail://ElmahErr/... See
splunkd.log for full data.

My connection is currently configured as;

Any ideas why I am getting the above error?

Thanks for the help.

[rickyholland87]

Looking at the Splunkd log it shows;

08-19-2015 12:23:14.681 +0100 ERROR AdminManagerExternal - Received malformed XML from external handler:\nFailed to validate: com.splunk.config.SplunkConfigurationException: Error validating dbmonTail for monitor=dbmon-tail://ElmahErr/Elmah_Err: [Elmah_Err] Invalid query "DECLARE @tmp DATETIME\r\r\nSET @tmp = DATEADD(hour,-1,GETDATE())\r\r\nexec GetElmahData @tmp, NULL" without proper {{ ... $rising_column$ > ?}} pattern! with query = \r\r\nfalse<class 'spp.java.bridge.JavaBridgeError'>Command com.splunk.dbx.monitor.DatabaseMonitorValidator returned status code 17Traceback (most recent call last):\r\n  File "C:\Program Files\Splunk\Python-2.7\Lib\site-packages\splunk\admin.py", line 70, in init\r\n    hand.execute(info)\r\n  File "C:\Program Files\Splunk\Python-2.7\Lib\site-packages\splunk\admin.py", line 526, in execute\r\n    if self.requestedAction == ACTION_CREATE:   self.handleCreate(confInfo)\r\n  File "C:\Program Files\Splunk\etc\apps\dbx\bin\spp\config.py", line 230, in handleCreate\r\n    self.handleModification("create", output)\r\n  File "C:\Program Files\Splunk\etc\apps\dbx\bin\spp\config.py", line 218, in handleModification\r\n    id, props = self.process_modification(id, props, type=type, output=output)\r\n  File "C:\Program Files\Splunk\etc\apps\dbx\bin\rest_handler_dbmon.py", line 95, in process_modification\r\n    self.validateConfig(stanza, props)\r\n  File "C:\Program Files\Splunk\etc\apps\dbx\bin\rest_handler_dbmon.py", line 126, in validateConfig\r\n    executeBridgeCommand("com.splunk.dbx.monitor.DatabaseMonitorValidator", args, checkStatus=True)\r\n  File "C:\Program Files\Splunk\etc\apps\dbx\bin\spp\java\bridge.py", line 182, in executeBridgeCommand\r\n    raise JavaBridgeError("Command %s returned status code %s" % (cmd, ret))\r\nJavaBridgeError: Command com.splunk.dbx.monitor.DatabaseMonitorValidator returned status code 17\r\n\r\n

So it seems to be grumbling about the Rising_Column not being included in the query - how do I include it properly?

[jcoates_splunk]

Like this: http://docs.splunk.com/Documentation/DBX/1.2.1/DeployDBX/Configuredatabasemonitoring#Configure_input...

SELECT * FROM my_table {{WHERE $rising_column$ > ?}}