Dashboard Inpus will not dynamically update with field values to select from

[lbrhyne]

Ok... I have search far and wide and I'm unable to get any Input to populate with the Department field value. I'm pretty sure it is probaly something simple, but I just can't see the darn thing! Any help would be appreciated!

<form theme="dark">
  <label>Employee Retention</label>
  <fieldset submitButton="false">
    <input type="dropdown" token="dept" searchWhenChanged="true">
      <label>Department</label>
      <fieldForLabel>Department</fieldForLabel>
      <fieldForValue>Department</fieldForValue>
      <choice value="*">All</choice>
      <prefix>Department="</prefix>
      <suffix>"</suffix>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <search>
          <query>index=identities sourcetype="ADP:Vantage" (NOT ee_status="EEStatus")
| dedup email
| eval ih=substr(hire_date,1) | eval ih=strptime(ih,"%m/%d/%Y") | eval iaDiff=round((NOW()-ih)/86400,0) | eval iaDiffM=round(((NOW()-ih)/86400)*.03285,0)
| eval it=substr(term_date,1) | eval it=strptime(it,"%m/%d/%Y") | eval itDiff=round((it-ih)/86400,0) | eval itDiffM=round(((it-ih)/86400)*.03285,0) | eval itEndDate = if (ee_status=="A" OR ee_status=="L","",term_date)
| eval DaysofEmp = if (ee_status=="A" OR ee_status=="L",iaDiff,itDiff) | eval MthsofEmp = if (ee_status=="A" OR ee_status=="L",iaDiffM,itDiffM)
| eval fullname = (toString(ad_fname) + " " + toString(ad_last_name))
| convert timeformat="%m/%d/%Y" ctime(_time) AS date
| eval Month=strftime(_time,"%m") | eval Year=strftime(_time,"%Y") 
| rename ee_status as "HR Employment Status" fullname as "Full Name", department as Department DaysofEmp as "Days of Employment" hire_date as "Start Date" itEndDate as "End Date" MthsofEmp as "Months of Employment" date as Date
| stats count("HR Employment Status") as "Emp Status Totals"  by Department  "HR Employment Status"  Year Month
| search $dept$</query>
        </search>
        <option name="count">100</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <format type="number" field="Total Employees">
          <option name="precision">0</option>
          <option name="useThousandSeparators">false</option>
        </format>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <table>
        <search>

[niketn · Accepted solution]

@lbrhyne you can use the Dynamic Options in Dropdown to have Department values populate from search query. Ideally you should have a lookup file/kv store for populating/keeping track of all available departments rather than running search on your index data.

    <input type="dropdown" token="tokDepartment">
      <label>Department</label>
      <choice value="*">All</choice>
      <fieldForLabel>department</fieldForLabel>
      <fieldForValue>department</fieldForValue>
      <search>
        <query>index=identities sourcetype="ADP:Vantage" (ee_status!="EEStatus") department=*
| stats count by department</query>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </search>
      <default>*</default>
    </input>

Then in your search query as per Splunk search optimization tips you should filter Departments while pulling the data from index itself i.e something like following.

index=identities sourcetype="ADP:Vantage" (ee_status!="EEStatus") department="$tokDepartment$"
<yourRemainingSearch>

PS: You should also know that there is difference between (NOT ee_status="EEStatus") and (ee_status!="EEStatus"). Refer to Documentation: https://docs.splunk.com/Documentation/Splunk/latest/Search/NOTexpressions

[lbrhyne]

Thank you @niketnilay! That worked perfectly! Also, thanks for the tip on the KV store. I'm new to Splunk and always looking to better my skills. I will certainly look into building out a KV store as suggested.

Here is my end results!

<form theme="dark">
  <label>Employee Retention</label>
  <fieldset submitButton="false">
    <input type="multiselect" token="dept" searchWhenChanged="true">
      <label>Department</label>
      <fieldForLabel>department</fieldForLabel>
      <fieldForValue>department</fieldForValue>
      <choice value="*">All</choice>
      <search>
        <query>index=identities sourcetype="ADP:Vantage" (ee_status!="EEStatus") $dept$
 | stats count by department</query>
      </search>
      <initialValue>*</initialValue>
      <delimiter> OR </delimiter>
      <default>*</default>
      <valuePrefix>department="</valuePrefix>
      <valueSuffix>"</valueSuffix>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <search>
          <query>index=identities sourcetype="ADP:Vantage" (ee_status!="EEStatus") $dept$
          | dedup email
| eval ih=substr(hire_date,1) | eval ih=strptime(ih,"%m/%d/%Y") | eval iaDiff=round((NOW()-ih)/86400,0) | eval iaDiffM=round(((NOW()-ih)/86400)*.03285,0)
| eval it=substr(term_date,1) | eval it=strptime(it,"%m/%d/%Y") | eval itDiff=round((it-ih)/86400,0) | eval itDiffM=round(((it-ih)/86400)*.03285,0) | eval itEndDate = if (ee_status=="A" OR ee_status=="L","",term_date)
| eval DaysofEmp = if (ee_status=="A" OR ee_status=="L",iaDiff,itDiff) | eval MthsofEmp = if (ee_status=="A" OR ee_status=="L",iaDiffM,itDiffM)
| eval fullname = (toString(ad_fname) + " " + toString(ad_last_name))
| convert timeformat="%m/%d/%Y" ctime(_time) AS date
| eval Month=strftime(_time,"%m") | eval Year=strftime(_time,"%Y") 
| rename ee_status as "HR Employment Status" fullname as "Full Name", department as Department DaysofEmp as "Days of Employment" hire_date as "Start Date" itEndDate as "End Date" MthsofEmp as "Months of Employment" date as Date
| stats count("HR Employment Status") as "Emp Status Totals"  by Department  "HR Employment Status"  Year Month
</query>
        </search>
        <option name="count">100</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <format type="number" field="Total Employees">
          <option name="precision">0</option>
          <option name="useThousandSeparators">false</option>
        </format>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <table>

[niketn]

@lbrhyne you should not be using $dept$ token within your multiselect search query.

 <input type="multiselect" token="dept" searchWhenChanged="true">
   <label>Department</label>
   <fieldForLabel>department</fieldForLabel>
   <fieldForValue>department</fieldForValue>
   <choice value="*">All</choice>
   <search>
     <query>index=identities sourcetype="ADP:Vantage" (ee_status!="EEStatus") $dept$
     ....

$dept$ above is incorrect. You are using the token within the input for the same token.

[lbrhyne]

Thank you @niketnilay! Corrected below:

<form theme="dark">
  <label>Employee Retention</label>
  <fieldset submitButton="false">
    <input type="multiselect" token="dept" searchWhenChanged="true">
      <label>Department</label>
      <fieldForLabel>department</fieldForLabel>
      <fieldForValue>department</fieldForValue>
      <choice value="*">All</choice>
      <search>
        <query>index=identities sourcetype="ADP:Vantage" (ee_status!="EEStatus") department="*"