topic Re: Drop Down Menu problem in Dashboards & Visualizations

Drop Down Menu problem

mbasharat — Sun, 15 Apr 2018 03:13:55 GMT

HI,

Thanks in-advance for answering this question.

I have two drop down menus.

Drop Down 1 has Project Acronyms as ProjectA, ProjectB etc
Drop Down 2 has Hosts associated with selected Drop Down 1 Project.

Drop down 2 populates after drop down 1 when selected as it should when I select them individually by project BUT they do not when I use Static filed All="*" for both that is actually supposed to show me all hosts in all projects OR all hosts in a selected project and results accordingly. Instead of showing me All hosts within that project, it is giving results of all hosts in all indexes which I do not want.

I have tried several other posts but did not help. May be I missed something to understand.

Can someone assist please?

Re: Drop Down Menu problem

p_gurav — Sun, 15 Apr 2018 04:49:44 GMT

Can you share search your using for this drop downs?

Re: Drop Down Menu problem

Sukisen1981 — Sun, 15 Apr 2018 08:17:34 GMT

Hi,
I have lately sen this issue with many users. You are probably modifying your filter token queries and search queries and saving the dashboard. Refresh the dashboard 2-3 times, log out and log back in, your changes should get affected.
I am also interested in the search query in your tokens, it should be something like this
Drop down 1

index="abc" "blah blah blah"| dedup Project | table Project

your field for label and field for value should both have Project and you have probably set static option as something like Name ALL , value *
Now, you need to pass your token from drop down 1 into the search query for drop down 2 token, spmething like

index="abc" Project="$field1$" | dedup host

you will set field for label and field for value as host in this token, field1 is your token from the first drop down.

Am i correct in the way you are going so far?

Re: Drop Down Menu problem

niketn — Sun, 15 Apr 2018 08:42:03 GMT

@mbasharat, this seems to be a duplicate of your previous question, https://answers.splunk.com/answers/639024/splunk-drop-down-menu.html#answer-641390.

If it is not, can you please share the simple xml code for the two dropdown that are not working as expected?

Cascading Form Input is a regular use case in Splunk and Splunk Dashboard Examples app has this example.

Re: Drop Down Menu problem

mbasharat — Sun, 15 Apr 2018 13:48:16 GMT

It was a duplicate so I am posting it here and deleted the other one. Appreciate everyone's assistance here.

The queries I am using with in the dynamic option is actually two lookup tables.

Drop down 1:
| inputlookup project_info.csv
| fields project
| dedup project

Drop down 2:
| inputlookup project_servers.csv
| search project=$project$
| fields Host
| dedup Host

This queries are also slow including the drop downs as well as in the last snippet for Top LogIn Failure Hosts first of all but does produce the listing I need BUT problem is same above, the All="*" is outputs all hosts in indexes instead of just the ones with in a selected project from drop down 1 of All projects from drop down one. I will need faster queries as well pls. code below and rest of the panels follow.

enter code here

Sample

<input type="dropdown" token="project">
  <label>Select Project</label>
  <fieldForLabel>Project</fieldForLabel>
  <fieldForValue>Project</fieldForValue>
  <search>
    <query>| inputlookup project_servers_simple.csv | fields Project | dedup Project</query>
    <earliest>-24h@h</earliest>
    <latest>now</latest>
  </search>
  <choice value="*">All</choice>
  <default>*</default>

</input>
<input type="dropdown" token="projectserver">
  <label>Select Server</label>
  <fieldForLabel>Host</fieldForLabel>
  <fieldForValue>Host</fieldForValue>
  <search>
    <query>| inputlookup boundary_servers_simple.csv | search Project="$project$" | fields Host | dedup Host</query>
    <earliest>-24h@h</earliest>
    <latest>now</latest>
  </search>
  <choice value="*">All</choice>
  <default>*</default>

</input>
<input type="time" token="time" searchWhenChanged="true">
  <label>Time Picker</label>
  <default>
    <earliest>-24h@h</earliest>
    <latest>now</latest>
  </default>
</input>



<panel>
  <title>Top 10 LogIn Failures Hosts</title>
  <chart>
    <search>
      <query>index=wineventlog sourcetype="WinEventLog:Security" host="$projectserver$"

| search EventCode=4624 host=*
| top host

-24h@h
now

pie
none

Re: Drop Down Menu problem

mbasharat — Sun, 15 Apr 2018 13:53:49 GMT

thanks again as well. the sourcecode and queries posted above.

Re: Drop Down Menu problem

niketn — Sun, 15 Apr 2018 19:16:58 GMT

@mbasharat, so following are two of your concerns:

1) All Hosts (*) in the dropdown should search for Hosts specific to the Project/s selected, not all the hosts present in the index.
2) Query performance (possibly for the the main search (do you also need for inputlookup?)

Following are the queries you should try:

1) Query for Select Project dropdown (remains the same):

| inputlookup project_servers_simple.csv | fields Project | dedup Project

2) Query for Select Server dropdown (where condition in the inputlookup command):

| inputlookup boundary_servers_simple.csv where Project="$project$" | fields Host | dedup Host

3) Query for Top 10 LogIn Failures Hosts. Filter events upfront in the base search. Your existing query gets all events codes and then filters required Event Codes.

index=wineventlog sourcetype="WinEventLog:Security" EventCode=4624     [| inputlookup boundary_servers_simple.csv where Project="$project$" AND Host="$projectserver$" 
| fields Host 
| dedup Host 
| rename Host as host]
| top host showperc=f

Please try out and confirm. If the Final Top 10 search still takes longer than expected to complete, there might be couple of other ways required

1) If you are on 6.6 or higher, you can pass on the list of comma separated hosts by coding the <change> Event Handler for second lookup Select Project from boundary_servers_simple.csv lookup and use host IN ("$projectserver$")
2) Based on the minimum time window required for your Top 10 search you can create Summary Index for Various Event Codes so that your search query runs on summary index which will be faster than search on regular index.

Following is a run anywhere example. Since the data is cooked up to match expected output the search filter on the Top 10 Login failures by host query is applied after mock data is generated. The cooked up data has 6 hosts, however, the results are displayed based on Hosts returned in the Selected Server dropdown.

Following is the Run anywhere Dashboard Code based on the code/details provided:

<form>
  <label>Cascaded Dropdown Menu</label>
  <fieldset submitButton="false">
    <input type="dropdown" token="project">
      <label>Select Project</label>
      <fieldForLabel>Project</fieldForLabel>
      <fieldForValue>Project</fieldForValue>
      <search>
        <query>| inputlookup project_servers_simple.csv | fields Project | dedup Project</query>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </search>
      <choice value="*">All</choice>
      <default>*</default>
    </input>
    <input type="dropdown" token="projectserver">
      <label>Select Server</label>
      <fieldForLabel>Host</fieldForLabel>
      <fieldForValue>Host</fieldForValue>
      <search>
        <query>| inputlookup boundary_servers_simple.csv where Project="$project$" 
| fields Host
| dedup Host</query>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </search>
      <choice value="*">All</choice>
      <default>*</default>
    </input>
    <input type="time" token="time" searchWhenChanged="true">
      <label>Time Picker</label>
      <default>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <title>Top 3 Log In Failures Hosts</title>
      <chart>
        <search>
          <query>|  makeresults
|  eval data="host=host1,count=5;host=host2,count=3;host=host3,count=1;host=host4,count=10;host=host5,count=3;host=host6,count=1;host=host1,count=2;host=host3,count=3;host=host1,count=1;host=host5,count=1;host=host1,count=3;host=host2,count=1"
|  makemv data  delim=";" 
|  mvexpand data
|  rename data as _raw
|  KV
| search 
    [| inputlookup boundary_servers_simple.csv where Project="$project$" AND Host="$projectserver$" 
    | fields Host 
    | dedup Host 
    | rename Host as host]
| top 3 host</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.chart">pie</option>
        <option name="charting.drilldown">none</option>
        <option name="refresh.display">progressbar</option>
      </chart>
    </panel>
  </row>
</form>

Re: Drop Down Menu problem

mbasharat — Sun, 15 Apr 2018 21:23:10 GMT

Hi niketnilay,

The issue with ALL remains. I have even tried your sample above and did adjustments as needed but still, ALL does not work. Niether for Project NOR for Project Servers. I can not use servers manually within the code as you suggested because I have them in thousands and I have to use lookup. Didn't attempt to do TOP piece because the ALL piece does not work. What to do next? Any manual adjustments in the source code to be precise pls?

Re: Drop Down Menu problem

niketn — Sun, 15 Apr 2018 23:28:49 GMT

Can you try the following query with $project$ as one of your projects. See whether you get only the hosts for that specific project or not? If you notice I have Hostset to *

 index=wineventlog sourcetype="WinEventLog:Security" EventCode=4624     [| inputlookup boundary_servers_simple.csv where Project="$project$" AND Host="*" 
 | fields Host 
 | dedup Host 
 | rename Host as host]
 | top host showperc=f

Are you saying the output of run anywhere example is also not as per your expectation? What is the issue with that?
The suggested query changes for your dashboard are before run anywhere example and there is no manual code, so can you please explain what do you mean by use servers manually within code?

Re: Drop Down Menu problem

mbasharat — Mon, 16 Apr 2018 02:37:42 GMT

Hi nikenilay,

Good news, this new query worked with the Host="*" tweak.
Bad news, I also used this with slight adjustment for statistical table panel and its taking a very long time! How to tweak it for that pls.

Re: Drop Down Menu problem

mbasharat — Mon, 16 Apr 2018 02:39:34 GMT

one correction, actually for both pie chart for top host and stats table panel both are taking very long.

Re: Drop Down Menu problem

niketn — Mon, 16 Apr 2018 05:08:52 GMT

@mbasharat Host="*" was for you to test. It should be Host="$projectserver$" once you have tested.

If you are using same/Similar query in Pie Chart and Table you can use Post Processing. Can you share the query for Table?

Also Which version of Splunk are you on? Are you on Splunk 6.6 or higher?

Re: Drop Down Menu problem

mbasharat — Tue, 29 Sep 2020 19:06:18 GMT

We have Splunk 6.6.5 and 6.6.6 in different environments.

I actually meant to say Host="$projectserver$", this is what happens at half past 12 midnight! 🙂 while typing.

Stats table query is:

index=wineventlog sourcetype="WinEventLog:Security" EventCode=4624 [| inputlookup boundary_servers_simple.csv WHERE Boundary="$boundary$" AND Host="$projectserver$" | fields Host| dedup Host| rename Host as host]
| stats count by host, Account_Name, EventCode, EventCodeDescription, _time
| fieldformat _time=strftime('_time', "%m/%d/%Y %T")

Re: Drop Down Menu problem

niketn — Tue, 29 Sep 2020 19:02:37 GMT

Oh I am glad that means we are close. Only issue is performance of the actual query.

As stated in one of previous comments, the following command can be avoided as it is already being run in the dropdown for Select Server

[| inputlookup boundary_servers_simple.csv WHERE Boundary="$boundary$" AND Host="$projectserver$" | fields Host| dedup Host| rename Host as host]
Instead of re-running the query we can pass on the specific host names. In Splunk 6.6 IN() atrribute can be used i.e. host IN ("host1","host2","host3"), but 6.5 will need a query would be like host="host1" OR host="host2" OR host="host3"

Let me post updated answer with <change> event handler for Select Server so that above query can be sent.

However, if that does not improve performance it would imply that your query really has to search through a lot of events and only possibility to speed up the query would be to use Summary Indexing. I see that you are performing stats by _time as well. Is there a reason why you are not defining span like bin span=1h _time?

Re: Drop Down Menu problem

niketn — Mon, 16 Apr 2018 16:33:43 GMT

Following is updated Run Anywhere Example. It has following two changes.
1) The Select Server dropdown sends host="host1" or host IN ("host1","host2") depending on option selected (which means each time Project Changes Select Server dropdown is reset).

2) The query for stats table is used as a base search for post processing and the same results are used in the Pie Chart with Top command. Which means data is pulled from index only once.

<form>
  <label>Cascaded Dropdown</label>
  <fieldset submitButton="false">
    <input type="dropdown" token="project" searchWhenChanged="true">
      <label>Select Project</label>
      <fieldForLabel>Project</fieldForLabel>
      <fieldForValue>Project</fieldForValue>
      <search>
        <query>| inputlookup project_servers_simple.csv | fields Project | dedup Project</query>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </search>
      <choice value="*">All</choice>
      <default>*</default>
      <change>
        <unset token="form.projectserver"></unset>
        <unset token="tokSelectedServers"></unset>
      </change>
    </input>
    <input type="dropdown" token="projectserver" searchWhenChanged="true">
      <label>Select Server</label>
      <fieldForLabel>label</fieldForLabel>
      <fieldForValue>host</fieldForValue>
      <search>
        <query>| inputlookup boundary_servers_simple.csv where Project="$project$" 
| fields Host
| dedup Host
| rename Host as host
| mvcombine delim="," host 
| nomv host
| eval label="All"
| append [| inputlookup boundary_servers_simple.csv where Project="$project$" 
| fields Host
| dedup Host
| rename Host as host
| eval label=host]</query>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </search>
      <change>
        <condition label="All">
          <set token="tokSelectedServers">host IN ($value$)</set>
        </condition>
        <condition>
          <set token="tokSelectedServers">host=$value$</set>
        </condition>
      </change>
    </input>
    <input type="time" token="time" searchWhenChanged="true">
      <label>Time Picker</label>
      <default>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <title>Top 3 Log In Failures Hosts</title>
      <chart>
        <search base="baseStatsSearch">
          <query>stats sum(count) as count by host
 | sort - count
 | top 3 host</query>
        </search>
        <option name="charting.chart">pie</option>
        <option name="charting.drilldown">none</option>
        <option name="refresh.display">progressbar</option>
      </chart>
    </panel>
    <panel>
      <table>
        <search id="baseStatsSearch">
          <query>| makeresults 
| eval data="host=host1,count=5,Account_Name=ABC,EventCode=4624,EventCodeDescription=Test;host=host2,count=3,Account_Name=ABC,EventCode=4624,EventCodeDescription=Test;host=host3,count=1,Account_Name=ABC,EventCode=4624,EventCodeDescription=Test;host=host4,count=10,Account_Name=ABC,EventCode=4624,EventCodeDescription=Test;host=host5,count=3,Account_Name=ABC,EventCode=4624,EventCodeDescription=Test;host=host6,count=1,Account_Name=ABC,EventCode=4624,EventCodeDescription=Test;host=host1,count=2,Account_Name=DEF,EventCode=4624,EventCodeDescription=Test;host=host3,count=3,Account_Name=HIJ,EventCode=4624,EventCodeDescription=Test;host=host1,count=1,Account_Name=ABC,EventCode=4624,EventCodeDescription=Test;host=host5,count=1,Account_Name=ABC,EventCode=4624,EventCodeDescription=Test;host=host1,count=3,Account_Name=DEF,EventCode=4624,EventCodeDescription=Test;host=host2,count=1,Account_Name=DEF,EventCode=4624,EventCodeDescription=Test" 
| makemv data delim=";" 
| mvexpand data 
| rename data as _raw 
| KV 
| search $tokSelectedServers$
| bin _time span=1h 
| stats count by host, Account_Name, EventCode, EventCodeDescription, _time</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
        </search>
        <option name="refresh.display">progressbar</option>
      </table>
    </panel>
  </row>
</form>

Please try out and confirm!

Re: Drop Down Menu problem

mbasharat — Tue, 17 Apr 2018 00:32:55 GMT

Thanks nikenilay,

I got a little confused actually because I have thousands of hosts and can't put each in lets say | makerresults piece for instance.

Here is my complete source code for one of the dashboards, pls guide me what is up with this to make it faster and post processing. It runs perfectly fine, just is slow. Adjust as you suggest it should be.

<form>

  <label>Sample</label>
  <fieldset autoRun="true" submitButton="false">
    <input type="dropdown" token="boundary">
      <label>Select a Boundary</label>
      <fieldForLabel>Boundary</fieldForLabel>
      <fieldForValue>Boundary</fieldForValue>
      <search>
        <query>| inputlookup boundary_servers_simple.csv
| fields Boundary
| dedup Boundary</query>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </search>
      <choice value="*">All</choice>
      <default>*</default>

</input>
    <input type="dropdown" token="server" searchWhenChanged="true">
      <label>Host in Boundary</label>
      <fieldForLabel>FQDN</fieldForLabel>
      <fieldForValue>FQDN</fieldForValue>
      <search>
        <query>| inputlookup boundary_servers_simple.csv where Boundary="$boundary$"
| fields FQDN
| dedup FQDN
| sort by FQDN Asc</query>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </search>
      <choice value="*">All</choice>
      <default>*</default>
    </input>

<input type="dropdown" token="eventcode" searchWhenChanged="true">
      <label>Event Code</label>
      <fieldForLabel>EventCode</fieldForLabel>
      <fieldForValue>EventCode</fieldForValue>
      <search>
        <query>| inputlookup Windows2008andAbove_EventCode_AllSources.csv
| fields EventCode
| dedup EventCode
| sort EventCode Asc</query>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </search>
      <default>4624</default>
    </input>

<input type="dropdown" token="description" searchWhenChanged="true">
      <label>EventType</label>
      <fieldForLabel>EventCodeDescription</fieldForLabel>
      <fieldForValue>EventCodeDescription</fieldForValue>
      <search>
        <query>| inputlookup Windows2008andAbove_EventCode_AllSources.csv
| where EventCode="$eventcode$"
| fields EventCodeDescription</query>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </search>
      <default>An account was successfully logged on</default>
    </input>

<input type="time" token="time" searchWhenChanged="true">
      <label>Time Picker</label>
      <default>
        <earliest>-7d@h</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>

<row>
    <panel>
      <title>Results for Boundary=($boundary$), Server=($server$)</title>
      <table>
        <title>Event Code ($eventcode$) - $description$</title>
        <search>
          <query>index=wineventlog sourcetype="WinEventLog:Security" EventCode="$eventcode$" Account_Name!="*$" Account_Name!="SYSTEM" Account_Name!="LOCAL ACCOUNT" Account_Name!="HealthMail*" Account_Name!="Symantec*" Account_Name!="SMP*" Account_Name!="*POOL" Account_Name!="APPPOOL*" Account_Name!="NETWORK SERVICE" [| inputlookup boundary_servers_simple.csv WHERE Boundary="$boundary$" AND FQDN="$server$" | fields FQDN | dedup FQDN | rename FQDN as host | eval Account_Name=mvindex(Account_Name, 1)]
| eval Account_Domain=mvindex(Account_Domain, 1)
| stats count by host, Account_Domain, Account_Name, EventCode, EventCodeDescription, _time
| where NOT LIKE(LOWER(Account_Name),"-")
| fieldformat _time=strftime('_time', "%m/%d/%Y %T")
| dedup Account_Name</query>
          <earliest>$time.earliest$</earliest>
          <latest>$time.latest$</latest>
        </search>
        <option name="drilldown">row</option>
      </table>
    </panel>
  </row>

<row>
    <panel>
      <chart>
        <title>Top LogIn Failures Destinations @ $boundary$ Boundary</title>
        <search>
          <query>index=wineventlog sourcetype="WinEventLog:Security" EventCode=4625 [| inputlookup boundary_servers_simple.csv WHERE Boundary="$boundary$" AND FQDN="$server$" | fields FQDN | dedup FQDN | rename FQDN as host]
| stats count by host
| top host showperc=f</query>
          <earliest>$time.earliest$</earliest>
          <latest>$time.latest$</latest>
        </search>
        <option name="charting.chart">pie</option>
        <option name="charting.chart.sliceCollapsingThreshold">0</option>
        <option name="charting.drilldown">none</option>
        <option name="height">302</option>
      </chart>
    </panel>
    <panel>

<chart>
        <title>Top LogIn Failures by User @ $boundary$ Boundary</title>
        <search>
          <query>index=wineventlog sourcetype="WinEventLog:Security" EventCode=4625 Account_Name!="*$" Account_Name!="SYSTEM" Account_Name!="LOCAL ACCOUNT" Account_Name!="HealthMail*" Account_Name!="Symantec*" Account_Name!="SMP*" Account_Name!="*POOL" Account_Name!="APPPOOL*" Account_Name!="NETWORK SERVICE" Account_Name!="GAdminAD" Account_Name!="GadminAD" Account_Name!="LOCAL SERVICE" [| inputlookup boundary_servers_simple.csv WHERE Boundary="$boundary$" AND FQDN="$server$" | fields FQDN | dedup FQDN | rename FQDN as host]
| where NOT LIKE(LOWER(Account_Name),"%svc")
| top Account_Name showperc=f</query>
          <earliest>$time.earliest$</earliest>
          <latest>$time.latest$</latest>
        </search>
        <option name="charting.chart">pie</option>
        <option name="charting.chart.sliceCollapsingThreshold">0</option>
        <option name="charting.drilldown">none</option>
        <option name="height">299</option>
      </chart>
    </panel>   

<panel>
      <chart>
        <title>Top LogIn Failures Explicit by User @ $boundary$ Boundary</title>
        <search>
          <query>index=wineventlog sourcetype="WinEventLog:Security" EventCode=4648 Account_Name!="*$" Account_Name!="SYSTEM" Account_Name!="LOCAL ACCOUNT" Account_Name!="HealthMail*" Account_Name!="Symantec*" Account_Name!="SMP*" Account_Name!="*POOL" Account_Name!="APPPOOL*" Account_Name!="NETWORK SERVICE" Account_Name!="GAdminAD" Account_Name!="GadminAD" Account_Name!="LOCAL SERVICE" [| inputlookup boundary_servers_simple.csv WHERE Boundary="$boundary$" AND FQDN="$server$" | fields FQDN | dedup FQDN | rename FQDN as host]
| where NOT LIKE(LOWER(Account_Name),"%svc")
| top Account_Name showperc=f</query>
          <earliest>$time.earliest$</earliest>
          <latest>$time.latest$</latest>
        </search>
        <option name="charting.chart">pie</option>
        <option name="charting.chart.sliceCollapsingThreshold">0</option>
        <option name="charting.drilldown">none</option>
        <option name="height">300</option>
      </chart>
    </panel>

<panel>
      <chart>
       <title>Top LogIn Failures SVC Account/s @ $boundary$ Boundary</title>
        <search>
          <query>index=wineventlog sourcetype="WinEventLog:Security" EventCode=4625 Account_Name!="*$" Account_Name!="SYSTEM" Account_Name!="LOCAL ACCOUNT" Account_Name!="HealthMail*" Account_Name!="Symantec*" Account_Name!="SMP*" Account_Name!="*POOL" Account_Name!="APPPOOL*" Account_Name!="NETWORK SERVICE" Account_Name!="GAdminAD" Account_Name!="GadminAD" Account_Name!="LOCAL SERVICE" [| inputlookup boundary_servers_simple.csv WHERE Boundary="$boundary$" AND FQDN="$server$" | fields FQDN | dedup FQDN | rename FQDN as host]
| where LIKE(LOWER(Account_Name),"%svc")
| top Account_Name showperc=f</query>
          <earliest>$time.earliest$</earliest>
          <latest>$time.latest$</latest>
        </search>
        <option name="charting.chart">pie</option>
        <option name="charting.chart.sliceCollapsingThreshold">0</option>
        <option name="charting.drilldown">none</option>
        <option name="height">300</option>
      </chart>
    </panel>
  </row>
</form>