How to search based on embedded search and partial string matching?

[silence09]

Hello,

Not sure if something similar has been posted but what i'm trying to do is a partial match of all the ids in one search result with those in another search (two different sources so can't return in one search).

dummy data:

Source 1                                                                                       Source 2

extendedproductcode                      code2                                          productcode          

2233445566                                         101573                                       5566                          

2233445567                                          245859                                      5567                          

eg. search 1 from source 1 returns a list of numbers like this: 2233445566, 2233445567 etc

Now search 2 from source 2 has data that looks like this: "5566", "5567" etc

Question is, how do I return a full list of  results from search 1 (source 1 data) where the numbers look like "*5544", "*5567" . 

For a one off case I can run simple search  the ids in source 1 using ="*5566", but I'm not sure how to do it for a list of productds say 100-200 long. As the list is dynamic, I can't hardcode the numbers/ids.

So I have a list of productids from source 2 which I need to search for in source 1 by partial match on productID. If there is a match, I want to return in a table from source 1 extendedProductId, code 2, and also the partial match. So something like this:

Query to return:

extendedproductcode                      code2                                          productcode          

2233445566                                         101573                                       5566                          

2233445567                                          245859                                      5567   

Thanks,

[ITWhisperer]

How representative is your dummy data, for example, is the product code (partial match) always 4 digits?

[silence09]

Hi @ITWhisperer  The 4 digits is always in source 1. So I'm hoping to capture a full match for the partial source 2 productId

[ITWhisperer]

Try something like this to try and filter search 1 using results from search 2

<search 1> [search <search 2> | eval extendedproductcode = "*".productcode | dedup extendedproductcode | table extendedproductcode]

Bear in mind that searches with leading wildcards are slow

Another option if you don't mind filtering after search 1 has completed

<search 1>
| eval productcode = substr(extendedproductcode, -4)
| search [search <search 2> | dedup productcode | table productcode]

Also, bear in mind that subsearches are limited to 50,000 events

[silence09]

Hi @ITWhisperer For the first example, how do I write a query where I have two searches in the same query window? Like with SQL, does it need to be in some kind of holding table? Quite new to Splunk so not sure how to do this.

Thanks

[ITWhisperer]

Since you didn't provide details of your actual searches, I will use a dummy example.

index=foo sourcetype=bar "search text" [search index=bar sourcetype=foo "search string 2" | eval extendedproductcode = "*".productcode | dedup extendedproductcode | table extendedproductcode] 

This effectively equates to 

index=foo sourcetype=bar "search text" ( extendedproducttype="*5556" OR extendedproducttype="*5557" )

SPL is not SQL - it processes a stream of events in a pipeline - each command in the pipeline processes events from the previous command and passes events on to the next command - with subsearches (the command pipeline in square brackets []), the subsearch is processed first so that the result can be used by the outer search