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 (3)
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!

Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...