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)
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
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?
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.
why not try something like this:
| eval $fieldToMatch$= case($fieldToMatch$==Email AND isnotnull(Email), Email, $fieldToMatch$==Email AND isnull(Email),NotifyAddress, 1=1,$fieldToMatch$)
Give this a try
| eval "$fieldToMatch$"= if("$fieldToMatch$"=="Email", coalesce(Email,NotifyAddress), '$fieldToMatch$')
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!
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.
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.