Splunk Search

Match lookup table to summary index

IRHM73
Motivator

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

0 Karma
1 Solution

HiroshiSatoh
Champion

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\")"

View solution in original post

0 Karma

HiroshiSatoh
Champion

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\")"
0 Karma

IRHM73
Motivator

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

0 Karma

HiroshiSatoh
Champion

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

0 Karma

IRHM73
Motivator

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

0 Karma

IRHM73
Motivator

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...