topic How to change search query dynamically based on input in Splunk Search

How to change search query dynamically based on input

sangs8788 — Tue, 29 Sep 2020 14:51:33 GMT

Hi All,

I have 2 queries for each dataservice,

Query 1

index=db_connect source = "db2_*.log" earliest=-1d|dedup TBSP_NAME, DB_NAME | convert timeformat="%Y-%m-%d %H:%M:%S" ctime(_time) AS Timestamp | stats sum(TBSP_SIZE_BYTES) as "TBSP_SIZE_BYTES" by Timestamp , DB_NAME | stats first(Timestamp) as "as of", first(TBSP_SIZE_BYTES) as "LATEST_TBSP_SIZE_BYTES" by DB_NAME | eval TBSP_SIZE_GB=round(LATEST_TBSP_SIZE_BYTES/(1024 * 1024 * 1024),2) |table DB_NAME, TBSP_SIZE_GB, "as of"| rename DB_NAME as Database |rename TBSP_SIZE_GB as "Database Size (GB)"

Query 2

index=db_connect source = "mssql_*_dbgrowth.log" | dedup TBSP_NAME, DB_NAME |convert timeformat="%Y-%m-%d %H:%M:%S" ctime(_time) AS Timestamp
|stats sum(DATA_KB) as "DATA_KB" by Timestamp , DB_NAME

|stats first(Timestamp) as "as of", first(DATA_KB) as "LATEST_DATA_KB" by DB_NAME
|eval DATA_GB=round(LATEST_DATA_KB/(1024 * 1024 ),2)
|table DB_NAME, DATA_GB, "as of"| rename DB_NAME as Database |rename DATA_GB as "Database Size (GB)"

Is it possible to build search query based on a input string ? Say i have a input which will get value as "DB2" or MSSQL".

If my input value is DB2,
then Query 1 must run
If my input value is MSSQL
then Query 2 must run

Could you please let me know.

Thanks

Re: How to change search query dynamically based on input

gcusello — Thu, 13 Jul 2017 07:07:10 GMT

try something like this

<form>
  <label>test</label>
  <fieldset submitButton="false">
    <input type="radio" token="db">
      <label>Choice DB</label>
      <choice value="source = db2_.log earliest=-1d">DB2</choice>
      <choice value="source = mssql__dbgrowth.log | rename DATA_KB AS TBSP_SIZE_BYTES">MSSQL</choice>
      <default>source = db2_.log earliest=-1d</default>
    </input>
  </fieldset>
  <row>
    <panel>
      <event>
        <search>
          <query>
              index=db_connect 
              $db$
             | dedup TBSP_NAME, DB_NAME 
             | convert timeformat="%Y-%m-%d %H:%M:%S" ctime(_time) AS Timestamp 
             | stats sum(TBSP_SIZE_BYTES) as "TBSP_SIZE_BYTES" by Timestamp , DB_NAME 
             | stats first(Timestamp) as "as of", first(TBSP_SIZE_BYTES) as "LATEST_TBSP_SIZE_BYTES" by DB_NAME 
             | eval DATA_GB=round(LATEST_TBSP_SIZE_BYTES/(1024*1024),2) 
             | table DB_NAME, TBSP_SIZE_BYTES, "as of"
             | rename DB_NAME as Database TBSP_SIZE_GB as "Database Size (GB)"
          </query>
          <earliest>$earliest$</earliest>
          <latest>$latest$</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="list.drilldown">full</option>
        <option name="list.wrap">1</option>
        <option name="maxLines">5</option>
        <option name="raw.drilldown">full</option>
        <option name="rowNumbers">0</option>
        <option name="table.drilldown">all</option>
        <option name="table.sortDirection">asc</option>
        <option name="table.wrap">1</option>
        <option name="type">list</option>
      </event>
    </panel>
  </row>
</form>

Bye.
Giuseppe

Re: How to change search query dynamically based on input

sangs8788 — Tue, 29 Sep 2020 14:51:36 GMT

This wouldnt work because Query2 has a small change in dedup compared to Query 1.

Query 1 - dedup TBSP_NAME, DB_NAME
Query2 - dedup DB_NAME

And also the Query 1 TBSP_SIZE_BYTES is in bytes and in Query 2 DATA_KB is in kb.

Re: How to change search query dynamically based on input

gcusello — Thu, 13 Jul 2017 08:05:39 GMT

In your choices you can put also other, modify in this way your choices

<form>
   <label>test</label>
   <fieldset submitButton="false">
     <input type="radio" token="db">
       <label>Choice DB</label>
       <choice value="source = db2_.log earliest=-1d | dedup TBSP_NAME, DB_NAME">DB2</choice>
       <choice value="source = mssql__dbgrowth.log | rename DATA_KB AS TBSP_SIZE_BYTES | dedup DB_NAME">MSSQL</choice>
       <default>source = db2_.log earliest=-1d dedup TBSP_NAME, DB_NAME</default>
     </input>
   </fieldset>
   <row>
     <panel>
       <event>
         <search>
           <query>
               index=db_connect 
               $db$
              | convert timeformat="%Y-%m-%d %H:%M:%S" ctime(_time) AS Timestamp 
              | stats sum(TBSP_SIZE_BYTES) as "TBSP_SIZE_BYTES" by Timestamp , DB_NAME 
              | stats first(Timestamp) as "as of", first(TBSP_SIZE_BYTES) as "LATEST_TBSP_SIZE_BYTES" by DB_NAME 
              | eval DATA_GB=round(LATEST_TBSP_SIZE_BYTES/(1024*1024),2) 
              | table DB_NAME, TBSP_SIZE_BYTES, "as of"
              | rename DB_NAME as Database TBSP_SIZE_GB as "Database Size (GB)"
           </query>
           <earliest>$earliest$</earliest>
           <latest>$latest$</latest>
           <sampleRatio>1</sampleRatio>
         </search>
         <option name="count">20</option>
         <option name="list.drilldown">full</option>
         <option name="list.wrap">1</option>
         <option name="maxLines">5</option>
         <option name="raw.drilldown">full</option>
         <option name="rowNumbers">0</option>
         <option name="table.drilldown">all</option>
         <option name="table.sortDirection">asc</option>
         <option name="table.wrap">1</option>
         <option name="type">list</option>
       </event>
     </panel>
   </row>
 </form>

Bye.
Giuseppe

Re: How to change search query dynamically based on input

sangs8788 — Thu, 13 Jul 2017 08:49:25 GMT

Thanks. Let me try that out.

Re: How to change search query dynamically based on input

niketn — Thu, 13 Jul 2017 09:08:32 GMT

@sangs8788, you can code the <change> event of the input to set whatever tokens you need

    <fieldset submitButton="false">
      <input type="radio" token="db">
        <label>Choice DB</label>
        <choice value="db2">DB2</choice>
        <choice value="mssql">MSSQL</choice>
        <default>db2</default>
        <change>
           <condition value="db2">
               <set token="queryString">
                   index=db_connect source = "db2_.log"
| dedup TBSP_NAME, DB_NAME 
| convert timeformat="%Y-%m-%d %H:%M:%S" ctime(_time) AS Timestamp 
| stats sum(TBSP_SIZE_BYTES) as "TBSP_SIZE_BYTES" by Timestamp , DB_NAME 
| stats first(Timestamp) as "as of", first(TBSP_SIZE_BYTES) as "LATEST_TBSP_SIZE_BYTES" by DB_NAME 
| eval TBSP_SIZE_GB=round(LATEST_TBSP_SIZE_BYTES/(1024 1024 * 1024),2) 
| table DB_NAME, TBSP_SIZE_GB, "as of"
| rename DB_NAME as Database 
| rename TBSP_SIZE_GB as "Database Size (GB)"
               </set>
           </condition>
           <condition value="mssql">
               <set token="queryString">
                     index=db_connect source = "mssql__dbgrowth.log" 
| dedup TBSP_NAME, DB_NAME 
| convert timeformat="%Y-%m-%d %H:%M:%S" ctime(_time) AS Timestamp 
| stats sum(DATA_KB) as "DATA_KB" by Timestamp , DB_NAME 
| stats first(Timestamp) as "as of", first(DATA_KB) as "LATEST_DATA_KB" by DB_NAME 
| eval DATA_GB=round(LATEST_DATA_KB/(1024 1024 ),2) 
| table DB_NAME, DATA_GB, "as of"
| rename DB_NAME as Database 
| rename DATA_GB as "Database Size (GB)"
               </set>
           </condition>
        </change>
      </input>
    </fieldset>
    <row>
      <panel>
        <table>
          <search>
            <query>$queryString$</query>
            <earliest>-1d@d</earliest>
            <latest>now</latest>
          </search>
        ...
        ...

Ideally you should create a macro for search with various parameters (so that you can handle situations like different DBs, size, time range, fields etc.) and then set only the parameters through the change event. I have just used complete search for a example of change event. You can set several tokens using set tag (PS: eval tag is also available which will allow you to set tokens based on some conditions.) Splunk documentation for reference: https://docs.splunk.com/Documentation/Splunk/latest/Viz/EventHandlerReference#Event_handler_element

Re: How to change search query dynamically based on input

gcusello — Thu, 13 Jul 2017 09:19:50 GMT

If this answer satiesfies your request, please accept it.
Bye and good luck.
Giuseppe

Re: How to change search query dynamically based on input

sangs8788 — Thu, 13 Jul 2017 09:58:25 GMT

This does work though it cannot work in my scenario since i have other dataservice with different queries. So it is not possible to have a common query for all. Below posted query seems to work for my usecase. Thanks for your time.