Why is my join not working?

[leftinnerouter]

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?

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

[bowesmana · Accepted solution]

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

[leftinnerouter]

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

[gcusello]

Hi @leftinnerouter,

good for you, see next time!

Ciao and happy splunking

Giuseppe

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

[leftinnerouter]

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

[gcusello]

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

[leftinnerouter]

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.