Join a subsearch with the outer search without a common field

[smashedpumpkins]

I'm having trouble taking the results from a subsearch and joining them with the outer search. My goal is to take a set of logs containing external addresses and match them to the corresponding country which the IP block lives in.

I have a lookup definition named "country_to_ip" which contains "startRange", "endRange", and "country3". For example, one line reads "16777216,16777471,Australia". The startRange variable is the first available IP in decimal format and the endRange is the last available IP in decimal format. My logs contain an external IP in decimal format.

In order to pull the corresponding country from the lookup table I need to join the initial table lookup with a subsearch of the logs where "externalAddress>=startRange AND externalAddress<=endRange".

My problem is that it seems I can only join the results when I have matching fields. But in this case, there are no matching fields, only the calculation that determines if the IP is within the range.

I've stripped my search down for simplicity.

index=XXX sourcetype=XXXX externalAddress
| join where externalAddress>=startRange AND externalAddress<=endRange [| inputlookup country_to_ip | fields startRange,endRange,country3]
| table _time,externalAddress,country3

[somesoni2]

Here is what you need to do:

index=XXX sourcetype=XXXX externalAddress | eval joinfield=1 | join max=0 joinfield [|inputlookup country_to_ip |eval joinfield=1| fields startRange,endRange,country3,joinfield] | where externalAddress>=startRange AND externalAddress<=endRange | table _time,externalAddress,country3

[dougmartin]

Can you add an explanation of the parts.

[jtrucks]

Perhaps you need to use appendcols or just a straight subsearch instead of a join. This allows you to do any search you want and concat them together.

[smashedpumpkins]

I've tried a million and one solutions including both you've mentioned but have had little success. Would you mind putting together a simple sample for me? I would appreciate it. Thank you.