Limited search join

[mattiasrs]

Hello awesome community!

I got help from here once before so I will try again.

I have two indexes, Index A and Index B.

Fields:

Index A: id

Index B: pid, address

I want to retrieve the top 10 count ids from Index A and then join these 10 with Index B to retrieve the address info.

This is my search (simplified) to retrieve the top 10 ids from Index A:

index = A

| stats count by id

| sort -count

| head 10

Index B is huge so I only want to search it for the top 10 ids found from Index A search above.

How can I use the results found from the above search into a search from Index B?

Many thanks

[gcusello]

Hi @mattiasrs,

you cannot use commands that use subsearches (like join) because there's the limit of 50,000 results in subsearches and then because join is a very slow command.

So I hint to try something like this:

index=A OR index=B
| eval id=coalesce(id, pid)
| stats count(eval(index="A")) AS count values(address) AS address by id
| sort -count
| head 10

Ciao.

Giuseppe

[gcusello]

Hi @mattiasrs,

the B index is huge, so you cannot use a subsearch, the only way is to put both the indexes in the main search and count the events where index=A for each id, taking also the value of the IP address from the index=B.

Ciao.

Giuseppe

[mattiasrs]

Hi Giuseppe and thanks for your reply!

I tried your solution but it seems like it is searching through everything from Index B, which is not an option here due to the amount of data in that index. Also, maybe worth mentioning is that the pid field in index B contains data that is not present in Index A so I need it to only consider the top 10 count from Index A.

Any idea what I'm doing wrong?