How to run subsearches on individual values of a field from a primary search?

[djconroy]

I have a primary search that finds all the events that indicate a failure of a process and presents a list of unique values. I then want to take those unique values and submit a subsearch for each value to obtain a field value from the subsearch (only need 1 result, many exist), and then display a table of Search1Field and SubSearchField. For example:

index=subex-ldds StatusCode=PUBLISH_FAIL | rex field=_raw "Endpoint: (?\d+)-\d" | dedup StoreNumber

This might yield results such as:

123

445

480

1012

I would like to then search for 1 event for each StoreNumber to extract the value of a different field (applicationversion) that occurs in a different set of events and display them side by side in a table.

So the second search would need to recurse the list, something like:
search index=subex-ldds applicationversion StoreNumber=123 | rex field=_raw "ApplicationVersion=\"(?\S+)\""
...
and get back a value for store 123 such as:

123 5.1

445 5.0

480 5.1

1012 5.1

I have tried a foreach command but it is not yielding the desired results, so I'm not sure if the syntax is just wrong or if I am using foreach improperly.

[somesoni2 · Accepted solution]

There could be two ways to do this.

Subsearch

Have the first search as subsearch to second search so that instead of passing individual storenumber, you can pass them all at once.

e.g.

index=subex-ldds applicationversion 
[search index=subex-ldds StatusCode=PUBLISH_FAIL | rex field=_raw "Endpoint: (?<StoreNumber>\d+)-\d" | stats cound by StoreNumber | table StoreNumber ] 
| rex field=_raw "ApplicationVersion=\"(?<applicationversion>\S+)\"" |...rest of the search

Map command

index=subex-ldds StatusCode=PUBLISH_FAIL | rex field=_raw "Endpoint: (?<StoreNumber>\d+)-d" | stats cound by StoreNumber | map [search index=subex-ldds applicationversion StoreNumber=123 | rex field=_raw "ApplicationVersion=\"(?<applicationversion>\S+)\"" |...] maxsearches=100 

Adjust maxsearches value based on max no of result returned by subsearch per StoreNumber.