substr field comparison and negative matching

[howyagoin]

Hi,

I've tried a few of the hints here to solve this one elegantly but can't quite get there.

I have two sources of data. One is "used" and the other is "valid" - the values in "valid" contain ranges of numbers for product IDs, whilst the "used" source is what is actually sitting in an inventory system.

A "valid" value for a product ID could be something starting with:

0231

0232

049

073

And in the actual "used" data I have product IDs such as:

0231930128

023183818

074432131234

8313952

What I'm looking for is a way to extract out the values of product IDs for which there's no value prefix in the valid source to match. In this simple example, the 074432131234 and 8313952 entries (I want the entire event, not JUST this field) should appear, but not the first two.

I've tried various games with eval, tables to assign "Y" values, comparing the two, join inner, join outer, but there must be a better and easier way.

[woodcock]

Like this (provided you don't run into subsearch limit of 10500 events):

source=used NOT [search source=valid | eval productID=productID . "*" | fields productID]