help to solve a different syntax field between a lookup and a main search

[jip31]

hi

I use a lookup with a field corresponding to a site name

| inputlookup site.csv | search site=*paris*

 In this lookup field, the site can be called in many way : "PARIS 1", "Paris 2", "Paris XX"...

I use this lookup in a join command in order to cross data

index=toto sourcetype=tutu 
| stats count as Pb by site 
| join type=left site 
    [| inputlookup site.csv ] 
| table site

the problem I have is that in my main search the site for PARIS is always called "Paris"

So what I need is to cross all the events which starts by "Paris" with my lookup where there is a lot of different "Paris" syntax

How to do this please?

[gcusello]

Hi @jip31,

at first you don't need to use the join command with a lookup because the lookup command already perform a join between the main search and a lookup.

Let me understand: you have values as Paris1, Paris2, ParisXX both in your lookup and your main search, if this is your situation you could simply use the lookup command, something like this:

index=toto sourcetype=tutu 
| stats count as Pb by site 
| lookup  site.csv site
| table site

the thing that I don't understand is what is your requirement:

• you don't display count from stats,
• you don't display fields from the lookup,
• you don't use the lookup to filter results,

so why do you want to use the lookup?

Ciao.

Giuseppe

[jip31]

Hi gcusello

First, you are right for the join command but if I put directly the lookup I have the message

"Error in 'lookup' command: Must specify one or more lookup fields." and because I use a geostats command at the end of the search and unfortunately, I cant use "geostats by site"

No I dont have values as Paris1, Paris2, ParisXX both in my lookup and my main search

In my main search, the syntax for Paris is "Paris" but in the lookup the syntax for Paris can take a lot of different syntax

Thats why I need a way to solve it? Regex pearhaps?

My lookup is used to retrieve latitude and longitude for a site because at the end of my search I geostats the events

| geostats latfield=latitude longfield=longitude values(nbincid)