Using results from one search as a filter for another

[dmacl]

Hi,

I'm trying to filter the results from one search based on the results from another search.

Example:

Consider the following table of data

user	eventId
Joe	1
Joe	2
Bob	3

I have created a search that returns only eventIds generated by user Joe and creates a token with the result

<search>
    <query> "event created" user=Joe | table eventId </query>
        <done>
            <set token="eventId">$result.eventId$</set>
        </done>
</search>        

I have another table with the following data

eventId	eventName
1	myEvent_1
2	myEvent_2
3	myEvent_3

What I would like to do is create a search that will return just the eventId and eventName that was generated by user Joe using the token created in the first search. So far I have this query

"event names" eventId=$eventId$ | table eventId eventName

This query is only returning the first result from the token list rather than every result. Is there a way to use the token this way to return results from all values in the token? I would like to avoid using JOIN or subsearches as I will need to create multiple tables with the same token filter and those methods would start to get very slow. Thanks in advance!

[somesoni2]

Try something like this

"event names" [search "event created" user=Joe | table eventId | table eventId eventName

[dmacl]

Thank you, this solution works.  Is there a way to cache the results from the inner search though? I need to run the same inner search multiple times and would be cautious of the performance of doing so.

[ITWhisperer]

You only have access to the first row of results. If you want more data, you will have to rewrite the first search to return the data in the first row.

[dmacl]

Thanks for the reply, how would I go about rewriting the first search to return all the data in the first row?

[ITWhisperer · Accepted solution]

You could try this

| stats list(*) as *

which should give you multivalue fields for all non-null columns - the issue with this is that if any of the rows have nulls in some of the time, the rows across the multivalue fields become misaligned.

[dmacl]

Thank you! This does exactly what I needed. So I used the stats list as you mentioned

<search>
    <query> "event created" user=Joe | stats list(eventId) as eventId </query>
        <done>
            <set token="eventId">$result.eventId$</set>
        </done>
</search> 

and I was able to use the token in my later searches using IN

"event names" | search eventId IN($eventId$) | table eventId eventName

  I've accepted this answer because it uses the cached values which was important for my requirements