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

Share Improve this question Follow asked Mar 3 at 17:48 caramelcaramel 1122 bronze badges 1

• 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

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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

Value	Url	Host	Path	Url1	OtherValue	Host1	Path 1
URL	https://www.caramel./check/_xyz	www.caramel.	/check/_xyz	https://www.caramel./check/_xyz	1111	www.caramel.	/check/_xyz
URL	https://www.toffee/check/_abc	www.toffee	/check/_abc	https://www.toffee/check/_abc	2222	www.toffee	/check/_abc
URL	https://www.taffy/check/_123	www.taffy	/check/_123	https://www.taffy/check/_123	3333	www.taffy	/check/_123
URL	https://www.fudge.biz/check/_zyx	www.fudge.biz	/check/_zyx	https://www.fudge.biz/check/_zyx	4444	www.fudge.biz	/check/_zyx