topic Re: Why is my join not working? in Splunk Search

Why is my join not working?

leftinnerouter — Thu, 28 Jul 2022 19:30:34 GMT

Basically my query should search an index for an ip in the last 4 hours and return 1 event.

Then it should left join on IP to a second index and search for results over the last 7 days.

The IP i am searching exists in both indexes.

Why are no results being returned?

Re: Why is my join not working?

gcusello — Fri, 29 Jul 2022 06:15:18 GMT

Hi @leftinnerouter,

how many results have you running the subsearch by itself? probably more than 50,000.

There's a limit of 50,000 results in subsearches, in addition the join command is very slow.

You are in the usual error of people coming from SQL: Splunk isn't a DB and you should use join only when you haven't any other solution.

in your case, I suppose that you want to check if the IP from the main search in 4 hours was present in the previous 7 days, is it correct?

If this is your requirement, please, try this approach:

index=data2 earliest=-7d latest=now() [ search index=data1 Source_Network_Address=10.1.1.1 earliest=-4h latest=now() | head 1 | rename Source_Network Address as IP | fields IP ] | table index _time IP DNS

Ciao.

Giuseppe

Re: Why is my join not working?

leftinnerouter — Wed, 10 Aug 2022 01:30:32 GMT

Sorry for the late response.

There are less than 50,000 results and I have attempted adding a limit as suggested.

Unfortunately this is still not working for me as expected.

Re: Why is my join not working?

leftinnerouter — Wed, 10 Aug 2022 01:31:57 GMT

Yes I would like to check if the IP from the main search in 4 hours was present in the previous 7 days.

Re: Why is my join not working?

bowesmana — Wed, 10 Aug 2022 03:12:30 GMT

First check:

Two searches - do they return data.

earliest=-4h latest=now() index=data1 Source_Network_Address=10.1.1.1 | head 1 | rename Source_Network Address as IP AND earliest=-7d latest=now() index=data2 IP=10.1.1.1 | table index, _time, IP, DNS

Secondly, don't use join - if you're looking for a single IP in both 'index=data1' in last 4 hours AND 'index=data2' in last 7 days you could use a simple OR search

(earliest=-4h latest=now() index=data1 Source_Network_Address=10.1.1.1) OR (earliest=-7d latest=now() index=data2 IP=10.1.1.1) | rename Source_Network Address as IP | table index, _time, IP, DNS

you can then control which of the returned entries you are interested in.

Note that if you DO you join, then always join on the smaller data set, otherwise you are likely to come up against limits. As @gcusello says, join is rarely the way to solve a Splunk query - there are almost always better ways to write the query, typically using 'stats' to join things together, e.g. you could end the above second query with

| stats values(*) as * by IP

Re: Why is my join not working?

leftinnerouter — Wed, 10 Aug 2022 12:13:40 GMT

@bowesmana @gcusello thank you for your help. I've reverted to a simple search.

Re: Why is my join not working?

gcusello — Wed, 10 Aug 2022 13:13:46 GMT

Hi @leftinnerouter,

good for you, see next time!

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉