To use subsearch result in outersearch for > and < comparisons.

[asingla]

I have a subsearch which is returning two fields and I am succesfully able to use that in the outer search for the equality comparisons.

index="summary" compName | eval maxTime=_time  
| search  
   [  
     search index="summary" source="LastestDseStats"  
     | stats max(_time) as maxTime by compName  
     | fields maxTime, dseName | format  
 ] 

This query finds the latest event for all the different compName. Here I am comparing compName and _time (aka maxTime) field for equality comparison.

But I want to find all the events but not latest. For that I need to have a < condition on _time (aka maxTime) and equality condition on compName. Is there any way for that?

I was able to achieve <= condition be naming the field as maxTime< in my subsearch.

stats max(_time) as maxTime< by compName  

To me this looked sneaky but it works. But I don't know how to get rid of the '=' sign.

[sideview · Accepted solution]

Unfortunately there's no way to tell the format command to use one operator with one field and a different operator with a second field.

The good news is that this is totally possible. You just have to do a little of the work manually.

If you have a field called 'search' in there, the format command backs off and doesn't stick on the fieldname= part. Instead it just dumps the value(s) of the search field out.

This is alluded to in the docs. http://docs.splunk.com/Documentation/Splunk/latest/User/HowSubsearchesWork#Change_the_format_of_subs... However it's not very clear what exactly it's trying to say.

So the answer is to take a different approach and glue together your search strings explicitly before passing them to format

index="summary" compName | eval maxTime=_time | search  
 [  
   search index="summary" source="LastestDseStats"  
   | stats max(_time) as maxTime by compName  
   | eval search="maxTime>" + maxTime + " dseName=" + dseName 
   | fields search 
   | format
]

and the search that you end up with will look like

index="summary" compName | eval maxTime=_time | search (maxTime>12312312 dseName=foo) OR (maxTime>14312321 dseName=bar)

[Shtark]

Thanks Ayn. One tweak required though. I needed to add a count to the return function, e.g.
| return 100000 $search |
otherwise it only returns the search field for one result.

[Ayn]

Use "| return $search" instead of "| fields ... | format". It will return the value of the search field, without the quotes.

[sideview]

D'oh! I see the same thing. When I run [| stats count | eval query="_time>10000000" | head 1 | fields query | format ], it quotes the entire "_time>100000" search term. I guess either they "fixed" an issue around quoted terms in subsearches, thus breaking this trick, or it's distantly possible that I was just wildly incorrect in the first place. I swear I've done this trick before but it's been so long.

[Shtark]

I used this method and the subsearch returned
( "_time<=1348231831 AND serial=660500011226000000000023" ) OR ...
which doesn't work because the '<=' is in the middle of a string. Eval'ing only the _time comparison as search results in
( "_time<=1348231831" AND serial="660500011226000000000023" ) OR ...
which still doesn't work. What I really need is
( _time<=1348231831 AND serial="660500011226000000000023" ) OR ...

Any ideas?

[asingla]

Great answer. I was aware of the fact about using search as a field name but still this didn't strike me.