How to join on customDimensions in Application Insights Analytics?

Question

When I try to join on one of the customDimensions fields, I get a syntax error: "join attributes may be only column entity or equality expressions". I am able to join on non-custom columns such as name or timestamp.

Sample code:

let ExperimentLaunchedEvents = customEvents | where name=="ExperimentLaunched" and timestamp > now(-30d);  

let ExperimentTerminatedEvents = customEvents | where name=="ExperimentTerminated" and timestamp > now(-30d); 

ExperimentLaunchedEvents  
    | project name, timestamp, experimentId=customDimensions.ExperimentId  
    | join kind=leftanti (ExperimentTerminatedEvents  
                            | project name, timestamp, experimentId=customDimensions.ExperimentId) on tostring(experimentId)  

If joining on customDimensions columns is not supported, is there any way to achieve selecting launched experiments that haven't been terminated? Thanks!

Answer 1

As mentioned by John in the comments When using custom dimensions for any operations you need to convert it to a type that can be used by the query engine. In this case I use tostring(), but you can also use other functions like toint().

I also extend a column type so it can be reused in clauses like join or where without having to use the long hand over and over again.

ExperimentLaunchedEvents
| extend experimentId=tostring(customDimensions.ExperimentId)
| project name, timestamp, experimentId
| join kind=leftanti (ExperimentTerminatedEvents
| extend experimentId=tostring(customDimensions.ExperimentId)
| project name, timestamp, experimentId)
on experimentId