Splunk Search

How to trigger second search based on first search where condition

ibob0304
Communicator

I have a dbquery alert which will trigger when first query has more than 250 records then second search will trigger using |map command.

| dbxquery connection=conn query="select * from db" 
|where records>=250
|map maxsearches=1 search="dbxquery select query"

So, I am looking for an equivalent query that works for regular log events. I tried subsearch but it didn't worked well.

index=* source=*  sourcetype=json 
| where count < 100 
| trigger second query when above condition agree 

All I am trying is output the second query output results based on first query search condition.

0 Karma
1 Solution

niketnilay
Legend

@ibob0304, you can use the map command to set the token in the main search only if count is >250. This way the second search will fail if the token is not set. Following is a run any where example.

PS: Instead of | makeresults | eval testCount=300 you will have your first query that returns count. I have used this for testing so that you can change 300 to 50 to see the search fail.

| makeresults
| eval testCount=300
| eval tokenForSecondSearch=case(testCount>=250,"true")
| map search="search index=_internal sourcetype=splunkd log_level!=INFO| stats count by component| sort - count| head 10| eval tokenForSecondSearch=\"$tokenForSecondSearch$\"| fields - tokenForSecondSearch"

Refer to map command documentation: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Map

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

View solution in original post

niketnilay
Legend

@ibob0304, you can use the map command to set the token in the main search only if count is >250. This way the second search will fail if the token is not set. Following is a run any where example.

PS: Instead of | makeresults | eval testCount=300 you will have your first query that returns count. I have used this for testing so that you can change 300 to 50 to see the search fail.

| makeresults
| eval testCount=300
| eval tokenForSecondSearch=case(testCount>=250,"true")
| map search="search index=_internal sourcetype=splunkd log_level!=INFO| stats count by component| sort - count| head 10| eval tokenForSecondSearch=\"$tokenForSecondSearch$\"| fields - tokenForSecondSearch"

Refer to map command documentation: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Map

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

View solution in original post

ibob0304
Communicator

Thanks Niket

0 Karma

niketnilay
Legend

@ibob0304 anytime 🙂 I am glad you found it working 🙂

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

jkat54
SplunkTrust
SplunkTrust

As long as you are using count, index, source and sourcetype in the root search ONLY; you can take advantage of tstats instead of stats/timechart etc.

Like this:

 | tstats count where index=* AND sourcetype=json | search count > 100 

Or you could even use the metadata or eventstats commands.

niketnilay
Legend

@ibob0304, Following is a run anywhere dashboard where count from query 1 is used to set the second query to be run. Please try out and confirm. I have added a text box to adjust the count in query 1 for testing purpose.

<form>
  <label>Run second search based on number of results</label>
  <!-- First Search which returns count for where condition match-->
  <search>
    <query>| makeresults
    | eval resultCount="$tokCount$"
    | fields - _time
    </query>
    <done>
      <!-- Second query is set only first query returns count greater than 100-->
      <condition match="$result.resultCount$>100">
        <set token="tokSubQuery">index=_internal sourcetype=splunkd log_level!=INFO earliest="$tokTime.earliest$" latest="$tokTime.latest$"| stats count by component | sort - count | head 5</set>
      </condition>
      <!-- If the count is less than 100 token is unset to stop search and hide the same -->
      <condition>
        <unset token="tokSubQuery"></unset>
      </condition>
    </done>
  </search>
  <fieldset submitButton="false">
    <input type="time" token="tokTime" searchWhenChanged="true">
      <label></label>
      <default>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </default>
    </input>
    <!-- Text box input is for testing purpose to generate count > 100 and < 100 -->
    <input type="text" token="tokCount" searchWhenChanged="true">
      <label>Enter Limit Count for test</label>
      <default>200</default>
    </input>
  </fieldset>
  <row depends="$tokSubQuery$">
    <panel>
      <table>
        <search>
          <query>$tokSubQuery$</query>
        </search>
      </table>
    </panel>
  </row>
</form>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

Luciana
Explorer

@niketnilay , Good Afternoon! sorry for asking you , but I have an issue in my dashboard in triggering a second search if the count of result of my first search  will be  != 0 . 

I was passing by and I saw this post where you explain how to do that, but in fact,  in the solution... apparently once we set the token, the SECOND SEARCH will not run a second time if the FIRST SEARCH runs again.  Do you know How Can I get this around?

 

Tags (1)
0 Karma

gjanders
SplunkTrust
SplunkTrust

niketnilay is no longer with us so he will be unable to answer, you may wish to start a new thread as this thread is quite old...

Alerts for Splunk Admins https://splunkbase.splunk.com/app/3796/
Version Control for Splunk https://splunkbase.splunk.com/app/4355/

ibob0304
Communicator

Thanks Niket, I upvoted the answer. But, do you know if this can be done using ad- hoc search instead of dashboard ?

0 Karma

nabeel652
Builder

Awesome! great example explained in a great way.

Just a question, does the <done> tag follows a <query> tag? and the "result" in "$result.resultCount$" refers to the result of the recent query?

0 Karma

ibob0304
Communicator

@nabeel652, I see you deleted your prior comment, which is fine. But, I would like to let you know that if there is no response to the answer then the person who posted might waited for long time looking for an answer and gave up after some days. And I am looking for an adhoc search. If you know then let me know the answer and I will appreciate it or else dont use words like shame, and this is not stackoverflow

0 Karma

nabeel652
Builder

There was no point discussing this when I deleted my comments. Period

0 Karma

niketnilay
Legend

@nabeel652, <done> and <progress> are two of Search Event Handlers that allows access to default tokens $result.<fieldName>$ (only single value or first row value). Refer to the documentation for details.

The <done> and <query> tags should be inside <search>. But I dont think sequence is of importance. The <done> search event handler accesses fields from the query in the same search.

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

nabeel652
Builder

Thanks @niketnilay for the explanation.

0 Karma

micahkemp
Champion

In your example the timechart command only returns count and _time fields. Is this all you want to use to perform your secondary search?

map is probably not the best solution for your needs. Can you elaborate a bit regarding what your data looks like, and what type of data would indicate a need to find additional data?

0 Karma

ibob0304
Communicator

Sorry, changed the query. All I want is to show second query results based on first trigger condition

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!