SOLVED

Create mapping between two fields

%3CLINGO-SUB%20id%3D%22lingo-sub-1340359%22%20slang%3D%22en-US%22%3ECreate%20mapping%20between%20two%20fields%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1340359%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20write%20a%20query%20in%20Application%20Insights%20that%20returns%20the%20slowest%20element%20(buttons%2C%20fields%2C%20etc)%20rendering%20times%20from%20custom%20events%20and%20group%20these%20by%20the%20component%2Fview%20they%20belong%20to.%20Some%20of%20the%20custom%20events%20are%20generated%20on%20the%20back%20end%20and%20some%20on%20the%20front%20end.%20The%20front%20end%20events%20contain%20a%20human-readable%20name%20of%20the%20component%20but%20the%20back%20end%20events%20do%20not.%20I%20want%20to%20join%20these%20so%20that%20I%20can%20display%20the%20human-readable%20name.%20My%20query%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3EcustomEvents%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20UpdateTime%20%3D%20toint(customDimensions%5B%3C%2FSPAN%3E%3CSPAN%3E%22serverUpdateTime%22%3C%2FSPAN%3E%3CSPAN%3E%5D)%2C%20ComponentID%20%3D%20tostring(customDimensions%5B%3C%2FSPAN%3E%3CSPAN%3E%22componentId%22%3C%2FSPAN%3E%3CSPAN%3E%5D)%2C%20Property%20%3D%20tostring(customDimensions%5B%3C%2FSPAN%3E%3CSPAN%3E%22elementName%22%3C%2FSPAN%3E%3CSPAN%3E%5D)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20UpdateTime%20!%3D%20%3C%2FSPAN%3E%3CSPAN%3E0%3C%2FSPAN%3E%20%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20DisplayID%20!%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%20%3CSPAN%3Eand%3C%2FSPAN%3E%3CSPAN%3E%20Property%20!%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20ServerUpdateTime%20%3D%20percentile(UpdateTime%2C%20%3C%2FSPAN%3E%3CSPAN%3E50%3C%2FSPAN%3E%3CSPAN%3E)%20%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20ComponentID%2C%20Property%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3Ewhere%20%22componentId%22%20is%20a%20GUID%20for%20the%20component%2Fview%20in%20the%20system.%20This%20query%20only%20returns%20events%20generated%20by%20the%20back%20end.%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EI%20then%20want%20to%20get%20the%20human-readable%20component%20names.%20I%20have%20tried%20this%3A%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ejoin%3C%2FSPAN%3E%3CSPAN%3E%20(%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EcustomEvents%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20ComponentName%20%3D%20tostring(customDimensions%5B%3C%2FSPAN%3E%3CSPAN%3E%22componentName%22%3C%2FSPAN%3E%3CSPAN%3E%5D)%2C%20ComponentID%20%3D%20tostring(customDimensions%5B%3C%2FSPAN%3E%3CSPAN%3E%22componentId%22%3C%2FSPAN%3E%3CSPAN%3E%5D)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E)%20%3C%2FSPAN%3E%3CSPAN%3Eon%3C%2FSPAN%3E%3CSPAN%3E%20ComponentID%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EWhich%20doesn't%20work%20since%20some%20rows%20are%20lost%3C%2FSPAN%3E%3CSPAN%3E.%20So%20how%20do%20I%20map%20%22ComponentID%22%20to%20%22ComponentName%22%20and%20aggregate%20without%20losing%20rows%3F%20Essentially%2C%20I%20want%20to%20group%20by%20%22ComponentID%22%2C%20but%20show%20it%20as%20%22ComponentName%22.%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EThanks%20in%20advance%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1340359%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EApplication%20Insights%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECustom%20Logs%20and%20Custom%20Fields%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1340710%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20mapping%20between%20two%20fields%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1340710%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F638030%22%20target%3D%22_blank%22%3E%40ThomasLundgren%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20don't%20have%20any%20test%20data%20but%20in%20KQL%2C%20the%20last%20line%20maybe%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3E)%3C%2FSPAN%3E%20%3CSPAN%3Eon%3C%2FSPAN%3E%20%3CSPAN%3E%24%3C%2FSPAN%3E%3CSPAN%3Eleft%3C%2FSPAN%3E%3CSPAN%3E.%3C%2FSPAN%3E%3CSPAN%3EComponentID%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%24%3C%2FSPAN%3E%3CSPAN%3Eright%3C%2FSPAN%3E%3CSPAN%3E.%3C%2FSPAN%3E%3CSPAN%3EComponentName%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3EMaybe%20you%20also%20can%20change%26nbsp%3Bthe%20default%20JOIN%20kind%20from%20innerunique%3F%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fjoinoperator%3Fpivots%3Dazuredataexplorer%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fjoinoperator%3Fpivots%3Dazuredataexplorer%3C%2FA%3E%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1341112%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20mapping%20between%20two%20fields%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1341112%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%3C%2FP%3E%3CP%3EThanks%20for%20your%20quick%20reply!%3C%2FP%3E%3CP%3EAs%20with%20many%20things%2C%20the%20solution%20just%20came%20to%20me%20after%20taking%20a%20break%20from%20it%20for%20a%20while.%20Here's%20the%20solution%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EcustomEvents%0A%7C%20project%20ComponentName%20%3D%20tostring(customDimensions%5B%22componentName%22%5D)%2C%20ComponentID%20%3D%20tostring(customDimensions%5B%22componentId%22%5D)%0A%7C%20where%20ComponentName%20!%3D%20%22%22%20and%20ComponentID%20!%3D%20%22%22%0A%7C%20distinct%20ComponentName%2C%20ComponentID%0A%7C%20join%0A(%0AcustomEvents%0A%7C%20project%20UpdateTime%20%3D%20toint(customDimensions%5B%22serverUpdateTime%22%5D)%2C%20ComponentID%20%3D%20tostring(customDimensions%5B%22componentId%22%5D)%2C%20Property%20%3D%20tostring(customDimensions%5B%22elementName%22%5D)%0A%7C%20where%20UpdateTime%20!%3D%200%20and%20ComponentID%20!%3D%20%22%22%20and%20Property%20!%3D%20%22%22%0A)%0Aon%20ComponentID%0A%7C%20summarize%20avg(UpdateTime)%20by%20ComponentName%2C%20Property%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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 Replies
Highlighted
Solution

@ThomasLundgren 

 

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

 

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

@Clive Watson

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