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 ?
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
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
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 )
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