topic Re: How to search multiple indexes and join field values that don't exactly match? in Splunk Search

How to search multiple indexes and join field values that don't exactly match?

StormTrooper — Thu, 23 Oct 2014 13:23:42 GMT

Hi,

I need to search in multiple indexes but the field values won't match exactly so a straight join will not produce results.

index=proxy Url="" | join [search index=watchlist "".domain."*"]

This is the code I am using and while syntax is ok I don't know if it is doing what I want. The proxy index has a full URL while the watchlist only has the top level of the domain i.e. www.splunk.com

Any help appreciated.

Re: How to search multiple indexes and join field values that don't exactly match?

carpga — Thu, 23 Oct 2014 14:10:15 GMT

I think you may want to do an eval and rex command on the proxy Url to pull out the top level domain. I believe the join command is going to search for an exact match and I am trying to imagine scenarios where your Url and subsearch on the join won't match but should.

Re: How to search multiple indexes and join field values that don't exactly match?

yannK — Thu, 23 Oct 2014 14:21:25 GMT

if you want to join events per domain, you need to extract the domain in a field for both type of events.
By example with a rex command. Then join the 2 set of results on this new field.

please adapt to your actual fields formats.

Re: How to search multiple indexes and join field values that don't exactly match?

StormTrooper — Fri, 24 Oct 2014 13:28:14 GMT

Thank you for these answers. @ yannK I tried your code but got the following error

Error in 'rex' command: Encountered the following error while compiling the regex 'http(s|)://(?[-_\w\d.]*)': Regex: unrecognized character after (? or (?-

Any idea why? It all looks OK to me so I am not sure what I did wrong.

Re: How to search multiple indexes and join field values that don't exactly match?

yannK — Fri, 24 Oct 2014 16:18:23 GMT

The regex command was reformatted by the website,

it should have a tag after the question mark, I added it back in the example below,
please remove the "underscore" to fix it _shortdomain_ to shortdomain

Re: How to search multiple indexes and join field values that don't exactly match?

StormTrooper — Thu, 13 Nov 2014 17:23:40 GMT

Thank you for this, I got it working as I wanted.

P.S. sorry for the delay in replying I haven't had a chance to look at this for a while.