Add value from sub search into the result set of the main search

[Joerg]

I've a sub search on an SMTP log to get all TO and FROM values together with the status. Unfortunately TO and FROM are in one log entry and TO and STATUS in a different one. Common field is the TextID. 
Simplified the log structure looks like the following for a single TextID:

...
{"id":null,"log":{"text":"123A: to=<T@>, status=sent"}}
{"id":null,"log":{"text":"123A: to=<T@>, status=deferred"}}
{"id":null,"log":{"text":"123A: from=<F@> to=<T@> proto=ESMTP"}}
...

My current search:

index=A 
[ search index=A "to=<"
| rex field=log.text "(?<TextID>\w+).*from=<(?<FROM>.*)> to=<(?<TO>.*)> "
| dedup TextID
| return 1000000 $TextID
]
| rex field=log.text "(?<TextID>\w+).*to=<(?<TO>.*)>.*, status=(?<STATUS>.*\))"
| table TextID TO STATUS

My current result:

TextID	TO	STATUS
123A	To1	sent
123A	To1	deferred
234B	To2	sent
234B	To2	delayed
345C	To3	sent

How can I also print out the FROM which is only available in the sub search in the result set of the main search?
I already tried to resolve this with union, join, append, appendcols but was unable to get expected result. Expected result would be:

TextID	TO	STATUS	FROM
123A	To1	sent	From1
123A	To1	deferred	From1
234B	To2	sent	From2
234B	To2	delayed	From2
345C	To3	sent	From1

Thank you
Jörg

[ITWhisperer]

Try something like this

index=A 
| rex field=log.text "(?<TextID>\w+).*from=<(?<FROM>.*)> to=<(?<TO>.*)> "
| rex field=log.text "(?<TextID>\w+).*to=<(?<TO>.*)>.*, status=(?<STATUS>.*\))"
| stats values(STATUS) as STATUS values(TO) as TO values(FROM) as FROM by TextID

[Joerg]

Thanks @ITWhisperer unfortunately FROM is still empty.
I also have to admit that I missed to add a condition (proto=ESMTP) to my current search. This condition is essential for the sub search to return only specific entries:

index=A 
[ search index=A AND "to=<" AND "proto=ESMTP"
| rex field=log.text "(?<TextID>\w+).*from=<(?<FROM>.*)> to=<(?<TO>.*)> "
| dedup TextID
| return 1000000 $TextID
]
| rex field=log.text "(?<TextID>\w+).*to=<(?<TO>.*)>.*, status=(?<STATUS>.*\))"
| table TextID TO STATUS

 Unfortunately TextID, TO, FROM, proto are in one log entry (sub search) and TextID, TO, STATUS in a different one (main search). Common for both is the TextID, TO. 

[Joerg]

@ITWhisperer all of a sudden I got your example working and the FROM is now showing. No idea why it wasn't working the first time.  Sorry for the hassle.
But as I missed to give some important info (additional search criteria for the sub query) it also returns entries without
... "to=<" AND "proto=ESMTP"...

| rex field=log.text "(?<TextID>\w+).*from=<(?<FROM>.*)> to=<(?<TO>.*)> "

Can I somehow include additional search criteria as well similar to what I've in my sub search?

Thanks Jörg

[ITWhisperer · Accepted solution]

index=A 
| rex field=log.text "(?<TextID>\w+).*from=<(?<FROM>.*)> to=<(?<TO>.*)> proto=(?<PROTO>.*)\""
| rex field=log.text "(?<TextID>\w+).*to=<(?<TO>.*)>.*, status=(?<STATUS>.*\))"
| stats values(STATUS) as STATUS values(TO) as TO values(FROM) as FROM values(PROTO) as PROTO by TextID

[PickleRick]

You can do pretty much whatever you want in splunk 😉

If you want to restrict your results only to those that have proto=ESMTP, just extract that field, then add it as another stats to calculate and filter to include only those having ESMTP in proto field.

index=A 
| rex field=log.text "(?<TextID>\w+).*from=<(?<FROM>.*)> to=<(?<TO>.*) proto=(?<PROTO>.*)> "
| rex field=log.text "(?<TextID>\w+).*to=<(?<TO>.*)>.*, status=(?<STATUS>.*\))"
| stats values(STATUS) as STATUS values(TO) as TO values(FROM) as FROM values(PROTO) as PROTO by TextID
| search PROTO=ESMTP