Splunk Search

Merging data from 2 different sourcetypes

neerajs_81
Contributor

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
Legend

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
Legend

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
Contributor

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
Legend

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!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...