Dashboards & Visualizations

How to create a token that points to multiple values returned from dbquery?

amandaxtru
Engager

I created a drop-down that lists the METRO_CITY name. When selected, it will return multiple router name prefixes to get all routers in that city. How would I pipe all these router names that were returned into a token to be used in another dbquery in the following table?

  <input type="dropdown" token="METRO_CITY" searchWhenChanged="true">
    <label>Select a location:</label>
    <choice value="London">london</choice>
    <search>
      <query>| dbquery "ROUTERDB" "SELECT METRO_CITY FROM LKP_LOCATION_EDITED" | dedup METRO_CITY</query>
    </search>
   <default value="Miami (FL)">Miami</default>
    <fieldForLabel>METRO_CITY</fieldForLabel>
    <fieldForValue>METRO_CITY</fieldForValue>


    <change>
      <condition value="$METRO_CITY$">
        <set token="search_area">
          <search>
            <query>
              | dbquery "ROUTERDB" "SELECT DEVICE_PREFIX FROM LKP_LOCATION_EDITED WHERE METRO_CITY LIKE '%$METRO_CITY$%'" 
          </query>
        </search>
      </set>
      </condition>
    </change>


  </input>

  <table id="highlight">
    <title>GWR</title>
    <search>
      <query>| dbquery ROUTERDB  "select * from tbl_sample_stats WHERE ROUTER LIKE '%$search_area$%' " | table ROUTER </query>
    </search>
0 Karma
1 Solution

sundareshr
Legend

In you second query (DEVICE_PREFIX) use makemv to create a multivalue field and use that in your final query. You may have to use IN as opposed to LIKE and massage the text some. Hopefully, this gets you started.

| dbquery "ROUTERDB" "SELECT DEVICE_PREFIX FROM LKP_LOCATION_EDITED WHERE METRO_CITY LIKE '%$METRO_CITY$%'"  |  stats values(DEVICE_PREFIX) AS DEVICE_PREFIX | makemv DEVICE_PREFIX delim=","  | table DEVICE_PREFIX

and in the final query, try using IN

| dbquery ROUTERDB  "select * from tbl_sample_stats WHERE ROUTER IN '%search_area%' " | table ROUTER 

View solution in original post

sundareshr
Legend

In you second query (DEVICE_PREFIX) use makemv to create a multivalue field and use that in your final query. You may have to use IN as opposed to LIKE and massage the text some. Hopefully, this gets you started.

| dbquery "ROUTERDB" "SELECT DEVICE_PREFIX FROM LKP_LOCATION_EDITED WHERE METRO_CITY LIKE '%$METRO_CITY$%'"  |  stats values(DEVICE_PREFIX) AS DEVICE_PREFIX | makemv DEVICE_PREFIX delim=","  | table DEVICE_PREFIX

and in the final query, try using IN

| dbquery ROUTERDB  "select * from tbl_sample_stats WHERE ROUTER IN '%search_area%' " | table ROUTER 

amandaxtru
Engager

The first query you gave me returns multiple router names separated by a space. e.g. router1 router2 router3
For the second query, how does that translate into the '%search_area%'?
Because the keyword IN is usually done like WHERE ROUTER IN ('router1' router2'... etc);
What I mean to ask is how does the IN keyword work with the token $search_area$ and the "%" wildcard?

0 Karma

sundareshr
Legend

Hmm, wonder why the delim is not working. Try this instead.

For DEVICE_PREFIX, use this query

| dbquery "ROUTERDB" "SELECT DEVICE_PREFIX FROM LKP_LOCATION_EDITED WHERE METRO_CITY LIKE '%$METRO_CITY$%'"  |  stats count by DEVICE_PREFIX | fields - count | rename DEVICE_PREFIX AS ROUTER  |  format

and in the final query

| dbquery ROUTERDB  "select * from tbl_sample_stats WHERE $search_area$"  | table ROUTER
0 Karma

amandaxtru
Engager

Another question... The router prefix needs a wildcard character to find all of the routers with the prefix.
The query returns something like ( ( ROUTER="PHIL" ) OR ( ROUTER="PHL02" ) OR ( ROUTER="PHL23" ))
but I need it to be ((ROUTER LIKE 'PHIL%') OR (ROUTER LIKE 'PHL02%') OR (ROUTER LIKE 'PHL23%')).
How would I go about this? Also thanks for the help!!! :')

0 Karma

sundareshr
Legend

Try replacing the string, like this

  | dbquery "ROUTERDB" "SELECT DEVICE_PREFIX FROM LKP_LOCATION_EDITED WHERE METRO_CITY LIKE '%$METRO_CITY$%'" | stats count by DEVICE_PREFIX | fields - count | rename DEVICE_PREFIX AS ROUTER  | format |eval search=replace(search, "=", " LIKE ")
0 Karma

amandaxtru
Engager

Thanks so much!!!

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