Deployment Architecture

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
Path Finder

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:

alt text

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.

0 Karma

jcoates_splunk
Splunk Employee
Splunk Employee

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.

0 Karma

rampent
New Member

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
0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...