topic Re: Tokens in dbconnect 2 sql query?? in Dashboards & Visualizations

Tokens in dbconnect 2 sql query??

mrgibbon — Sat, 23 Jan 2016 03:59:31 GMT

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.

Re: Tokens in dbconnect 2 sql query??

dart — Mon, 25 Jan 2016 09:27:28 GMT

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>

Re: Tokens in dbconnect 2 sql query??

mrgibbon — Tue, 26 Jan 2016 05:29:38 GMT

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>

Re: Tokens in dbconnect 2 sql query??

mrgibbon — Wed, 27 Jan 2016 08:21:28 GMT

Fixed, thanks @Dart