Dashboards & Visualizations

Tokens to filter dbxquery output

sochsenbein
Communicator

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$'"
0 Karma

niketn
Legend

[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!

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

sochsenbein
Communicator

@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.

0 Karma

niketn
Legend

Can you change |where to |search and try?

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

sochsenbein
Communicator

@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."

0 Karma
Get Updates on the Splunk Community!

Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...

Welcome to the Splunk Community!

(view in My Videos) We're so glad you're here! The Splunk Community is place to connect, learn, give back, and ...

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...