How to create a search to compare all my products from my lookup, if they are "price tagged" or not?

[zacksoft_wf]

I have a list of products  (that i have in a csv lookup) with fields such as
prod_name, product_ID, price_tag
look up name : myproduct.csv

I want to compare  all my products from my lookup, if they are "price tagged" or not ?  
I have an index and sourcetype that contains events of all the products that are "price tagged."
index=all sourceype=all_price_tagged_poducts
Fields : prod_ID (same as product_ID of the lookup)

If the product_ID value from my lookup is present in any of the events in the sourcetype=all_price_tagged_poduct, then I know that all products in my .csv lookup are 'price tagged' 

Need help to write a query for it.

[gcusello · Accepted solution]

Hi @zacksoft_wf,

only one question: the products to check are more or less than 50,000?

Supponing that they are less than 50,000, you could use a search like this:

| inputlookup myproduct.cs
| search [ search index=all sourceype=all_price_tagged_poducts | fields prod_ID ]
| table prod_name, product_ID

in this way you have all the prod_name of the lookup present in the index.

If instead you want a status (taggen/not tagged), you should follow a different approach:

index=all sourceype=all_price_tagged_poducts
| append [| inputlookup myproduct.csv | rename product_ID AS prod_ID | fields prod_ID prod_name price_tag ]
| stats dc(index) AS dc_index values(prod_name) AS prod_name values(price_tag9 AS price_tag BY prod_ID 
| eval status=icase(dc_index="1" AND price_tag="*","Both index and lookup", dc_index="1" AND NOT price_tag="*","Only index",dc_index="0","Only lookup")
| table prod_ID prod_name price_tag status

Ciao.

Giuseppe

[gcusello]

Hi @zacksoft_wf

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉