How to search and filter emails of the same subject where "Support@email.com" is the sender?

[leonjxtan]

Hi my use case is to search for only email chains that are replied (attended) by Support team.
I have managed to extract data like below table:

Subject      |  From
AAAA         |  Client1@abc.com
AAAA         |  Support@email.com
BBBB         |  Client1@abc.com
CCCC         |  Client2@abc.com
CCCC         |  Support@email.com
CCCC         |  business@email.com

The search criteria I wanted to achieve is to filter by all subjects that "Support@email.com" exists in From field at least once.

So the expected returned results should be:

AAAA | Client1@abc.com
AAAA | Support@email.com
CCCC | Client2@abc.com
CCCC | Support@email.com
CCCC | business@email.com

The email chain BBBB will not show up in search result because this email chain is not replied by "Support@email.com"

Any idea please?

[leonjxtan · Accepted solution]

Thanks for all help. I did it with multiplevalue in the end, as below.
The performance of multiplevalue is not great but acceptable so far.

sourcetype=SupportEmails |rex field=Subject "^\s*(?:(?:(?:[Rr][Ee][^a-zA-Z])|(?:[Ff][Ww][Dd]?.?)):?\s*)*(?<Sub>.*)" 
|fields Sub, SentBy 
| mvcombine delim="~" SentBy 
| eval SupportReplyCnt=mvcount(mvfilter(match(SentBy, "Support@email.com")))
| where SupportReplyCnt>0
| eval TotalReplyCnt=mvcount(SentBy) 
| eval Requestor=mvindex(SentBy,0)

[woodcock]

You should click Accept on this answer to close the question.

[woodcock]

Like this:

... | stats values(From) AS From BY Subject | search From="Support@email.com"

[lguinn2]

I can't fully test this, but you might try:

yoursearchhere
| eventstats values(From) as senders by Subject
| search senders="Support@email.com"
| fields - senders
| sort Subject _time
| table Subject From _time

[pradeepkumarg]

Might not be the most elegant solution but I was able to quickly come up with something like below

....| eval flag=if(From="Support@email.com",1,0) | eventstats sum(flag) as flag2 by Subject | search flag2 > 0 | fields Subject From

If you want to understand how this works, I am just counting the number of occurrences of Support@email.com for each subject. if it has 0, it will filter out