Hi all, I'm wondering if it possible to tokenise parameters within an sql query when using dbconnect 2.
eg - "SELECT * FROM "Permanent"."db"."Status" WHERE LoadDate=$datetoken$"
OR
"SELECT * FROM "Permanent"."db"."Status" WHERE $datetoken$"
Thanks in advance.
You can use this with dbxquery, and you probably want to use the $token_name|u$
form to urlescape your query:
<form>
<label>Query_Builder</label>
<fieldset submitButton="true">
<input type="dropdown" token="connection">
<label>Connection</label>
<search>
<query>| rest /servicesNS/-/-/db_connect/connections | table title</query>
</search>
<fieldForLabel>title</fieldForLabel>
<fieldForValue>title</fieldForValue>
</input>
<input type="radio" token="options">
<label> Options </label>
<choice value="shortnames=true">Short field names </choice>
<choice value="shortnames=false"> Long field names </choice>
</input>
<input type="text" token="db_query">
<label>DB Query</label>
</input>
<input type="text" token="splunk_query">
<label>Splunk PostProcess Query</label>
<default></default>
</input>
</fieldset>
<row>
<panel>
<event>
<title>| dbxquery connection=$connection$ query=$db_query|u$ $options$| $splunk_query$ </title>
<search>
<query>| dbxquery connection=$connection$ query=$db_query|u$ $options$ | $splunk_query$</query>
<earliest>0</earliest>
</search>
<option name="count">10</option>
<option name="list.drilldown">full</option>
<option name="list.wrap">1</option>
<option name="maxLines">5</option>
<option name="raw.drilldown">full</option>
<option name="rowNumbers">0</option>
<option name="table.drilldown">all</option>
<option name="table.wrap">1</option>
<option name="type">list</option>
<fields>[]</fields>
</event>
</panel>
</row>
</form>
Fixed, thanks @Dart
Thanks @dart
This is the code I'm working with as part of a wider project. This is the simplified version, part of a drilldown:
LoadDate_tok is one of the tokens passed through from the previous dashboard.
I haven't been able to get the urlescape to work.
<search>
<query>
| stats count
| eval LoadDate = strftime($LoadDate_tok$, "%Y-%m-%d")
| eval query="SELECT * FROM database.table WHERE LoadDate=$LoadDate$"
| eval query=replace(query, " ", "%20")
| eval query=replace(query, "=", "%3D")
</query>
<preview>
<condition match="'job.resultCount' > 0 ">
<set token="DBTable">$result.DBTable$</set>
<set token="LoadDate">$result.LoadDate$</set>
<set token="query">$result.query$</set>
</condition>
</preview>
</search>
<row>
<panel>
<table>
<search>
<query> | dbxquery connection=test query=$query|u$</query>
</search>
</table>
</panel>
</row>
You can use this with dbxquery, and you probably want to use the $token_name|u$
form to urlescape your query:
<form>
<label>Query_Builder</label>
<fieldset submitButton="true">
<input type="dropdown" token="connection">
<label>Connection</label>
<search>
<query>| rest /servicesNS/-/-/db_connect/connections | table title</query>
</search>
<fieldForLabel>title</fieldForLabel>
<fieldForValue>title</fieldForValue>
</input>
<input type="radio" token="options">
<label> Options </label>
<choice value="shortnames=true">Short field names </choice>
<choice value="shortnames=false"> Long field names </choice>
</input>
<input type="text" token="db_query">
<label>DB Query</label>
</input>
<input type="text" token="splunk_query">
<label>Splunk PostProcess Query</label>
<default></default>
</input>
</fieldset>
<row>
<panel>
<event>
<title>| dbxquery connection=$connection$ query=$db_query|u$ $options$| $splunk_query$ </title>
<search>
<query>| dbxquery connection=$connection$ query=$db_query|u$ $options$ | $splunk_query$</query>
<earliest>0</earliest>
</search>
<option name="count">10</option>
<option name="list.drilldown">full</option>
<option name="list.wrap">1</option>
<option name="maxLines">5</option>
<option name="raw.drilldown">full</option>
<option name="rowNumbers">0</option>
<option name="table.drilldown">all</option>
<option name="table.wrap">1</option>
<option name="type">list</option>
<fields>[]</fields>
</event>
</panel>
</row>
</form>