Have a KQL query which is of the below format

TempTable
| where Value contains "URL" 
| extend Url = parse_json(Value).['URL']
| where Url contains ";

I need to do the where clause with a "==" instead of "contains" , as I need to be able to join to another table using the Url value. I have tried converting "Url" to string, trimming it, but nothing seems to work. I have validated and the Url field does return the exact string I need to filter on, but using a "==" does not return any data, but "contains" works. Any other options?

Have a KQL query which is of the below format

TempTable
| where Value contains "URL" 
| extend Url = parse_json(Value).['URL']
| where Url contains "https://www.caramel./check/_xyz"

I need to do the where clause with a "==" instead of "contains" , as I need to be able to join to another table using the Url value. I have tried converting "Url" to string, trimming it, but nothing seems to work. I have validated and the Url field does return the exact string I need to filter on, but using a "==" does not return any data, but "contains" works. Any other options?

• kql

• Can you provide the input data? == Requires exact match of value( no whitespaces, no change in encoding nor type issues. Make sure that everything matches else you have to use contains – RithwikBojja Commented Mar 4 at 5:57

1 Answer 1

This seems a little strange, as Rithwik points out == requires exact matches so if contains is working but == is not I may be tempted to do some further normalisation and joining on those values instead.

Using parse_url would give you an almost guaranteed normalised set of fields.

let TempTable = datatable (Value:string, Url:string) [
    'URL', 'https://www.caramel./check/_xyz',
    'URL', 'https://www.toffee/check/_abc',
    'URL', 'https://www.taffy/check/_123',
    'URL', 'https://www.fudge.biz/check/_zyx'
];
let OtherTempTable = datatable (Url:string, OtherValue:string) [
    'https://www.caramel./check/_xyz', '1111',
    'https://www.toffee/check/_abc', '2222',
    'https://www.taffy/check/_123', '3333',
    'https://www.fudge.biz/check/_zyx', '4444'
];
TempTable
| where Value contains 'URL'
//| where Url == 'https://www.caramel./check/_xyz' //This works as expected
| extend 
    Host = tostring(parse_url(Url)['Host']), 
    Path = tostring(parse_url(Url)['Path'])
| join kind=leftouter (
    OtherTempTable
    | extend 
        Host = tostring(parse_url(Url)['Host']), 
        Path = tostring(parse_url(Url)['Path'])
) on Host, Path