Substring search on one column from the column of another table without IN operator

Question

I have two tables in Azure one of which is a list of URLs and the other has only Domain Names. I want to be able to check if the the URLs in the URLtable "contains" the domain name from the DomainName_table. The "in" operator cannot be used since there will never be an exact match. Dummy tables below:

let DomainName_table= datatable (domainname: string)
        [
            "abc456",
            "gmail"
        ]
        |summarize domainlist = make_list(domainname);
 let URLtable= datatable (URL: string)
        [
            "abc456/.com/ffsfd/sdfsdfds",
            "gmail",//.com/sAFSfS"
            "gmddail.com"///sAFfsdfsfSfS"
        ];
          URLtable
          | where URL in (DomainName_table)

I also tried to split the URL to extract the domain name :

let DomainName_table= datatable (domainname: string)
        [
            "abc456",
            "gmail"
        ]
        |summarize domainlist = make_list(domainname);
 let URLtable= datatable (URL: string)
        [
            "https://abc456.com/ffsfd/sdfsdfds",
            "https://gmail.com/sAFSfS"
            "https://gmddail.com/sAFfsdfsfSfS"
        ];
          URLtable
          |extend split_url = split(URL,"/",2)//| project split_url
  | where split_url in (DomainName_table)

This is also not a great way since it can also be "xyz.abc456.com" and it won't return a match. Will almost always return a 0 since the URL can never be an exact match.

Also there are no common columns between the two to use in a join. Basically a substring search on one column from the column of another table.

Can anyone please suggest how I can do this? Thank you for your KQL-fu.

Slavik N · Accepted Answer · 2021-03-24T06:31:51.943

There are two different approaches to solve this:

Approach #1 - this approach will work if LookupDomains has no more than 1,000,000 records:

let Urls = datatable(url: string) [
    "happydomain.com",
    "a.happydomain.com",
    "b.happydomain.com",
    "angrydomain.com",
    "a.angrydomain.com",
    "q1.a.angrydomain.com",
    "q2.a.angrydomain.com",
    "b.angrydomain.com",
    "q1.b.angrydomain.com",
    "q2.b.angrydomain.com",
    "surpriseddomain.co.il",
    "a.surpriseddomain.co.il",
    "b.surpriseddomain.co.il",
    "q1.a.surpriseddomain.co.il",
    "q2.b.surpriseddomain.co.il",
];
let LookupDomains = datatable(domain: string) [
    "happydomain.com",
    "a.angrydomain.com",
    "q1.a.surpriseddomain.co.il"
];
Urls
| extend dl = split(url, ".")
| extend dl1 = tostring(dl[-1])
| extend dl2 = strcat(dl[-2], ".", dl1)
| extend dl3 = strcat(dl[-3], ".", dl2)
| extend dl4 = strcat(dl[-4], ".", dl3)
| extend dl5 = strcat(dl[-5], ".", dl4)
| extend LoopupDomain =
        case(dl1 in (LookupDomains), dl1,
             dl2 in (LookupDomains), dl2,
             dl3 in (LookupDomains), dl3,
             dl4 in (LookupDomains), dl4,
             dl5 in (LookupDomains), dl5,
             "")
| where isnotempty(LoopupDomain)
| project-away dl*

Output:

url	LoopupDomain
happydomain.com	happydomain.com
a.happydomain.com	happydomain.com
b.happydomain.com	happydomain.com
a.angrydomain.com	a.angrydomain.com
q1.a.angrydomain.com	a.angrydomain.com
q2.a.angrydomain.com	a.angrydomain.com
q1.a.surpriseddomain.co.il	q1.a.surpriseddomain.co.il

If the LookupDomains table has more than 1,000,000 records, then the in approach above won't work, and the next approach will have to be used instead.

Approach #2 - this approach will work regardless of the number of records in LookupDomains, but requires a little more work from your side:

First you'll need to add dl2 as a separate column in the Urls and LookupDomains tables (this can be done as part of your ingestion flow, or using an Update policy).

Then you'll need to use the following query instead of the query I wrote above:

LookupDomains
| join kind=inner Urls on dl2
| extend dl = split(url, ".")
| extend dl1 = tostring(dl[-1])
| extend dl3 = strcat(dl[-3], ".", dl2)
| extend dl4 = strcat(dl[-4], ".", dl3)
| extend dl5 = strcat(dl[-5], ".", dl4)
| where (dl1 == domain) or (dl2 == domain) or (dl3 == domain) or (dl4 == domain) or (dl5 == domain)
| project-away dl*

Thank you so much for the work you put in, instead of dismissing the question. Highly appreciate it! It looks like there is no straightforward way to achieve this , the way we do Select col1,col2 from Table1 where col1 In (select for another table). — Grace A, Mar 23 '21 at 09:37
You're welcome! While this is not trivial, it's definitely doable. I've also added a second approach to the answer, please take a look, you might find it useful too. — Slavik N, Mar 24 '21 at 06:38
If this answered your question, please accept my answer (see [this](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work?answertab=active#tab-top) to know why). If you still need any info/clarification, feel free to ask by posting a comment here. — Slavik N, Mar 24 '21 at 06:38

Substring search on one column from the column of another table without IN operator

1 Answers1