Create mapping between two fields

[ThomasLundgren]

Hi!

I'm trying to write a query in Application Insights that returns the slowest element (buttons, fields, etc) rendering times from custom events and group these by the component/view they belong to. Some of the custom events are generated on the back end and some on the front end. The front end events contain a human-readable name of the component but the back end events do not. I want to join these so that I can display the human-readable name. My query:

customEvents

| project UpdateTime = toint(customDimensions["serverUpdateTime"]), ComponentID = tostring(customDimensions["componentId"]), Property = tostring(customDimensions["elementName"])

| where UpdateTime != 0 and DisplayID != "" and Property != ""

| summarize ServerUpdateTime = percentile(UpdateTime, 50) by ComponentID, Property

where "componentId" is a GUID for the component/view in the system. This query only returns events generated by the back end.

I then want to get the human-readable component names. I have tried this:

| join (

customEvents

| project ComponentName = tostring(customDimensions["componentName"]), ComponentID = tostring(customDimensions["componentId"])

) on ComponentID

Which doesn't work since some rows are lost. So how do I map "ComponentID" to "ComponentName" and aggregate without losing rows? Essentially, I want to group by "ComponentID", but show it as "ComponentName".

Thanks in advance

[#2 · Accepted solution]

@ThomasLundgren 

I don't have any test data but in KQL, the last line maybe:

) on $left.ComponentID == $right.ComponentName

Maybe you also can change the default JOIN kind from innerunique? https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/joinoperator?pivots=azuredataexplor...

[#3]

@CliveWatson

Thanks for your quick reply!

As with many things, the solution just came to me after taking a break from it for a while. Here's the solution:

customEvents
| project ComponentName = tostring(customDimensions["componentName"]), ComponentID = tostring(customDimensions["componentId"])
| where ComponentName != "" and ComponentID != ""
| distinct ComponentName, ComponentID
| join
(
customEvents
| project UpdateTime = toint(customDimensions["serverUpdateTime"]), ComponentID = tostring(customDimensions["componentId"]), Property = tostring(customDimensions["elementName"])
| where UpdateTime != 0 and ComponentID != "" and Property != ""
)
on ComponentID
| summarize avg(UpdateTime) by ComponentName, Property