How to list matches of a search using lookup values

[dugalle]

Hi!

I have create a search that uses a dynamic lookup to find events in some index looking at the raw:

...................
Lookup.csv
...................
id value
.. ..........
1 one
2 two
3 three

....................................
sample-index Events
....................................
2020-01-17 11:42:37 Sample event one
2020-01-17 11:42:33 Sample event five
2020-01-17 11:42:31 Sample event two

Query:

index=sample-index
[
| inputlookup Lookup.csv
| table value
| rename value as search | format
]
| table _time , _raw

It works fine and returns the Event 1 and 3 but I want to list the values of the lookup that has matched the events (in this case it should return "one" and "two"). I think that it should be done counting the matches of all the values of the lookup in the results but I don't know how to do it. The csv is generated every day so I can't put the values on the query.

Do you have any idea how to do it?

Thanks!

[to4kawa]

UPDATED:
e.g.

| makeresults 
| eval _raw="time,raw
2020-01-17 11:42:37,Sample event one
2020-01-17 11:42:33,Sample event five
2020-01-17 11:42:31,Sample event two" 
| multikv forceheader=1 
| rename raw as _raw 
| rename time as _time 
| table _time _raw 
    `comment("this is sample your provide")` 
| appendpipe
    [| makeresults 
    | eval _raw="id value
1 one
2 two
3 three" 
    | multikv forceheader=1 
    | table id value
        `comment("this is sample as | inputlookup Lookup.csv")`
    | table value ]
| eventstats values(*) as *
| stats values(_raw) as raw by value
| where match(raw,value)
| table value

Recommend:

index=sample-index
| table _raw
| appendpipe [|inputlookup Lookup.csv
| table value]
| eventstats values(*) as *
| stats values(_raw) as raw by value
| where match(raw,value)
| table value

How about this?

[dugalle]

Hi! the field check appears always as "Null" 😞

Regarding to the table, that info is a sample of the "sample-index" events.

Thanks!

[TISKAR]

Hi @dugalle:

Can you try by lookup command like this:

index=sample-index
| lookup Lookup.csv value as search OUTPUTNEW value 
| where !isnull(value)
| table _time , _raw

[dugalle]

Hi, i have tried it and doesn't work 😞