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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...