How to work around Kusto's case sensitivity with JOIN

Question

I'm building dynamic computer groups with Log Analytics (Kusto). I need to join two tables where Computer case sensitivity does not match. Has anyone figured out a workaround for this scenario?

Example (using one computer):

Heartbeat table has Computer with Name ABCDE.domain.com Application_CL table has Computer with Name abcde.DOMAIN.com

If I run this query, the result set is empty, unless the FQDN's are an exact match. If this were SQL, it would return ABCDE.domain.com, which is what we need.

let H = Heartbeat | summarize by Computer; let A = Application_CL | where Name_s == "AppName" | summarize by Computer; H | join A on $left.Computer == $right.Computer | distinct Computer

I tried using =~ as the join operator, but it must be ==. toupper() and tolower() do me no good in this scenario.

Thanks in advance

user_7861807 · Answer

If the logic in your query allows you to use the case insensitive in~() or !in~() operators, you should choose that option.

Otherwise, you can extend a calculated column in both join legs before applying the join on that column (it's less efficient though, compared to if you didn't have to do this).

something like:

T1 
| extend lowercase_computer_name = tolower(ComputerName)
| join (T2 | extend lowercase_computer_name = tolower(ComputerName))
  on lowercase_computer_name

user_4661575 · Answer

You can use Search instead, which by default is case insensitive

user_16254881 · Answer

If what you want is to do a case insensitive regex evaluation, you can include the case insensitiveness flag at the beginning of the regex pattern.

Like in the following example:

ServiceName matches regex "(?i)super duper express"

See:

How to work around Kusto's case sensitivity with JOIN

3 Answers