Splunk Search

Merging data from 2 different sourcetypes

neerajs_81
Builder

Hi All,  

I have two sourcetypes in the same index, however the fields names are different but the value is same for the Email address of a user .   But yet when i do a coalesce or use |where clause,  splunk shows "No results found"
 For example:
Sourcetype s1 contains email field while s2 contains user_email field.
Both fields have same value:  john_smith@domain.com

 

index=xx (sourcetype=s1 OR sourcetype=s2) (email=* OR user_email=*)
| eval user_id = coalesce(email, user_email)

OR 

| index=xx (sourcetype=s1 OR sourcetype=s2) 
| where email=user_email

 


Result:  No results found.

I am following whatever is  mentioned in https://community.splunk.com/t5/Splunk-Search/merge-two-sourcetypes-that-have-the-same-data-but-diff...  but yet in my case it shows 0 Result matches.

Any idea what can be the issue ?  Is the @ sign or "." (dot) in the email id creating a problem ?

Labels (2)
Tags (2)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @neerajs_81,

at first check if there something else in your fields (e.g. spaces).

Then try this:

index=xx ((sourcetype=s1 email=*) OR (sourcetype=s2 user_email=*))
| eval user_id=coalesce(email,user_email)

In addition, put speciat attention if the email field cound have null values, becuase in this case the coalesce doesn't work.

In this case, try something like this:

index=xx ((sourcetype=s1 email=*) OR (sourcetype=s2 user_email=*))
| eval user_id=if(email="",user_email,email)

Ciao.

Giuseppe

View solution in original post

gcusello
SplunkTrust
SplunkTrust

Hi @neerajs_81,

at first check if there something else in your fields (e.g. spaces).

Then try this:

index=xx ((sourcetype=s1 email=*) OR (sourcetype=s2 user_email=*))
| eval user_id=coalesce(email,user_email)

In addition, put speciat attention if the email field cound have null values, becuase in this case the coalesce doesn't work.

In this case, try something like this:

index=xx ((sourcetype=s1 email=*) OR (sourcetype=s2 user_email=*))
| eval user_id=if(email="",user_email,email)

Ciao.

Giuseppe

neerajs_81
Builder

Hi Giuseppe, Thank you for responding. 
Verified there are no blank spaces.   Even if there were null values for some user records,   the other query i tried that has |where clause ,  as in |where email = user_email , should work or show some matches   but even that one shows  "No results found".

Out of curiosity even if i run just this one liner as shown below ,  it displays  fields (under Interesting Fields) only from 1st sourcetype s1.  Nothing from S2 is visible.  I guess that's why both coalesce and |where clause are not working for me. 

 

index=xx (sourcetype=s1 OR sourcetype=s2 )

 

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @neerajs_81,

I don't think that this is the issue.

please try to extract the second file (user_email) using the rex command, maybe there's a problem in field extraction.

Ciao.

Giuseppe

Get Updates on the Splunk Community!

.conf25 Registration is OPEN!

Ready. Set. Splunk! Your favorite Splunk user event is back and better than ever. Get ready for more technical ...

Detecting Cross-Channel Fraud with Splunk

This article is the final installment in our three-part series exploring fraud detection techniques using ...

Splunk at Cisco Live 2025: Learning, Innovation, and a Little Bit of Mr. Brightside

Pack your bags (and maybe your dancing shoes)—Cisco Live is heading to San Diego, June 8–12, 2025, and Splunk ...