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?
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)
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)
You should click Accept
on this answer to close the question.
Like this:
... | stats values(From) AS From BY Subject | search From="Support@email.com"
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
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