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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...