Dashboards & Visualizations

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

lbrhyne
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

niketn
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

niketn
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!!!"

lbrhyne
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>

niketn
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

lbrhyne
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="*"
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...