Splunk DB Connect 1: Using a SQL variable as a rising column to create a dbmon-tail, how do I resolve error "Column unique_concat not found"?

[justinfranks]

Hi all,

I have a confusing issue here. I have a proprietary SQL system that I am connecting to, to get data from. It is AttacheBI which runs C-tree Ace SQL in the back end and I only have read-only access to the data.

I have established my connection through the use of an JDBC-ODBC bridge and I can browse the tables and fields using DB Query. So the connection is good there.

I am trying to setup a dbmon-tail, however, there is no single unique field in the data. I have spoken with Attache support and I would need to concat 3 fields in order to get my unique field to use as a Rising Column. Here is the SQL command I am using:

select TO_NUMBER(concat(concat(TO_CHAR(so.financialyearstart, 'YYYYMMDD'), LPAD(TO_CHAR(glt.batchnum), 10, '0')), LPAD(TO_CHAR(glt.seqnum), 10, '0'))) AS unique_concat, glt.*, glte.*, gl.description
FROM admin.systemgloptions2 so, (admin.generalledger_transaction glt LEFT JOIN admin.generalledger_transactionextension glte ON glt.code = glte.code AND glt.seqnum = glte.seqnum AND glt.batchnum=glte.batchnum)
LEFT JOIN admin.generalledger gl ON gl.code = glt.code
WHERE char_length(glt.code) > 0 {{AND $rising_column$ > ?}}
ORDER BY unique_concat

Here is a screen shot of the Input:

This query works fine in the DB Query part of DBX, but when I use it to create the dbmon-tail, I get this 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://AttacheBi... See splunkd.log for full data.

Looking at the logs, I can see that DBX has issues that the field unique_concat is not in the data.

From the splunkd.log:

08-18-2015 11:19:01.472 +1000 ERROR AdminManagerExternal - Received malformed XML from external handler:\nFailed to validate: com.splunk.config.SplunkConfigurationException: Error validating dbmonTail for monitor=dbmon-tail://AttacheBi/attachebi_general_ledger: [FairCom][ODBC FairCom Driver 10.3.0.19319(Build-140121)][ctreeSQL] -20006 Column unique_concat not found/specified with query

Any ideas on how I might be able to get around this? As stated before, I cannot modify the source in any way, as it is a locked down accounting package.

[jcoates_splunk]

that looks like our validation code is barfing on the SQL statement... I would try removing the underscore, and I would try making or tweaking the inputs.conf directly without the UI to see if that works. You might have found a bug in the validator.

[rampent]

Even without the underscore, it still errors.

09-14-2015 09:44:56.460 +1000 ERROR AdminManagerExternal - Received malformed XML from external handler:\nFailed to validate: com.splunk.config.SplunkConfigurationException: Error validating dbmonTail for monitor=dbmon-tail://AttacheBi/AttacheBI_General_Ledger_Ingest: [FairCom][ODBC FairCom Driver 10.3.0.19319(Build-140121)][ctreeSQL] -20006 Column uniqueconcat not found/specified with query