Querying top requested URL's using Kusto and Log Analytics in Azure Application Insights

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.

Many analytics products will also answer this question, but those analytics programs usually rely on JavaScript, iframes, and 1-pixel images. Meanwhile, Azure Application Insights can answer the same question, but its data comes from back-end telemetry and can include request telemetry of static files as well.

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