Splunk Search

Searching two different records with one common field

vnguyen46
Contributor

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,

0 Karma
1 Solution

vnguyen46
Contributor

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.

View solution in original post

0 Karma

vnguyen46
Contributor

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.

View solution in original post

0 Karma

woodcock
Esteemed Legend

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
Esteemed Legend

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
0 Karma

aholzer
Motivator
<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

0 Karma

vnguyen46
Contributor

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,

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!