All Apps and Add-ons
Highlighted

Does Splunk DB Connect V2 supports queries variables?

Explorer

Hi,

We used to have Splunk DB Connect lookups to Advanced SQL with parameters (using $field_name$ as an identifier) - which let us use special queries as lookup

Since version 2, we can't find a way to have these special queries. Is there any option to config customized queries?

Thanks,

EG

Highlighted

Re: Does Splunk DB Connect V2 supports queries variables?

Explorer

Are you referring to the tokens used in a dashboard to populate a search? It is possible to do, and I have it working in our dashboards now. (it took a bit of trial and error) Remember the SQL queries are URL encoded now, so encode everything but the $token$. If you copy/paste the string into an automatic encoder, it will encode the $ and not work. Here's a snippet of one of my dashboard queries: "where%20EmpID%20%3D%20%27$id$%27" The $id$ is replaced with whatever variable the analyst places in the field to query the SQL database on.

0 Karma
Highlighted

Re: Does Splunk DB Connect V2 supports queries variables?

Explorer

After beating my head against a wall on this, I've found this is not currently possible for lookups. It either screws up the query wrapping Splunk does, or when that is disabled, it attaches a second where clause, which makes it invalid (since it is not AND <condition>), but WHERE <this> WHERE <that>.

The more problematic hurdle is that Splunk will batch up the queries and provide them into an IN clause.

For one of my use cases I'm getting around this by providing a materialized view, so the query is still performing well, and the query logic is housed in that view.

My second use case won't easily be supported, where I have a user defined function that I need to pass the parameter to. This, I'll have to figure something else out.

Both of these work fine with dbxquery, but that is harder to use as a lookup like this.

0 Karma
Highlighted

Re: Does Splunk DB Connect V2 supports queries variables?

Path Finder

Hi!
Can you show your workaround (example) with dbxquery?

0 Karma
Highlighted

Re: Does Splunk DB Connect V2 supports queries variables?

Explorer

I would use outputlookup after the dbxquery, on a schedule, and then use lookup against that csv file.

0 Karma