Dashboards & Visualizations

How to string two dropdown tokens to use in where clause?

elomotanpru
Path Finder

Updated the post since the error changed into "Error in 'where' command. The expression is malformed. Expected)"

My aim is to use two separate stings of tokens in my search to pass the following:

1) "Start of the Month" + "Year"   ($month1$ $year$)

2) "End of the Month" + "Year" ($month2$ $year$)

I was trying to combine the strings using <eval> within the <change> step but had no luck on finding a guide to implement it properly.

Would it be better just to add the <eval> within the search or would it be better to do it during the change to avoid any complications?

elomotanpru_0-1653660958409.png

 

 

 

<form theme="dark">
  <label>CSC/ERSC/PSI PAGING Report</label>
  <fieldset submitButton="true" autoRun="true">
    <input type="dropdown" token="lpar">
      <label>Select to View</label>
      <choice value="----">----</choice>
      <choice value="D7X0">D7X0</choice>
      <choice value="H7X0">H7X0</choice>
      <choice value="D1D0">D1D0</choice>
      <choice value="DAD0">DAD0</choice>
      <choice value="E1D0">E1D0</choice>
      <choice value="H1D0">H1D0</choice>
      <choice value="WSYS">WSYS</choice>
      <choice value="YSYS">YSYS</choice>
      <default>----</default>
    </input>
    <input type="dropdown" token="year">
      <label>Select Year</label>
      <choice value="----">----</choice>
      <choice value="2022">2022</choice>
      <default>----</default>
    </input>
    <input type="dropdown" token="month1">
      <label>Select Month</label>
      <choice value="****">****</choice>
      <choice value="01/01/">January</choice>
      <choice value="02/01/">February</choice>
      <choice value="03/01/">March</choice>
      <choice value="04/01/">April</choice>
      <choice value="05/01/">May</choice>
      <choice value="06/01/">June</choice>
      <choice value="07/01/">July</choice>
      <choice value="08/01/">August</choice>
      <choice value="09/01/">September</choice>
      <choice value="10/01/">Ocotber</choice>
      <choice value="11/01/">November</choice>
      <choice value="12/01/">December</choice>
      <default>****</default>
      <change>
        <condition label="****">
          <set token="month2">----</set>
        </condition>
        <condition label="January">
          <set token="month2">01/31/</set>
        </condition>
        <condition label="February">
          <set token="month2">02/29/</set>
        </condition>
        <condition label="March">
          <set token="month2">03/31/</set>
        </condition>
        <condition label="April">
          <set token="month2">04/30/</set>
        </condition>
        <condition label="May">
          <set token="month2">05/31/</set>
        </condition>
        <condition label="June">
          <set token="month2">06/30/</set>
        </condition>
        <condition label="July">
          <set token="month2">07/31/</set>
        </condition>
        <condition label="August">
          <set token="month2">08/31/</set>
        </condition>
        <condition label="September">
          <set token="month2">09/30/</set>
        </condition>
        <condition label="October">
          <set token="month2">10/31/</set>
        </condition>
        <condition label="November">
          <set token="month2">11/30/</set>
        </condition>
        <condition label="December">
          <set token="month2">12/31/</set>
        </condition>
      </change>
    </input>
  </fieldset>
  <row>
    <panel>
      <chart>
        <search>
          <query>index=mainframe-platform sourcetype="mainframe:mpage" 
MVS_SYSTEM_ID=$lpar$
| eval DATE=strftime(strptime(DATE,"%d%b%Y"),"%Y-%m-%d")
| eval _time=strptime(DATE." ","%Y-%m-%d")
| where _time &gt;= strptime("$month1$""$year$", "%m/%d/%Y") AND _time &lt;= strptime("$month2$""$year$", "%m/%d/%Y")
| chart sum(VIO_PAGING_SEC) as "$lpar$ Sum of VIO_PAGING_SEC" sum(SYSTEM_PAGEFAULTS_SEC) as "$lpar$ SYSTEM_PAGEFAULTS_SEC" sum(SWAP_PAGIN_SEC) as "$lpar$ SWAP_PAGIN_SEC" sum(LOCAL_PAGEFAULTS_SEC) as "$lpar$ LOCAL_PAGEFAULTS_SEC" over _time</query>
          <earliest>0</earliest>
          <latest></latest>
        </search>
        <option name="charting.chart">column</option>
        <option name="charting.drilldown">none</option>
      </chart>
    </panel>
  </row>
</form>

 

 

Would appreciate the help.

Labels (4)
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @elomotanpru,

the your search is probably waiting for the token "range.earliest" and "range.latest" that you can find in the earliest and latest tags.

Ciao.

Giuseppe

 

elomotanpru
Path Finder

Thanks for pointing that out. Was not able to change the Time Range back to Timepicker when I tried using the Time Input before. Updated the post, the error I am receiving now is "Error in 'where' command. The expression is malformed. Expected )."

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @elomotanpru,

yes the where condition isn't correct, please try this:

| where _time &gt;= strptime("$month1$$year$", "%m/%d/%Y") AND _time &lt;= strptime("$month2$$year$", "%m/%d/%Y")

Ciao.

Giuseppe

0 Karma

elomotanpru
Path Finder

The error changed to "Could not create search on this". Its seems that its has to be concatenate prior to search.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @elomotanpru,

ok try this

| eval date_start=strptime("$month1$$year$", "%m/%d/%Y"), date-end=strptime("$month2$$year$", "%m/%d/%Y")
| where _time &gt;= date_start AND _time&lt;=date_end

if you continue to have the error, try "search" instead "where".

Ciao.

Giuseppe

0 Karma

elomotanpru
Path Finder

Hi @gcusello,

Sadly its just a different version of the same error. Really tricky one this one. Will give you an update if I find a different solution.

Thanks for the ideas and teaching me the eval with date 😀

elomotanpru_0-1654003179842.png

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @elomotanpru,

try to modify your code not directly in the Source section, but accessing code by UI -- Edit Search of the panel.

In this way you don't have the problem of "&gt;", you can us ">".

Ciao.

Giuseppe

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