Splunk Search

lookup doesn't work in macro

dadi
Path Finder

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:
Alt text

Thanks!

Tags (1)

lguinn2
Legend

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
Legend

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

dadi
Path Finder

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
Legend

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
Path Finder

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
Legend

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

dadi
Path Finder

added info in question. Thanks!

lguinn2
Legend

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
Path Finder

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.

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...