Merging data from 2 different sourcetypes

[neerajs_81]

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:  [email protected]

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 ?

[gcusello · Accepted solution]

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]

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 )

[gcusello]

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