I'm trying to create a query in Application Insights that can show me the absolute and average number of messages in conversations over a particular time period. I'm using the LUIS trace example to get the context+LUIS information, which is where I'm pulling the conversationID from. I can get a table showing the number of messages per conversation, but I would also like to have a average number of messages for the data set. Either static average or rolling average (by pulling in timestamp) would be fine. I can get this value by doing a second summarize statement, but then I lose the granularity from the first. Here is my query.
requests
| where url endswith "messages"
| where timestamp > ago(30d)
| project timestamp, url, id
| parse kind = regex url with *"(?i)http://"botName".azurewebsites.net/api/messages"
| join kind= inner (
traces | extend id = operation_ParentId
) on id
| where message == "LUIS"
| extend convID = tostring(customDimensions.LUIS_botContext_conversation_id)
| order by timestamp desc nulls last
| project timestamp, botName, convID
| summarize messages=count() by conversation=convID
This gives me a table of conversation IDs with the message count for each conversation. I would also like to see the average number of messages per conversation. For example, if I have 4 conversations with 100 messages total, I want to see that the average is 25. I can get this result by doing a second summarize statement | summarize messages=sum(messages), avgMessages=avg(messages), but then of course I can no longer see the individual conversations. Is there any way to see both in the same table?
I couldn't figure out how to do this without losing granularity from the first list (i.e. I couldn't figure out how to calculate average per period e.g. day), but the following query does at least get me the average across whatever timestamp filter I set, which ultimately gets me at the data I was looking for.
requests
| where url endswith "messages"
| where timestamp > ago(30d)
| project timestamp, url, id
| parse kind = regex url with *"(?i)http://"botName".azurewebsites.net/api/messages"
| join kind= inner (
traces | extend id = operation_ParentId
) on id
| where message == "LUIS"
| extend convID = tostring(customDimensions.LUIS_botContext_conversation_id)
| order by timestamp desc nulls last
| project timestamp, botName, convID
| summarize messages=count() by conversation=convID
| summarize conversations=count(), messageAverage=avg(messages)