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
Legend

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
Legend

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
Legend

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
Legend

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!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...