Dashboards & Visualizations

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

Path Finder

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>
0 Karma
1 Solution

Legend

@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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

Legend

@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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

Path Finder

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>

Legend

@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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Path Finder

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="*"
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!