Dashboards & Visualizations

How to add multiple fields from a CSV lookup as values in a drop-down?

alan20854
Path Finder

Hi,

I currently have two drop-downs (application and environment) in my dashboard, followed by a text input which the user can type into to search for certain events. I have been populating the two drop-downs with a lookup table, with 3 columns from the .csv I uploaded.
The first one contains the application name, the second the environment name, and the third the hosts that contain all the events from the selected application and environment.

Therefore, in my environment drop-down, the token name is 'env' and I currently have 'environment' as the Field for Label and 'hostCode' as the Field for Value.

Now, I have added another column in my lookup table, source, and I am trying to do the same thing I did for host (source is only dependent on application and environment). How do I retrieve the corresponding source and add it to the search?

This is my current search string: host = $env$ $srch$ ($srch$ is the string typed in by the user)
I would like the search to replicate host = $env$ source = $src$ $srch$

0 Karma

sundareshr
Legend

@alan20854 started this new block. The other one is getting a bit long.

Query for the HOST/SOURCE Dropdown (token=host_src)

     | inputlookup lookup.csv | search application="$app1$" | eval value="host=".hostCode." source=".sourceCode

Can you open the query in your panel in search and share that and names of fields you're looking for in the panel. Also, what is the name of the field in your index (not CSV) that has values for sourceCode (app1 app2 etc?). You need to make sure the field names are correct. host and source are the default fields.

0 Karma

alan20854
Path Finder

After running that query, I get a table with 5 columns: application, environment, hostCode, sourceCode, and value (a combination of hostCode and sourceCode).

At the bottom of all the events I am looking for, the last row contains host = .. source = .. sourcetype = .. The fields I am looking for in the panel are just host and source.

Also, in my second dropdown, the token = host_src and the field for value = hostCode

One more thing: Some of the values in my the hostCode column in my CSV contain 2 hosts - e.g. awslxappA-d* OR awslxappB-d* Thus, in the current search, which is host = awslxappA-d* OR awslxappB-d*, it only looks for events in the first value, so I would like it to be host = awslxappA-d* OR host = awslxappB-d*

0 Karma

sundareshr
Legend

So does this mean the original problem of host & source is now working? If not, I will need to see the panel query in search.

As for fields with more than one host, you either have to edit the csv to awslxappA-d* OR host=awslxappB-d* OR do an eval based drilldown on your dropdown.

0 Karma

sundareshr
Legend

I must admit, I don't claim to fully comprehend your requirements. I believe what you are looking for is 2 dropdowns; 1 with host & source and other with Application. And a textbox for keyword search. The user input from these three form elements should be used to search your data. Based on this understanding, here's one way to accomplish this

Query for the HOST/SOURCE Dropdown (token=host_src)

| inputlookup lookup.csv | search (application="$app1$" AND hostCode="$env$") | eval value="host=".hostCode." source=".src 

For label you can use whatever (Env OR value OR ...). But for value, use value

Query for your panel

index=xyz $host_src$ $srch$ | ...

Give this a try.

0 Karma

alan20854
Path Finder

I tried what you suggested but came up with no events.

To clarify, my lookup table contains four columns: application, environment, hostCode, and source in that order.
My first dropdown, Application, with token 'app1', simply contains the names of the applications.
The second dropdown, Environment, with token 'env', displays the possible environment names corresponding to that application, and based on your selection, a hostCode is assigned as the value for 'env'. After that, like you said, I have a textbox for keyword search, with token 'srch'.

Therefore, in my current search query, all I have is host = $env$ $srch$

Now, I would like to have this as my search query: host = $env$ source = $src$ $srch$ What I do not understand what to do is how/where to extract the source from the lookup table and include it in the search query.

0 Karma

sundareshr
Legend

Since you have only two drop downs, you will need to concatenate the host and source values based to populate the Env (token host_src in my example) dropdown. When you say you got no events, was that in the panel or in the dropdown? If it is in the panel, can you click on the little magnifying glass on the bottom, look at the final search query that is being executed.

0 Karma

alan20854
Path Finder

In the second dropdown, it only shows one possible option with your example, leaving out the others which I used to be able to see.
In the panel, it says "Search is waiting for input" even though I have selected everything

0 Karma

sundareshr
Legend

The search for the second dropdown, was one you shared as working. Can you share your dashboard code.

0 Karma

alan20854
Path Finder

This is my dashboard XML:

<input type="dropdown" token="app1" searchWhenChanged="true">
  <label>Application</label>
  <search>
    <query>| inputlookup Lookup.csv | stats count by application| table application</query>
  </search>
  <fieldForLabel>application</fieldForLabel>
  <fieldForValue>application</fieldForValue>
</input>
<input type="dropdown" token="host_src" searchWhenChanged="true">
  <label>Environment</label>
  <search>
    <query>| inputlookup Lookup.csv | search (application="$app1$" AND hostCode="$env$") | eval value="host=".hostCode." source=".src</query>
    <earliest>0</earliest>
  </search>
  <fieldForLabel>environment</fieldForLabel>
  <fieldForValue>hostCode</fieldForValue>
</input>
<input type="text" token="srch" searchWhenChanged="true">
  <label>Search</label>
  <initialValue>*</initialValue>
</input>
<input type="time" token="time">
  <label>Time</label>
  <default>
    <earliest>@d</earliest>
    <latest>now</latest>
  </default>
</input>
<panel>
  <event>

    <search>
      <query>$host_src$ $srch$</query>
      <earliest>$time.earliest$</earliest>
      <latest>$time.latest$</latest>
    </search>

Now the second dropdown doesn't even get populated, as it says "Could not create search"

0 Karma

sundareshr
Legend

That's right. In you second drop-down, you have hostCode=$env$ but you're not setting the token $env$ anywhere. Try taking that out. What will then happen is this drop-down will list all the hostCode & source for the selected application.

In other words, the search for your second drop-down should be

| inputlookup Lookup.csv | search application="$app1$" | eval value="host=".hostCode." source=".src

Replace src with the correct name of the field for source in your csv.

0 Karma

alan20854
Path Finder

I did what you said, but it still isn't working properly. The dropdowns are now working again, which is an improvement, but host_src seems to only contain the host values. When I open the query in search, it only searches for the hosts and the text in the textbox, leaving out the source.

In addition, I am looking for events with host = $host$ source = $src$ $srch$, not just $host$ $src$ $srch$. How can I do this?

For reference, my csv file looks like this:

application,environment,hostCode,sourceCode
applicationA,DEV,awslxappA-d*,appA
applicationA,PROD,awslxappA-p*,appA
applicationA,QA,awslxappA-q*,appA
applicationB,DEV,awslxappB-d*,appB
applicationC1,DEV,awslxappC-d*,appC1
applicationC1,PROD,awslxappC-p*,appC1
applicationC1,QA,awslxappC-q*,appC1
applicationC2,DEV,awslxappC-d*,appC2
applicationC2,PROD,awslxappC-p*,appC2
applicationC2,QA,awslxappC-q*,appC1

0 Karma

sundareshr
Legend

Change the last segment of the query for the second dropdown to this

| eval value="host=".hostCode." source=".sourceCode 
0 Karma

alan20854
Path Finder

For the dropdown or the panel? I already did that for the dropdown

0 Karma

sundareshr
Legend

Can you open the query in your panel in search and share that and names of fields you're looking for in the panel. Also, what is the name of the field in your index (not CSV) that has values for sourceCode (app1 app2 etc?). You need to make sure the field names are correct. host and source are the default fields.

0 Karma

alan20854
Path Finder

So this is my search string for my panel: $host_src$ $srch$
When I open it in search after selecting a specific application and environment, I only get kwalxapptq010 *
When the textbox is empty, like what it was for this example, $srch$ defaults to *. Thus, it seems like the sourceCode is left out of the query.

The fields I am looking for are just host = .. source = .. and $srch$
The name of the field in my index that has values for sourceCode is source (based on the events retrieved from the query).

0 Karma

alan20854
Path Finder

So this is my search string for the panel: $host_src$ $srch$
I am looking for host = .. source = .. $srch$

When I don't type anything into the textbox, it $srch$ defaults to an asterisk
This is the query for when I select one specific application and its environment: kwalxapptq010 *

For the sourceCode values, for most of them, they are just , but for a couple of them, like the one I tested for the search query above, they are *applicationName
Thus, the query seems to be lacking the sourceCode

0 Karma

sundareshr
Legend

Do you have a separate dropdown for source? If you do, add a search query to the source dropdown that take the $env$ to filter its list. If host and source are in the same drop down, try concatenating the values in the query for host.

0 Karma

alan20854
Path Finder

Hi sundareshr,

I am not using another dropdown for source.

This query retrieves the source value:
| inputlookup lookup.csv | search (application="$app1$" AND hostCode="$env$")
but I don't know how to "save" that value and search for it.

Currently, I am trying to use the eval command to create a new variable. This is what I have:
| inputlookup lookup.csv | eval src = search (application="$app1$" AND hostCode="$env$")| search host = $env$ source = $src$ $srch$
but it says that there is an error in the search command, and that Comparator '=' has an invalid term on the right hand side.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...