Searching two different records with one common field

[vnguyen46]

Hi,
I have two different records:
[2019-07-22 10:32:03.819930 -0500] rprt s=2tuw17mc0b cmd=env_rcpt value=ken@gmail.com
[2019-07-22 10:32:03.816879 -0500] rprt s=2tuw17mc0b m=1 cmd=env_from value=support@yahoo.com

How can I search for records that displays as?
S From To

2tuw17mc0b support@yahoo.com ken@gmail.com

s has the same value in both records
both records have field name: value, but one is "from" and the other is "to".

Thanks,

[vnguyen46 · Accepted solution]

Hi Woodcock - thank you for help. The following lines work great:

index="email" (other search base)
| eval time=strftime(_time, "%+") 
| eval from = if(cmd=="env_from",value,null())
| eval to = if(cmd=="env_rcpt",value,null())
| stats first(time) as Date, values(from) as From, values(to) as To

Thanks again.

[woodcock]

So you have created your own answer, right? If so, click Accept to close the question and be sure to UpVote any answers that helped you.

[woodcock]

Like this:

index=<You should always specify an index> AND sourcetype=<And sourcetype too>
| eval {cmd} = value
| stats values(env_from) AS From values(env_rcpt) AS To BY S

[aholzer]

<your base search>
| eval from = if(cmd=="env_from",value,null())
| eval to = if(cmd=="env_rcpt",value,null())
| stats values(from) as From values(to) as To by s

This assumes that the FROM field will always have "env_from" value under the CMD field, and the TO field will always have "env_rcpt" in the CMD field.

Hope this helps

[vnguyen46]

Thanks for the query and I am almost there. So far, it looks like this:
index=proofpoint s=*
| eval from = if(cmd=="env_from",value,null())
| eval to = if(cmd=="env_rcpt",value,null())
| stats values(from) as From values(to) as To by s

There are two more fields _time and subject showing in return from the query above and that I need to add to the result table. Sample record reads:
Jul 22 10:32:04 MTAMXIPLP002 filter_instance1[145122]: rprt s=2tuw17mc0b m=1 x=2tuw17mc0b-1 mod=mail cmd=msg module=pdr rule=pass action=continue attachments=1 rcpts=1 routes=default_inbound,uth_tmc_edu_recipient size=39071 guid=QuKJZcb8D_D9rfLfgOf02Nw1xMPS6b0Y hdr_mid= qid=x6MFW4Gb105384 hops-ip=192.161.148.9 subject="Hello, I was unable to login to MD Coder 10 from m..." spamscore=0 virusname= duration=0.308 elapsed=0.517

Thanks,