Match lookup table to summary index

[IRHM73]

Hi, I wonder whether someone could help me please.

I'm using the following query to to interrogate a summary index, matching this to a lookup table.

index=summary_dg_nmo report=ddcops3148V5
| lookup ddcops3148.csv telno OUTPUT telno as "Matched"
| eval Matched=if(isnotnull(Matched), "Y", "N")
| dedup telno
| table telno Matched Registered

The lookup table has 10 records and the summary index has 100 records and as you can see I extract the fields "telno", "Matched" and "Registered".

The problem I have is that in it's current format I'm extracting all 100 records, but I would only like to extract the 10 records from the lookup table then the field "Registered" from the Summary Index and then the "Matched" field.

I know that the lookup table can't filter so has to be at the beginning of the search, but I'm struggling to get this to work.

I just wondered whether someone may be able to look at this and offer some guidance on how I can make the changes.

Many thanks and kind regards

Chris

[HiroshiSatoh · Accepted solution]

Is this something like that?

 index=summary_dg_nmo report=ddcops3148V5
     [ | inputlookup ddcops3148.csv  |fields telno]
 | dedup telno
 | table telno Registered

This?

| inputlookup ddcops3148.csv  |eval Matched="N"
| join  type=outer telno [search index=summary_dg_nmo report=ddcops3148V5|dedup telno|eval Matched="Y"]
| table telno Matched Registered

This is a maximum of 10 records.

| inputlookup ddcops3148.csv  |eval Matched="N"
| join  type=outer telno [search index=summary_dg_nmo report=ddcops3148V5 [| inputlookup ddcops3148.csv  |fields telno]
        | dedup telno|eval Matched="Y"]
| table telno Matched Registered

When using MAP

| inputlookup ddcops3148.csv 
   | map search="search index=summary_dg_nmo report=ddcops3148V5 telno=$telno$
   |stats count,first(Registered) as Registered|eval telno=\"$telno$\",Matched=if(count=0,\"N\",\"Y\")"

[IRHM73]

Hi @HiroshiSatoh, thank you for this.

Unfortunately when I run this, I receive the following error:

Error in 'lookup' command: Must specify one or more lookup fields.

Any ideas please.

Regards

Chris

[HiroshiSatoh]

I'm sorry. I made a mistake.The search sentence was corrected.

[IRHM73]

Hi, thank you for taking the time to come back to me with this.

Unfortunately though it's not returning the contents of the lookup table.

Many thanks and regards

Chris

[IRHM73]

Hi, thanks you for this. The output is perfect, but could you tell me please, is there a way of staying away from the join because I have read that it's inefficient and restricts the number of results to 50,000 rows.

Many thanks and kind regards

Chris