How to trigger second search based on first search where condition

[ibob0304]

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.

[niketn · Accepted solution]

@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

[ibob0304]

Thanks Niket

[niketn]

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

[jkat54]

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.

[niketn]

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

[Luciana]

@niketn , 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?

[gjanders]

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

[ibob0304]

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

[nabeel652]

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?

[ibob0304]

@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

[nabeel652]

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

[niketn]

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

[nabeel652]

Thanks @niketnilay for the explanation.

[micahkemp]

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?

[ibob0304]

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