Inputlookup append=true vs append [|inputlookup ] behavior difference

[elliotproebstel]

I saw a previous question dealing with this, but that question never got an accepted answer, and I think it was sufficiently complex that this distillation may highlight the issue more directly. I observed unexpected behavior when testing approaches using | inputlookup append=true ... vs | append [| inputlookup ... ]. Here are a series of screenshots documenting what I found.

I created two small test csv files: first_file.csv and second_file.csv. They each contain three fields: _time, row, and file_source. I tested this code first:

| inputlookup first_file.csv
| eval flag="this is from the first file" 
| append 
 [| inputlookup second_file.csv ]

This displayed what I expected:

Then I tested this code snippet:

| inputlookup first_file.csv
| eval flag="this is from the first file" 
| inputlookup append=true second_file.csv

Somehow, this causes the flag field to disappear, as you can see here:

Is this expected? I couldn't find anything in the docs to explain the difference in behavior. If it is expected, is there a flag or attribute I can set when using the | inputlookup append=true approach that would preserve fields?

[martin_mueller · Accepted solution]

This is a bug/feature of the search optimizer, in this particular case it's doing projection elimination - removing evals it thinks are unnecessary. To confirm, check the job inspector for optimizedSearch that only contains two inputlookups, but no eval.

The field will magically reappear if it's used later in the search, alternatively add | table * if you just want to see it now... or | noop search_optimization=false, or | eval foo = flag will also make it appear.
This is still happening in 7.0.2, make sure to file a support case to check if this is intentional behaviour or a bug.

http://docs.splunk.com/Documentation/Splunk/7.0.2/Search/Built-inoptimization#Projection_elimination

[elliotproebstel]

You're right! Thank you. It was causing me some confusion while trying to troubleshoot a more complex use case, but I can understand why it would be a "feature" here.

[somesoni2]

What version of Splunk are you testing it in? I don't see this behavior (flag field getting removed) in 6.2 and 6.3 instance that I can check right now. Also, what do you get when you run this

| gentimes start=-1
 | eval flag="this is from the first file" 
 | inputlookup append=true second_file.csv

[elliotproebstel]

I'm running Splunk 6.6.2.
Here's the output of that command:

endhuman    starttime   starthuman  endtime flag
Wed Feb 21 23:59:59 2018    1519189200  Wed Feb 21 00:00:00 2018    1519275599  this is from the first file

[somesoni2]

Nothing is getting added from yoru second_file.csv lookup?

[elliotproebstel]

Also, for what it's worth, our ops team upgraded everything from 6.6.2 to 6.6.5 last night, and I'm still seeing the same behavior.

[elliotproebstel]

Sorry, I was in a rush out the door last night and accidentally ran your suggested tests with outputlookup instead of inputlookup, hence the nonsense. Fully caffeinated and not in a rush, here's the real results.

CODE:

| gentimes start=-1 
| eval flag="this is from the first file" 
| inputlookup append=true second_file.csv

OUTPUT:

endtime endhuman    starthuman  starttime   _time   file_source row
1519361999  Thu Feb 22 23:59:59 2018    Thu Feb 22 00:00:00 2018    1519275600           
                2018-02-23 11:45:44 second  1
                2018-02-23 11:45:44 second  2
                2018-02-23 11:45:44 second  3

Easier to see/parse:
https://imgur.com/82CmsKh