topic db connect - input joining 3 tables in Splunk Search

db connect - input joining 3 tables

asimagu — Fri, 14 Jun 2013 00:03:39 GMT

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;

Re: db connect - input joining 3 tables

ziegfried — Sun, 16 Jun 2013 16:24:32 GMT

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_generation

Re: db connect - input joining 3 tables

asimagu — Mon, 17 Jun 2013 03:46:10 GMT

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

Re: db connect - input joining 3 tables

ziegfried — Mon, 17 Jun 2013 11:29:16 GMT

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

Re: db connect - input joining 3 tables

asimagu — Tue, 18 Jun 2013 00:25:46 GMT

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"

Re: db connect - input joining 3 tables

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

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

Re: db connect - input joining 3 tables

asimagu — Wed, 19 Jun 2013 04:02:37 GMT

the one that is at the beginning of the post 😉

Re: db connect - input joining 3 tables

ziegfried — Wed, 19 Jun 2013 14:58:17 GMT

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

Re: db connect - input joining 3 tables

asifhj — Fri, 17 Oct 2014 13:19:24 GMT

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