"Failed to resolve 'top' key column" Error in Application Insights Analytics Custom Dimensions

Question

I have some data in Application Insights, and I am using the Analytics view to write queries against it.

I can see that I have a trace, the CustomDimensions of which contain a property called ActivityID, which is a guid:

What I want to do is now run another query to return all traces that contain that ActivityId.

Using this as guide, I currently have the following:

union (traces
| extend ActivityId = tostring(customDimensions.ActivityId)
| project ActivityId
| where ActivityId  == "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx")
| top 101 by timestamp desc

However, this is returning the following syntax error message:

Failed to resolve 'top' key column

What am I doing wrong? I would also appreciate and an explanation of the error message if possible.

The accepted answer actually works but misleading. see my answer below. — yonisha, Jul 29 '17 at 12:19

Peter Bons · Accepted Answer · 2017-07-29T13:22:42.657

1

You cannot do a top on projection unless you actually include the timestamp column in the projection.

I did :

union (traces)
| top 101 by timestamp desc
| project session_Id

so this should work

union (traces
| extend ActivityId = tostring(customDimensions.ActivityId)
| where ActivityId  == "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx")
| top 101 by timestamp desc
| project ActivityId

and then it works. What is your complete query (I guess there is more since you are using union?)

edited Jul 29 '17 at 13:22

answered May 25 '17 at 15:00

Peter Bons

26,826
4
50
74

That fixed the error, thanks. The reason I used union originally was I had just modified the query that auto-populated after I opened the analytics view, which used a union. One other question, the error has gone but now I'm getting "no results found". Do I need to cast toguid rather than tostring? – Declan McNulty May 25 '17 at 15:06
Hmm I always have used `tostring` myself, even for values that could be parsed as a guid. Do mind it is case sensitive! Even stranger, only `==` is. Change it into `contains` than it will compare case insensitive. – Peter Bons May 25 '17 at 15:07
@PeterBons "It seems you cannot do a top on projection" is incorrect. you can do a top on project, but you'll need to project it first :) – yonisha Jul 29 '17 at 12:20
@yonisha Well then, my answer does work, but thank you for the correction and for the opportunity to update my answer. Not sure if it warrants a downvote though. Especially since I did not state it as a fact, I stated that it seemed (to me that) way. – Peter Bons Jul 29 '17 at 13:13

score 0 · Answer 2 · answered Jul 29 '17 at 12:17

You must project the 'timestamp' column if you want to use it inside the top.

traces
| extend ActivityId = tostring(customDimensions.ActivityId)
| project ActivityId, timestamp
| where ActivityId  == "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
| top 101 by timestamp desc

*Note that you also do not need to use union

"Failed to resolve 'top' key column" Error in Application Insights Analytics Custom Dimensions

2 Answers2