Querying top requested URL's using Kusto and Log Analytics in Azure Application Insights
Niels Swimberghe - - Azure
Follow me on Twitter, buy me a coffee
When you integrate Azure Application Insights into your web applications, a lot of telemetry is captured and made available for querying and visualizing. HTTP requests are one of those datapoints stored in the underlying Log Analytics workspace. One query many webmasters and content editors are interested in is which URL's are most popular.
Querying top requested URL's #
Navigate to your Application Insights resource in the Azure Portal:
Navigate to the 'Logs' section:
Inside of the query editor, copy/paste the following Kusto query:
requests | where resultCode == 200 | summarize Count=sum(itemCount) by url | sort by Count
This query does the following:
- fetch requests from the 'requests' table. This is the table where HTTP requests are stored.
- filter to requests that resulted in a 200 HTTP Status response.
- Group the requests by 'url'. Sum-aggregate the request rows by the 'itemCount' property. 'itemCount' will be 1 or higher depending on how the requests were sampled. If similar requests were sampled, they are rolled into a single row and the 'itemCount' is increased.
- Sort descending by the aggregated 'Count'.
The result will be a table where every URL represents a row and you can see the number of requests made to that URL resulting in 200 HTTP response. You can use this data to find your most popular/request URL's.
Optionally, you can improve this query by filtering out HTTP requests made by bots. There's a property in the requests dataset called "operation_SyntheticSource" which will be equal to "Bot" if it determines the request was made by a bot. This doesn't mean that if the property is not equal to "Bot", that you can be 100% sure the request wasn't made by a bot.
The query including the bot filtering looks like this:
requests | where resultCode == 200 | where operation_SyntheticSource != "Bot" | summarize Count=sum(itemCount) by url | sort by Count