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

[_jgpm_]

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

[martin_mueller · Accepted solution]

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_]

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!

[martin_mueller]

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.

[somesoni2]

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

[_jgpm_]

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.