USAGE OF SPLUNK EVAL FUNCTION : COALESCE

USAGE OF SPLUNK EVAL FUNCTION : COALESCE

Coalesce is an eval function (Use the eval function to evaluate an expression, based on our events ). This function takes an arbitrary number of arguments and returns the first value that is not NULL.

We can use this function with the eval command and as a part of eval expressions.

Syntax :

| eval <field_name>=coalesce(<field1>,<field2>,……..)

Example :

index="abc" sourcetype="abc"
| table Message1,Message2
| eval Message=coalesce(Message1,Message2)
| dedup Message

Result :

Explanation :

• In the above query “abc” is the index and sourcetype name is “abc”.
• Using table command, we have taken two fields called Message1 and Message2.
• Then using eval command we create a new field called Message. With eval command we use one function coalesce. Using coalesce function we got one new field Message with value of Message1 and Message2.

Message1 field contains some value and the Message2 field contain some value. Coalesce function returns the value of that field which is first not null field. In the Message field the first 4 rows from the top we are getting the value for the Message1 field because the Message1 field is not null. But in the last row, we are getting the data for the Message2 field because in the last row Message1 field is null. This function is also used for the data-normalization process.

Example 2:
Part 1:

index="shantanu" sourcetype="col_csv"
| table Name NAME name
| eval New_Name=coalesce(Name,NAME,name)

Result:

Explanation:

• In the above query “shantanu” is the index and sourcetype name is “col_csv”.
• Using table command, we have taken three fields called Name, Name and
• Then using the eval command we create a new field called New_Name. With eval command we use one function coalesce. Using coalesce function we got one new field New_Name with values of Name, Name and name fields.

Here one can see that within the coalesce function seriality of fields is like Name,NAME and name. According to that only we are getting results in New_Name, I.e. In the first Name field We have Nibedan and Gopal, both listed in New_Name but Salim from NAME field is not listed in New_Name because within the function we have Name field first. That’s why it will first check at Name field and after Gopal it will find NULL then only it will jump into the next field where it will find Sarada instead of  Salim.

Part 2:
Now if you change the seriality of fields name within the coalesce function according to that result will also change take a look.

index="shantanu" sourcetype="col_csv"
| table Name NAME name
| eval New_Name=coalesce(name,NAME,Name)

Result:

Explanation:
If you notice carefully then you will find the differences between the two, we have just changed the seriality of the field names within the coalesce function it’s now name,NAME and Name. That’s why the result in New_Name is also changed now you have Salim instead of Gopal because according to the seriality it will first check NAME then it will jump into the Name field.

Now you can effectively utilize “coalesce” function with “eval” command to meet your requirement !!

Hope you are now comfortable in : Usage of Splunk EVAL Function : COALESCE

HAPPY SPLUNKING !!