topic Re: How to search in the subquery for join? in Splunk Search

How to search in the subquery for join?

bshega — Mon, 04 Jun 2018 18:28:40 GMT

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?

Re: How to search in the subquery for join?

paulbannister — Mon, 04 Jun 2018 19:20:02 GMT

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

Re: How to search in the subquery for join?

adonio — Mon, 04 Jun 2018 19:39:04 GMT

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

Re: How to search in the subquery for join?

bshega — Mon, 04 Jun 2018 20:00:31 GMT

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.

Re: How to search in the subquery for join?

somesoni2 — Tue, 29 Sep 2020 19:46:08 GMT

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?

Re: How to search in the subquery for join?

martin_mueller — Mon, 04 Jun 2018 21:37:00 GMT

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.

Re: How to search in the subquery for join?

bshega — Tue, 05 Jun 2018 14:38:04 GMT

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

Re: How to search in the subquery for join?

bshega — Tue, 05 Jun 2018 14:39:21 GMT

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