Splunk Search

db connect - input joining 3 tables

asimagu
Builder

Hi

I have an issue trying to create an input with db connect that throws this error

2013-06-12 11:29:23.417 dbx7796:ERROR:TailDatabaseMonitor - Configuration Error: Invalid query specified! Found multiple placeholders for condition - this is invalid.

The query is maybe too complex for db connect as we are joining 3 tables and using where clauses to match specific conditions.

It's not clear in the documentation how complex we can make this query, so my guess is that this is probably too much for db connect?

This is my query

select s.colb, s.colc, pos.cold, s.cole, s.colf, pos.colg, s.colh, pos.coli,
case when rm.razon is null and pos.coli = 'T2' then 'Pachin'
     when rm.razon is null and pos.coli != 'T2' then 'Other'
     else rm.razon
end as "REASON_V",
pos.comentarios, pos.cancel_coli, pos.cancel_comentarios
from schema.table1 s
left outer join schema.table3 pos
  on s.colp = pos.colp
  and pos.cole = 'PAPA'
left outer join schema.table2 rm
  on pos.coli = rm.coli
  and rm.razon = 'PAPA'
where s.cole = 'PAPA'
and s.cola_n = 1
and s.colb > trunc(sysdate-7)
order by s.colb desc;
0 Karma

asifhj
Path Finder

Why don't you create a view of the query and give a try?

0 Karma

ziegfried
Influencer

DB Connect does not limit the complexity of queries for database inputs. For the tail input mode they have to comply with some rules, though:

1) They have to include an (exactly one) optional-condition-block {{...}} to distinguish between the SQL used for initial- and any subsequent execution. For the initial execution this block is omitted, for any subsequent run, it's included.

 ... {{ WHERE foo = ? }}

2) This optional-condition-block has to include the (exactly one) placeholder for the checkpoint value ? which is replaced with the checkpoint value.

? 

3) There must not be any other value placeholders in the SQL (ie. only 1 unquoted ? within the SQL query)

More info here http://docs.splunk.com/Documentation/DBX/latest/DeployDBX/Configuredatabasemonitoring#Query_generati...

ziegfried
Influencer

The error you're referring to is a from a database input and not from dbquery.

0 Karma

asimagu
Builder

the one that is at the beginning of the post 😉

0 Karma

ziegfried
Influencer

So it's not working in "Query database"? What error message do you get?

0 Karma

asimagu
Builder

I did not type that in there, but that would go at the end.That is clear but I think I would like to have this working in the db connect app "Query database"

0 Karma

ziegfried
Influencer

I can't find the {{ .. }} part in your query.

0 Karma

asimagu
Builder

thanks, I have read all the doc but I'm afraid my query is correct and it is still not working. I just edited my question in order to include my query

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

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

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...