Forum Discussion
Solved
Create mapping between two fields
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
I don't have any test data but in KQL, the last line maybe:
) on $left.ComponentID == $right.ComponentNameMaybe you also can change the default JOIN kind from innerunique? https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/joinoperator?pivots=azuredataexplorer
2 Replies
I don't have any test data but in KQL, the last line maybe:
) on $left.ComponentID == $right.ComponentNameMaybe you also can change the default JOIN kind from innerunique? https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/joinoperator?pivots=azuredataexplorerThanks 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
