Passing a field value from one search command in the pipeline to another

[_smp_]

I have a search which produces a list of fields in an output table, including a user ID. I want to take the at ID, search another index, and add additional output columns to the table. Functionally it behaves like this:

| makeresults
| eval requesting_user="david"
| appendcols
    [search index=admon sAMAccountName=$requesting_user$ earliest=0 latest=now
    | stats last(mail) as mail, last(givenName) as givenName, last(cn) as cn]

In the end, I want a single row with the requesting_user, mail, givenName and cn fields. But I'm not quite sure how to join these two searches together into a single row of output. I've experimented with appendcols, appendpipe, append, and map. Only map seems to be able to read the requesting_user token, but seems to throw away the requesting_user field.

The rest of the commands I've tried don't seem to be able to read the token or something else is going on, because I only get null values for those fields. When I execute the appendcols command substituting the token for the actual user name, it retrieves the values I want. 

Can anyone help me understand how include the fields from the bottom search into the output table of the top search?

[richgalloway · Accepted solution]

With the exception of map, the commands you tried (as well as join and multisearch) execute the subquery independent of the main search.  The subquery has no awareness of the fields in the main search and there is no way to pass arguments to the subquery.

Subsearches are similar, but they run first and make their results available to the main search.  So, your problem may be solved by swapping the order of operations.

index=admon [ | makeresults | eval requesting_user="david" ] sAMAccountName=requesting_user earliest=0 latest=now
| stats last(mail) as mail, last(givenName) as givenName, last(cn) as cn