Classic Dashboard Input Dynamic Options

[texascj]

Imagine, if you will, table view lookup that has been setup to pull the Host name, the environment (Dev/Test/Prod) and the Server type (Database, Web App, SSO, etc...) and the application the server supports.

I have 4 Input field LOVs setup.

1. Enclave...lets me choose Dev / Test / Prod, those are the only 3 options and the token name is "enclave"

2. Type...shows Database, Web App, SSO, Other ... again those are the only options, token name is "type"

3. Application...say HR, Payroll, Order Entry and HealthCare ... again, 4 options, token name is "app"

4.  This should be a DYNAMIC LOV that shows only the servers in the table view lookup that meet the condition set by the first 3 LOVs.

...example

Enclave set to Dev, Type set to Web App, Application set to HR.  My table view clearly shows there are 2 web app server names so the 4th LOV should show Server003, Server007, All.  The token would then be set based on the choice (003 or 007) and if "All" were picked the token would be Server003, Server007.  This would drive the panel searches.

Is this possible?  I can get the 4th LOV to run but it doesn't give me a list.

[texascj · Accepted solution]

Update

While this works :

| inputlookup servers | dedup host | sort host | table host host

The issue I have here is I need to use the token to shrink the returned list.  As noted above, this DOES NOT work:

| inputlookup servers where environment = $token$

| dedup host | sort host | table host host

 ...this returns NOTHING and I've spent several hours going bald trying to figure this out.  Then I found it.

The issue is the WHERE clause.  Apparently, you cannot do an inputlookup to return 1 field while doing the comparision on another field.  Who knew?  So if the query is returning both the host AND the environment...then it works.

What I ended up with is:

| inputlook servers

| table host, environment

| where environment = "$token$"

| fields - environment

...and now it's happy.

Thanks to all as I wouldn't haven't this far w/o getting the answers to my stupid questions!  🙂

[marnall]

Could you post the sanitized source code of your dashboard or inputs? It sounds like you are doing the right thing in using a dynamically populated 4th LOV, but maybe something isn't set right if the search is running but not populating the final LOV.

[texascj]

At the moment the query reads as :

| inputlookup my_servers

| stats values(host)

Obviously there is no token at the moment to restrict the list so I should get a rather long list of servers to select from.

The source for the XML frame or block is

<input type="multiselect" token="SERVERS">

     <label>SERVERS</label>

     <search>

          <query> | inputlookup servers

                             | stats values(host)</query>

     </search>

     <fieldForLabel>host</fieldForLabel>

     <fieldForValue>host</fieldForValue>

     <delimiter> </delimiter>

</input>

[ITWhisperer]

Try something like this

<query> | inputlookup servers
        | where Enclave="$ENCLAVE$" AND Type="$Type$" AND Application="$APPLICATION$"
        | stats values(host) as host</query>

Otherwise the field name is "values(host)" which doesn't match with fieldForLabel and fieldForValue

[texascj]

Okay...almost there.

Adding the "as host" evidently forces the column / field name to read as "host" which must match the property setting of the UI.  Simple enough.

However, the query is returning a string rather than several strings.  So, my expected result for the field would be to see

Server001

Server004

Server007

...but what I get is

Server001Server004Server007

Is it possible to get these to be individual choices?

[ITWhisperer]

The multiselect dropdown is essentially a multi-value field. This can be delimited (using the delimiter option) for example setting it to a comma. Since you have string values, you might want to set valuePrefix and valueSuffix to double quotes ("), then you can use the token in a IN clause. That is, it depends on how you are using the token in your dashboard searches.

[ITWhisperer]

Dropdown inputs can be set by a static list or a dynamic list or a combination of both. For a dynamic list, the search should return unique values for the list. So, in your instance, this could be filtering from a lookup table which holds all your servers, or by an index search to pull all the servers from your logs (although you would probably still need a lookup to get the other fields).

[texascj]

Finally got what I wanted.

| inputlookup servers | dedup host | sort host | table host host

...this gives me the delineated list of servers that are individually selectable.  Now I need to add my parameter tokens to trim the list down based on the first 3 LOVs.

Thanks all!

[texascj]

As luck would have it

This works :

| inputlookup servers | dedup host | sort host | table host host

However, this does not

| inputlookup servers where environment = $token$

| dedup host | sort host | table host host

.........

If I replace $token$ w/ "DEV" (which is what is in the table) it works.  I know the $token$ has the value, out of scope maybe?

[texascj]

That is how I understood it should work.  However, when I create the list input and go to the Dynamic Option and create the query...it returns nothing...it just says "Populating" so I know the query ran.  If I click on the link to run the query in a search window, I can see results.

[ITWhisperer]

You should ensure the tokens always have a value by setting them in an init block - just using an initial / default value is not enough to set the input token to a value.