How to write an efficient subsearch with or without coalesce?

[Glasses]

Need some advice writing a subsearch...

I have an index=email with two sourcetypes
sourcetype=MTA
sourcetype=MSG

both contain a field with a common value that ties the msg and mta logs together

for example
index=email sourcetype=MTA sm.qid
index=email sourcetype=MSG filter.qid

sm.qid = filter.qid for the same email session

I want to write an efficient search/subsearch that will correlate the two sourcetypes and also display multiple fields from each index.

I am at this point but wondering if I am going the right direction...

[search index=email sourcetype=MSG <some-search-criteria> | fields filter.id | rename filter.id as search] index=email sourcetype=MTA | table <some-fields>

This works partially but the sourcetypes MTA and MSG have different fields that I want to display together in the results...

For example sourcetype=MTA has fields: status,to,from,sm.qid
While sourcetype=MSG has fields:
messageID, subject, to, from, filter.qid

the "to" and "from" fields are slightly different and are formatted slightly different too...

I thought about coalesce but it is acting funny, I need to use ... | eval CommonID = coalesce('sm.qid','filter.qid') <<<< only single quotes work... and I am not sure the best way to use coalesce to grab all the fields from both sourcetype.

Any advice appreciated.
Thanks

[zacharychristen · Accepted solution]

I would recommend avoiding subsearches. They are really only good for certain circumstances and often negatively affect performance. Also, some special characters in field names can lead to issues during search time. It's recommended to change how these fields are being extracted using props and transforms.

For simplicity try this:

index=email (sourcetype=MSG OR sourcetype=MTA) 
| rename 'filter.qid' as filter_qid, 'sm.qid' as sm_qid
| eval qid=coalesce(filter_qid,sm_qid)
| stats count by qid

[Glasses]

for some reason that did not produce results for qid ....
but I follow your code, makes sense, not sure why its not working

[Glasses]

just had to lose the single quote in the rename... thanks

[nareshinsvu]

If possible try to rename both common fileds to qid before indexing and try below query? And then go from there?

index=email sourcetype=MSG OR sourcetype=MTA | stats values(*) as * by qid

[Glasses]

Thank you for the reply, unfortunately that is not an option for me... but very good idea.