Tokens to filter dbxquery output

[sochsenbein]

On my dashboard, I have a report that runs a Stored Procedure using dbxquery. I have added a dropdown input on the dashboard to allow the client to filter out data returned by the Stored Procedure, however, tokens do not appear to be working with dbxquery.

The following, without a token, filters out the table just fine:

| dbxquery connection=THE_DATABASE query="EXEC DATABASE..STORED_PROCEDURE" 
| where ProductType="PRODUCT123" 
| table ProductType ProductName ProductQuantity ProductID

However, when using a token, it returns nothing:

| dbxquery connection=THE_DATABASE query="EXEC DATABASE..STORED_PROCEDURE" 
| where ProductType=$ProductTypeFilter$ 
| table ProductType ProductName ProductQuantity ProductID

I have tried using quotes around the token, using search instead of where, and using match/like commands.

UPDATE: I changed the Stored Procedure to take in an argument, however, I still cannot get the token to be passed through.

Works:

| dbxquery connection=THE_DATABASE query="EXEC DATABASE..STORED_PROCEDURE @ProductTypeFilter = 'PROD'"

Does not work:

| dbxquery connection=THE_DATABASE query="EXEC DATABASE..STORED_PROCEDURE @ProductTypeFilter = '$ProductTypeFilter$'"

[niketn]

[UPDATED ANSWER]

Added run anywhere example to show that token is set/unset properly. I have used | search ProductID="$tokProductID$" in the example.

<form>
  <label>Filter by token</label>
  <fieldset submitButton="false"></fieldset>
  <row>
    <panel>
      <input type="dropdown" token="tokProductID" searchWhenChanged="true">
        <label>Product ID</label>
        <choice value="1234">Apple</choice>
        <choice value="2345">Banana</choice>
        <default>1234</default>
      </input>
      <table>
        <search>
          <query>| makeresults
| eval _raw="ProductType=\"fruit\" ProductName=\"apple\" ProductQuantity=\"good\" ProductID=\"1234\""
| append [| makeresults
| eval _raw="ProductType=\"fruit\" ProductName=\"banana\" ProductQuantity=\"bad\" ProductID=\"2345\""]
| KV
| fields - _*
| search ProductID="$tokProductID$"</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>
</form>

@sochsenbein Seems like token is not passed to Stored procedure. You are trying to filter results in Splunk. Ideally you should pass parameter to SP so that only required results are fetched.

However, for the time being, try one of the following

1) string escaped token |s$

 | dbxquery connection=THE_DATABASE query="EXEC DATABASE..STORED_PROCEDURE "
 | where ProductType=$ProductTypeFilter|s$ 
 | table ProductType ProductName ProductQuantity ProductID

2) double quotes wrapped token

 | dbxquery connection=THE_DATABASE query="EXEC DATABASE..STORED_PROCEDURE "
 | where ProductType="$ProductTypeFilter$" 
 | table ProductType ProductName ProductQuantity ProductID

Please try out and confirm!

[sochsenbein]

@niketnilay I tried both of these methods before posting, but forgot to mention I tried the "|s" method, sorry. The "|s" results in the error message "Unknown search command 's'. But I do agree, it'd be better to just pass the token to the Stored Procedure.

[niketn]

Can you change |where to |search and try?

[sochsenbein]

@niketnilay in my post I mentioned that I tried that haha.

"I have tried using quotes around the token, using search instead of where, and using match/like commands."