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>
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
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
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?
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
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!!! :')
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 ")
Thanks so much!!!