Splunk Search

How to search in the subquery for join?

bshega
Explorer

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?

0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

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.

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

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
Explorer

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

0 Karma

bshega
Explorer

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

0 Karma

paulbannister
Communicator

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

0 Karma

adonio
Ultra Champion

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

0 Karma

bshega
Explorer

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.

0 Karma

somesoni2
Revered Legend

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?

0 Karma
Get Updates on the Splunk Community!

Your Guide to SPL2 at .conf24!

So, you’re headed to .conf24? You’re in for a good time. Las Vegas weather is just *chef’s kiss* beautiful in ...

Get ready to show some Splunk Certification swagger at .conf24!

Dive into the deep end of data by earning a Splunk Certification at .conf24. We're enticing you again this ...

Built-in Service Level Objectives Management to Bridge the Gap Between Service & ...

Now On-Demand Join us to learn more about how you can leverage Service Level Objectives (SLOs) and the new ...