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

[amandaxtru]

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>

[sundareshr · Accepted solution]

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]

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?

[sundareshr]

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

[amandaxtru]

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!!! :')

[sundareshr]

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 ")

[amandaxtru]

Thanks so much!!!