Join fields from two indexes using fields that match partially

[DavidHourani]

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

[masonmorales · Accepted solution]

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.

[DavidHourani]

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

[somesoni2]

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 ] 

[DavidHourani]

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."*" ?