How to search in the subquery for join?

[bshega]

I have a query that is similar to this:

index=iot-productiondb source=Showers | search serial_number="1006055" | rename id as shower_id | join shower_id max=0 [ search index=iot-productiondb source=Recordings ] | sort + created_at | table created_at rssi | rename created_at as "Action Time" rssi as "RSSI Value"

But the problem is the subquery search index=iot-productiondb source=Recordings has about 20 million records which far surpasses the 50k limit. Is there a way I can limit that query so that it only looks for records for the matching shower? I've tried searching for the shower's id using search shower_id="$shower_id$" but I think that is performing an infinite loop or something.

Is there any way to get all of the records for this join instead of just what is retrieved from the 50k records?

[martin_mueller · Accepted solution]

Are you looking for all Recordings for a particular Shower?

index=iot-productiondb source=Recordings [search index=iot-productiondb source=Showers serial_number="1006055" id=* | head 1 | table id | rename id as shower_id]
| sort + created_at | table created_at rssi | rename created_at as "Action Time" rssi as "RSSI Value"

That will search the Showers for the shower id that matches your serial number, and then use that shower id to look for matching recordings. It's a much more splunky way than your join - that loads all recordings, attempts to join all recordings, and then throws away all non-matching recordings.

Note, I'm assuming the created_at and rssi fields come from the Recordings source.

[bshega]

I tried this but I'm not getting any results back. Is there supposed to be a join in there somewhere?

[bshega]

Oops, error on my part, I had an invalid serial number

[paulbannister]

Hi There,

Have you tried creating the query without the join, if you searched both sourcetypes and played around with transactions by the shower_id you might be able to get the result you are looking for. Might require a few evals to sort out the required fields but should be an option to get around the limitations of the join command

[adonio]

can you try the following query?

 index=iot-productiondb source=Showers serial_number="1006055" OR index=iot-productiondb source=Recordings
    | eval normalized_id = coalesce(id, shower_id)
    | sort + created_at 
    | table created_at rssi 
    | rename created_at as "Action Time" rssi as "RSSI Value"

please share some sample data from both sources as well as the desired results so we can better assist you

[bshega]

I have Shower that has a serial_number and Recording which has an rssi we search on the shower's serial_number and need to get the RSSI from every recording from that shower. I hope that helps.

[somesoni2]

YOur final output has two columns: created_at and rssi. On which source(s) both columns exist?. How many rows does your base search (which fetches shower_id/serial_number?