Splunk Search

How to change search query dynamically based on input

sangs8788
Communicator

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

Tags (1)
0 Karma
1 Solution

niketn
Legend

@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

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

View solution in original post

0 Karma

niketn
Legend

@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

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

gcusello
SplunkTrust
SplunkTrust

Hi

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

0 Karma

sangs8788
Communicator

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.

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

sangs8788
Communicator

Thanks. Let me try that out.

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

sangs8788
Communicator

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.

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