topic Re: How to create a token that points to multiple values returned from dbquery? in Dashboards & Visualizations

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

amandaxtru — Fri, 08 Jul 2016 13:46:09 GMT

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>

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

sundareshr — Sat, 09 Jul 2016 20:15:38 GMT

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

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

amandaxtru — Tue, 29 Sep 2020 10:11:23 GMT

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?

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

sundareshr — Mon, 11 Jul 2016 20:53:46 GMT

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

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

amandaxtru — Tue, 12 Jul 2016 20:10:26 GMT

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

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

sundareshr — Wed, 13 Jul 2016 16:19:14 GMT

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

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

amandaxtru — Wed, 13 Jul 2016 18:27:46 GMT

Thanks so much!!!