How to do inner joins using Kusto query language on AppInsights

Question

I'm using the following query to get the operationId values from the requests that failed with 400 using AppInsights:

requests 
| project timestamp, id, operation_Name, success, resultCode, duration, operation_Id, cloud_RoleName, invocationId=customDimensions['InvocationId'] 
| where cloud_RoleName =~ 'xxxx' and operation_Name == 'createCase' and resultCode == 400 
| order by timestamp desc

I use these operationId values on the following queries to get the logs of what happened:

traces
| union exceptions
| where operation_Id == '35edbc7c13f7ac4c85fa0b8071a12b72'
| order by timestamp asc

With this I'm getting the information I want but I need to write and execute queries several times so I'm trying to do a join between both queries without success as I'm not an expert on querying AppInsights and I'm not sure about how to do the join with a union, can you help me?

Answer 1

Please try the query below:

requests 
| project timestamp, id, operation_Name, success, resultCode, duration, operation_Id, cloud_RoleName, invocationId=customDimensions['InvocationId'] 
| where cloud_RoleName =~ 'xxxx' and operation_Name == 'createCase' and resultCode == 400 
| join (
    traces
    | union exceptions
) on operation_Id
| project-away operation_Id1
| order by timestamp asc

More details on the join operator - https://learn.microsoft.com/en-us/azure/kusto/query/joinoperator