Generating unique IP visits rendered into a column chart with kusto query for Azure Storage Account hosted website published with an App Gateway

I recently worked with a client who needed to quickly host a static website requiring zero dynamic content and little to no updates for years. Given the short runway available and the team being cost conscious, we opted to use the Static website feature of an Azure Storage Account to publish the website. Other than having to deal with the [I think] widely known WebContentNotFound issue when reloading pages, the service provided an adequate way of hosting the website. There was already an App Gateway in the environment so it was used to provide custom domain and WAF protection capabilities.

A few weeks into the launch of the website, I was asked to generate some statistics for the websites visit and given that I had the Diagnostics settings for the App Gateway set up to send allLogs to a Log Analytics Workspace and the logs captured on the Storage Account wouldn’t provide the real public IP addresses of the inbound traffic, I decided to use KQL to obtain the report requested.

The following are two reports I generated and thought I’d share it in case anyone may be looking for this.

Review visits over a range of days with hours as scale
This report groups unique IP addresses into bins within an hour over the start and end date specified.

Review visits over a range of days
This report groups unique IP addresses for each day over the start and end date specified.

The queries can be retrieved from my GitHub repo: https://github.com/terenceluk/Azure/blob/main/Kusto%20KQL/Azure-App-Gateway-Website-Stats.kusto

Hope this helps anyone who needs this data. The query can easily be changed for any backend service hosting the website and modified for different results.

Useful Kusto Query / KQL queries for Azure Firewall Troubleshooting

I do not often have the opportunity to do as many hands on deployment of Azure services on projects due to my role as an architect so when I do, I tend to spend a lot of time working with the service to try and understand the ins and outs of the product. One of my recent projects provided me the opportunity to deploy the Azure firewall that I designed and I noticed that there weren’t many Kusto query examples available for troubleshooting inbound and outbound traffic so I wanted to post a link to my GitHub repo where I have and continue to build upon KQL queries for querying Azure Firewall logs to monitor traffic: https://github.com/terenceluk/Azure/blob/main/Kusto%20KQL/Azure-Firewall.kusto

I tried to demonstrate as many customizations such as time zones, days ago, start and end time, variables that allowed these basic KQL queries to help me troubleshoot all the Teams outbound traffic that were being blocked as well as weekly reporting I needed to deliver to the client. Hope this helps anyone who might be looking for example queries and can use these as a start.