Splunk Search
Highlighted

Why Doesn't Coalesce work in an If/Case Statement?

Path Finder

I am trying to write a search that if the field= Email then perform a coalese, but if the field isn't Email- just put in the field- below is what I have written. It seems like coalesce doesn't work in if or case statements. I also tried to accomplishing this with isNull and it also failed.

| eval $fieldToMatch$= if($fieldToMatch$==Email, coalesce(Email,NotifyAddress), $fieldToMatch$)

Thanks for the help!

(essentially i am doing a join on a field and the field could either be email, an ID, etc. but if the field to match on is email- the field NotifyAddress might also contain some emails)

0 Karma
Highlighted

Re: Why Doesn't Coalesce work in an If/Case Statement?

SplunkTrust
SplunkTrust

I think you might be confused about what coalesce does. It selects the field that is not null. If both the Email and NotifyAddress fields might contain emails then you probably want to merge them using Email.NotifyAddress.

---
If this reply helps you, an upvote would be appreciated.
0 Karma
Highlighted

Re: Why Doesn't Coalesce work in an If/Case Statement?

Path Finder

No I want to use the functionality of coalesce- so if Email is null- then pull in the value from Notify Address. I don't care about Notify Address if Email has a value. Do you know why this isn't working with if/case?

0 Karma
Highlighted

Re: Why Doesn't Coalesce work in an If/Case Statement?

SplunkTrust
SplunkTrust

What is $fieldToMatch$ here? a token from some form input?

0 Karma
Highlighted

Re: Why Doesn't Coalesce work in an If/Case Statement?

Path Finder

yes it is a token that could either be the field Email, ID,Name. So if the field to match on is Email- I need to check for any additional emails to match on that could be in the field NotifyAddress.

0 Karma
Highlighted

Re: Why Doesn't Coalesce work in an If/Case Statement?

Super Champion

why not try something like this:

| eval $fieldToMatch$= case($fieldToMatch$==Email AND isnotnull(Email), Email, $fieldToMatch$==Email AND isnull(Email),NotifyAddress, 1=1,$fieldToMatch$)
0 Karma
Highlighted

Re: Why Doesn't Coalesce work in an If/Case Statement?

SplunkTrust
SplunkTrust

Give this a try

| eval "$fieldToMatch$"= if("$fieldToMatch$"=="Email", coalesce(Email,NotifyAddress), '$fieldToMatch$')

View solution in original post

Highlighted

Re: Why Doesn't Coalesce work in an If/Case Statement?

Path Finder

yes this worked thank you! But can you explain to me what the difference between " and ' is and when to use them?

Because the goal of my search is to match two data sources on a field (Email for example). The one data source is where I was using this functionality above with Email and Notify address, but the other data source- the field to match on may also be named Email so whoever wrote the search referred to that field as "Email".

Essentially I need to understand why you use " vs. ' vs. just the field name to make sure this doesn't mess up the rest of my search. Thanks for the help!

0 Karma
Highlighted

Re: Why Doesn't Coalesce work in an If/Case Statement?

SplunkTrust
SplunkTrust

If the field names contains special characters, you would enclose them in single quotes in eval/where expressions (e.g. ..| where <<expression>> or ..|eval fieldname=<<expression>>). For eval, you can use double quotes on the left side of = sign (first one after field name), and must use single quotes on right side of it.

Highlighted

Re: Why Doesn't Coalesce work in an If/Case Statement?

Path Finder

okay that makes sense- if I am pulling from two different sources who have fields with the same name- how do I distinguish them?

Cause the Email used in the above function should all be from one source, but the other source used also has the field Email in it.

0 Karma