Use subsearch with stats command to dynamically search for list of events

[perrinj2]

|inputlookup test_results |where build == [|inputlookup test|stats first(build)]

I'm trying to do something like the above. ie find the latest build number in the csv lookup file that gets created as part of an earlier base search and then find all events with that build number.
I get the following "Error in 'where' command: Typechecking failed. The '==' operator received different types."
The build field is a number in the csv file (ie no quotes) and looks ok when I just run the stats command. Same with the search ie when I hardcode the build number it return the correct results.
So I think it's something to do with how subsearches return values but don't know how to deal with this.
Thanks in advance for any help

[Richfez · Accepted solution]

The way a subsearch works is it returns results just like a regular search. Beinga subsearch, they run first and their results get inserted into the main search exactly where they are. So let's look at your example, tearing it down into a couple of pieces:

|inputlookup test_results |where build == [|inputlookup test|stats first(build)]

^^ This doesn't work.

My guess is that if you did the two simple things - as you hint at that you do - these both work. This: |inputlookup test|stats first(build) returns some value for build (I'm lazy and I'll pretend it returns the value 'BuildValue').

If you then search |inputlookup test_results |where build=BuildValue it would work. Though I'm not sure why you are using where - the where in Splunk is a trickier and easier-to-get-wrong version of search so let's try that instead - |inputlookup test_results | search build=BuildValue. That should work (assuming the lookup has the field build).

What you WANT is for the subsearch to return a correctly formatted piece of a search for you to use. The easiest way to do this would be to trim down your stats results to JUST the one field you want, right? Then ,by default, the way the subsearch would return it would be as key-value pairs. So let's rephrase the subsearch just a hair to make sure we have the results in the right format:

|inputlookup test|stats first(build) AS build | fields build

If you run that on its own, it should return a field "build" with a value of "BuildValue". So now, this

|inputlookup test_results | search [inputlookup test|stats first(build) AS build | fields build]

Should end up being a search that works.

One great tip is that you can inspect the job and find out what the search looks like. There's a trick to finding the ACTUAL search -
Click "Job", then "Inspect Job".
In the "Search job inspector" near the top click "search.log"
Press Control-F (e.g. "search this page with your browser") and search for "Expanded filtering search"

That should be the actual search - after subsearches were calculated - that Splunk ran.

A good resource is to search Splunk Docs for subsearches - the first few hits on this search are all really good. Subsearches get tricky, there's all sorts of formatting and reformatting you can do to the output to make it behave slightly differently. But you shouldn't need that for this.

Happy Splunking, and hope this helps!
-Rich

[perrinj2]

Thanks Rich. Very helpful and informative answer. It resolved my issue and helped conceptually. The tip about the Search job inspector will help with other issues.

[HiroshiSatoh]

Try this!

|inputlookup test_results where  [|inputlookup test|stats first(build) as build]