Splunk Search

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

katzr
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
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Give this a try

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

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Give this a try

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

katzr
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

somesoni2
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.

katzr
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

somesoni2
SplunkTrust
SplunkTrust

There is no way to differentiate just based on field name as fieldnames can be same between different sources. If your expression/logic needs to be different for different sources (though applied on same field name), then you'd need to include source identifier field (field/fields that can uniquely identify source) into your expressions/logic. e.g. For same original problem, with different expression for sourceA and sourceB (assuming field source is uniq identifier), so set the value of field Email differently, you can do like this:

| eval "$fieldToMatch$"= case("$fieldToMatch$"=="Email" AND source="sourceA", coalesce(Email,NotifyAddress), "$fieldToMatch$"=="Email" AND source="sourceB", coalesce(Email,Receipiers), , 1=1, '$fieldToMatch$')
0 Karma

cmerriman
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

richgalloway
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, Karma would be appreciated.
0 Karma

katzr
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

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

katzr
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
Get Updates on the Splunk Community!

Streamline Data Ingestion With Deployment Server Essentials

REGISTER NOW!Every day the list of sources Admins are responsible for gets bigger and bigger, often making the ...

Remediate Threats Faster and Simplify Investigations With Splunk Enterprise Security ...

REGISTER NOW!Join us for a Tech Talk around our latest release of Splunk Enterprise Security 7.2! We’ll walk ...

Introduction to Splunk AI

WATCH NOWHow are you using AI in Splunk? Whether you see AI as a threat or opportunity, AI is here to stay. ...