Populate dropdown menu using lookup and tokens with multiple field values

[johnward4]

I am trying to populate a dropdown menu using a lookup table that contains all my server's hostname in one column and their Category in another

| inputlookup UFlookups.csv  
| dedup Category
| stats count by Category host
| fields - count

The query above populates the dropdown with the category names as intended but I'm only able to show one server per Category when there should be showing several. Could this be due to my dedup? Another thing I read was using eval tokens in the XML, if that's the preferred method, can someone help me understand how to should multiple host if my token is named Hosts?

[woodcock]

Try this:

| inputlookup UFlookups.csv 
| stats values(Category) AS Category BY host
| nomv Category
| eval host = host . "(" . Category . ")"
| table host

[woodcock]

Try this:

| inputlookup UFlookups.csv 
| stats values(host) AS host BY Category
| nomv host
| eval Category = Category . "(" . host . ")"
| table Category

[gcusello]

Hi,
you can use two connected dropdown lists , in other words, you could create a dropdown list for your Categories and then use the choise to filter the server lists, something like this:

<input type="dropdown" token="Category">
  <label>Category</label>
  <choice value="*">All</choice>
  <search>
    <query>
       | inputlookup UFlookups.csv  
       | dedup Category
       | sort Category
       | table Category
    </query>
    <earliest>$Time.earliest$</earliest>
    <latest>$Time.latest$</latest>
  </search>
  <fieldForLabel>Category</fieldForLabel>
  <fieldForValue>Category</fieldForValue>
  <default>*</default>
  <prefix>Category="</prefix>
  <suffix>"</suffix>
</input>
<input type="dropdown" token="server">
  <label>Server</label>
  <choice value="*">All</choice>
  <search>
    <query>
       | inputlookup UFlookups.csv  
       | search $Category$
       | sort host
       | table host
    </query>
    <earliest>$Time.earliest$</earliest>
    <latest>$Time.latest$</latest>
  </search>
  <fieldForLabel>host</fieldForLabel>
  <fieldForValue>host</fieldForValue>
  <default>*</default>
  <initialValue>*</initialValue>
  <prefix>host="</prefix>
  <suffix>"</suffix>
</input>

Bye.
Giuseppe

[johnward4]

I'm getting the following error in my dashboard panels

Error in 'search' command. Unable to parase the search: Comparator '=' has an invalid term on the left hand side: host=host

[niketn]

@cusello, second dropdown query is better off this way:

    | inputlookup UFlookups.csv where $Category$
    | dedup host
    | sort host
    | table host

[renjith_nair]

@johnward4, yes the dedup deletes all the duplicate category. If you want both Category and host in the combination, then use dedup Category Host.
How do you want the multiple values in the token , because drop down can select only one value at a time? Are you looking for multiselect and a logical operator to connect them?

[johnward4]

I've been using a static method to get the desired representation so far but I'm testing using the lookup to populate the dropdown to show labels for my server "Category" field. The lookup has a full list of my hosts in the first column and their Category in the second column.

Old method, a dropdown Static option

Token : Hosts

Name : "BI" Value : dwhprd* OR host="dwhutilprd*" OR host="dwhclient*" OR host="dwhsql*" OR host="dwhmobile*" OR host="dwhmstr*" OR host="dwhiserver*" OR host="r2wdb02" OR host="r2wapp02" OR host="r2wpub02" OR host="dwhcisrv*" OR host="dwheisrv*"`

CPU Panel Query

|multisearch
   [search index=os host=$Hosts$ sourcetype=cpu cpu="all" |eval cpu_used_percent=100-PercentIdleTime]
   [search index=perfmon host=$Hosts$ source="Perfmon:CPU" counter="% User Time"  OR counter="% Processor Time"
    |eval cpu_user_percent=if(counter=="% User Time",round(Value,2),""), cpu_load_percent=if(counter=="% Processor Time",round(Value),""), cpu_used_percent=user_cpu+proc_cpu]
|eval lhost=upper(host)
|bin _time span=10m
|stats avg(cpu_user_percent) as user_cpu, avg(cpu_load_percent) as proc_cpu by host, _time
|stats last(user_cpu) as user_cpu, last(proc_cpu) as proc_cpu, sparkline(avg(user_cpu)) as "% User CPU Load", sparkline(avg(proc_cpu)) as "% Processor CPU Load" by host
|eval  user_cpu=round(user_cpu,2), proc_cpu=round(proc_cpu,2)
|rename user_cpu as "% Current User CPU Load", proc_cpu as "% Current Processor Load"
| sort - "% Current User CPU Load"