topic Re: Join fields from two indexes using fields that match partially in Splunk Search

Join fields from two indexes using fields that match partially

DavidHourani — Mon, 23 Mar 2015 12:34:08 GMT

Hello,

I have two indexes one containing a list of webpages that has been accessed (Index A) and another containing a list of vulnerable sites/domains/files and their description(Index B).

I would like to join these two indexes in order to see the vulnerable websites that have been accessed. A normal join operation doesn't quite cut it because the "value" field from index B can be a word that can appear anywhere in the "webpage" field of index A.

Any idea how i can perform such a join ?

Regards,
David

Re: Join fields from two indexes using fields that match partially

somesoni2 — Mon, 23 Mar 2015 17:58:00 GMT

Joins are expensive and should be avoided (if there are alternatives).

If your indexB has fewer records (<1000 for example) you can try following

index=indexA sourcetype=sourcetypeA [search index=indexB sourcetype=sourcetypeB | stats count by value | table value | eval webpage="*".value."*" | table webpage ]

Re: Join fields from two indexes using fields that match partially

DavidHourani — Tue, 24 Mar 2015 13:17:09 GMT

Thank you for your reply... I was hoping I could avoid lookups to do this.. what do you think ? would it be possible to output the useful fields from the smaller index into CSV then use them a lookup ? if so how would I handle the "*".value."*" ?

Re: Join fields from two indexes using fields that match partially

masonmorales — Tue, 24 Mar 2015 13:54:29 GMT

I'm not sure what end result you are trying to achieve, but you could probably use a calculated field...

eval match=if(like(URL, %vulnerabledomain%), 1, 0)

Then, filter to match=1 and do your join on match. Or, you may not even need to perform a join, and can run stats, etc. off the calculated field.

Re: Join fields from two indexes using fields that match partially

DavidHourani — Fri, 10 Apr 2015 11:40:20 GMT

Here we are supposing that the same event contains both fields ? The two fields I wish to match are in different events so I have to join those 2 events based on whether a part of the key matches in both of them