lookup doesn't work in macro

[dadi]

hi guys,
I've this following command that works perfectly in search query, but doesn't work in macro:

.... | lookup myfile.csv userId

The error i get is:
"Error in 'lookup' command: Could not find all of the specified lookup fields in the lookup table."

Does anyone has an idea?

some more info-
the search query is:
cohortCalcVisitsLookup("-4w@w1", "-3w@w1", "@w1", weeklyCohort.csv, "weeklyVisits")

and the macro is:
(index="analytics" table=userSessions earliest=$start$ latest=$end$) | dedup userId | eval startOfPeriod=relative_time(_time, "$snapTo$")
| lookup "$lookupCsv$" userId
| eval oldFirstTime=firstTime | eval oldVisits=$visits$
| fillnull value=replaceme oldFirstTime
| eval $visits$=if(oldFirstTime=="replaceme", startOfPeriod, mvappend(oldVisits , "", startOfPeriod))
| makemv_withspaces($visits$)
| eval firstTime=if(oldFirstTime=="replaceme", startOfPeriod, oldFirstTime) | eval _time=startOfPeriod
| table firstTime _time userId $visits$
| inputlookup append=true $lookupCsv$ | dedup userId | outputlookup $lookupCsv$

screenshot:

Thanks!

[lguinn2]

Weird - I have no idea why it doesn't work! BUT, I do have ideas about how to figure it out - apologies if you have already tried these.

1. Copy off the first part of the macro, including the lookup command, into a new macro.
   
2. Run the new macro and use the Search Job Inspector to look at how it parsed.
   

3. Run the first part from the command line as well, again using the inspector.

4. Try explicitly naming the output fields:
   
   lookup lookup "$lookupCsv$" userId OUTPUT _time as eTime weeklyVisits firstTime

5. Note that I have renamed the _time field, so that it does not conflict with the existing field. I am not sure if this is causing the problem, but you were potentially overwriting a built-in field...

6. If none of this works, then I am out of ideas. I'd suggest that you file a support ticket. Or perhaps someone else will know the answer.

[lguinn2]

Hmm, that was a crummy error message from Splunk - it really pointed at the lookup command!!

[dadi]

after some more investigation - It seems that the problem is not in the lookup command but rather on the next phase when I'm taking one of the lookup output parameter and try to use them ($visits$ in my example). If I inline this parameter then it works

[lguinn2]

This is what you said you did on the command line:

.... | lookup myfile.csv 

But this is what the macro does:

... | lookup "$lookupCsv$" userId

Expanded in to the example used in the screenshot, it would be

... | lookup "weeklyCohort.csv" userId

-- assuming that your macro arguments are in the correct order.

These do not look the same to me at all.

Also, what fields are in the weeklyCohort.csv file?

[dadi]

Thanks for your answer.
I'm sorry, I've updated the macro section of the question with many details but kept the command line in psuedo code. In fact the command line is also doing lookup with userId and I've added this info to the question.

The fields in weekly cohort are _time, userId, weeklyVisits, firstTime.

Any ideas?

[lguinn2]

What app is the macro in? What app are you running in when you do this from the command line?

[dadi]

added info in question. Thanks!

[lguinn2]

Can you show the complete text of the macro - and also the complete command line using the macro?

What do you see when you use the Search Job Inspector? It usually shows the macro expansion - so you cn see the full search string.

[dadi]

Its the same app. If i search for the lookup inline it works, but if I wrap it in a macro and run it from the same search web page (now only call the macro instead of the explicit lookup call), then it fails with this error.