topic Re: Merging data from 2 different sourcetypes in Splunk Search

Merging data from 2 different sourcetypes

neerajs_81 — Tue, 12 Apr 2022 07:14:56 GMT

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-different/m-p/493244, 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 ?

Re: Merging data from 2 different sourcetypes

gcusello — Tue, 12 Apr 2022 07:18:18 GMT

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

Re: Merging data from 2 different sourcetypes

neerajs_81 — Tue, 12 Apr 2022 07:50:17 GMT

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 )

Re: Merging data from 2 different sourcetypes

gcusello — Tue, 12 Apr 2022 08:02:10 GMT

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