Knowledge Management

Why does coalesce command work in one calculated field search but not another?

_jgpm_
Communicator

Hi,

First time poster.
I've combed the Splunk>Answers for something related but I can't find out why coalesce works in one search and not another.

I want to use stats to report Latitude Longitude from multiple different logs and there are some logs that have different log structures. I have used field extractions for all the various Lat&Lon combinations. In one saved search, I can use a calculated field which basically is eval Lat=coalesce(Lat1,Lat2,Lat3,Lat4) and corresponding one for Lon.

In SavedSearch1, I use a simple query of Event1=* OR Event2=* | stats Avg(Lat) Avg(Long) and it works the way it's supposed to. Event1 has Lat1 messages and Event2 has Lat2 messages and Lat ends up being a new field named "Lat" in every found event with either Lat1 or Lat2.

In SavedSearch2, I use the same query structure. The only difference in setup is that there is an intermediate calc field step: Lat4=exact(LatA/2) which shows up in the Verbose field. Lat=coalesce(Lat1,Lat2,Lat3,Lat4) does not work at all.

Tried:

  1. rearranging fields order in the coalesce function (nope)
  2. making all permissions to global (nope)
  3. double checking all syntax (nope)
  4. using eval statement in the search query to check manually (works)
  5. removing streamstats middle step in case there was an issue (nope)
  6. changing search query to explicitly search for LatA, then Lat4 (nope)

I hoped that writing this out would give me an idea and it has: combine the 1st calculated field into the coalesce function.

Turns out combining the calculated fields into one step works. I guess I found a race condition that wasn't easy to find in the documentation.

Cheers!
JPGM

0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

Calculated fields cannot use other calculated fields. You'd need to include the /2 in the coalesce.

http://docs.splunk.com/Documentation/Splunk/6.5.0/Knowledge/Searchtimeoperationssequence#Calculated_...

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

Calculated fields cannot use other calculated fields. You'd need to include the /2 in the coalesce.

http://docs.splunk.com/Documentation/Splunk/6.5.0/Knowledge/Searchtimeoperationssequence#Calculated_...

_jgpm_
Communicator

Thanks @Martin_Mueller. The reason I didn't find that is because it doesn't exist for 6.4.3 - which I'm running. I tried it out at the bottom of my post and it did work. Thanks for finding the documentation!

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Yeah, that's a new page... feel free to submit feedback at the bottom of the docs page to ask for it to be backported.

From what I can see, its content applies 1:1 to previous versions.

0 Karma

somesoni2
Revered Legend

Can you share the query that is not working and indicate what is the field name for lat and long for each data source?

0 Karma

_jgpm_
Communicator

It's all good. I figured it out. It is a search-time-operation-sequence issue. Basically, calculated fields cannot be based off of other calculated fields at search time.

0 Karma
Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...