How to add a field from a subsearch to every event in a parent search?

[jwalthour]

I'm wanting to do something like this:

index=main sourcetype=access_combined
[ search index=myidx sourcetype=oncall name="oncall_group" earliest=-24h latest=-0m|head 1|rename email as email_to|return email_to ]
| table sourcetype source email_to

So, the results look like this

access_combined source1 abc@mydomain.com
access_combined source2 abc@mydomain.com
access_combined source3 abc@mydomain.com
access_combined source4 abc@mydomain.com
access_combined source5 abc@mydomain.com
access_combined source6 abc@mydomain.com
access_combined source7 abc@mydomain.com
access_combined source8 abc@mydomain.com
access_combined source9 abc@mydomain.com
access_combined source10 abc@mydomain.com

Basically, the email address gets appended to every event in search results. I've tried join, append, appendpipe, appendcols, everything I can think of. Nothing works as intended. What am I not understanding here?

[BMacher]

Hi, I would use eval

...
| eval email_to = [
search index=myidx sourcetype=oncall name="oncall_group" earliest=-24h latest=-0m
| head 1
| rename email as email_to
| return $email_to
]
...

The $ does the following (field has the value xy):

return field -> returns field="xy"
return $field -> returns xy

[lmonahan]

Awesome!  Did not know about return.

[somesoni2]

If you just want to add a column with same value as email_to (no filtering of main search), try this

 index=main sourcetype=access_combined | eval email_to= [ search index=myidx sourcetype=oncall name="oncall_group" earliest=-24h latest=-0m|head 1|eval email="\"".email."\""|return email ]
 | table sourcetype source email_to

If you want to filter the main search as well create a new field, try this

 index=main sourcetype=access_combined
 [ search index=myidx sourcetype=oncall name="oncall_group" earliest=-24h latest=-0m|head 1|rename email as email_to|return email_to ] | eval email_to= [ search index=myidx sourcetype=oncall name="oncall_group" earliest=-24h latest=-0m|head 1|eval email="\"".email."\""|return email ] 
 | table sourcetype source email_to

[jwalthour]

The SPL gives an error: "Error in 'eval' command: Fields cannot be assigned a boolean result. Instead, try if([bool expr], [expr], [expr])."

[jawaharas]

Below one works for me. 

| gentimes start=1 end=5
| eval 
[| makeresults
| eval foo="test"
| return foo]

[rjthibod]

Maybe not the best solution, but a solution nonetheless is to use filldown

index=main sourcetype=access_combined  | table sourcetype source | appendcols [ search index=myidx sourcetype=oncall name="oncall_group" earliest=-24h latest=-0m | head 1| table email | rename email as email_to ] | filldown email_to

[jwalthour]

I don't know but the email_to field never appears in the table.

[rjthibod]

Does the search below have a single result row with a column "email_to" or does it return no results?

index=myidx sourcetype=oncall name="oncall_group" earliest=-24h latest=-0m | head 1| table email | rename email as email_to