Getting 0 results for partitioned external table in Azure Storage

Question

I have an Azure App Gateway's WAF logs being written to blob storage and I'd like to query the data. We have been using Log Analytics to do this, so the move to blob storage is part of an effort to see if we can store the data with a retention policy since we don't need to keep it for very long, but have it in a way that we can still query if we need to, even if it's not super convenient, since we're not looking at it all the time.

It seemed that the easiest way to do this was to set up an external table that pointed to the blobs, so I stood up a local Kusto cluster using Docker and I'm using Kusto Explorer to create the external table in the cluster DB.

When I point the external table to a single file in blob storage, it works fine and it can read the rows out of the log JSON fine, but since the WAF logs are partitioned by year, month, day, and hour, it's not very useful since I can only see 1 hour's logs at a time. I tried to follow the documentation to set the table up with those partition parameters and the path format properties, and when I create the table, I get the correct sample URIs and files preview, using validateNotEmpty=true, and it all looks good. Even .show external table T artifacts shows me the expected files in the container. But querying on the table always gives 0 results.

Here's my table definition:

.create external table ExternalTable (timestamp:string, resourceId:string, operationName:string, category: string, properties: string)  
kind=storage  
partition by (Year: string, Month: string, Day: string, Hour: string)  
pathformat=("resourceid=/SUBSCRIPTIONS/<guid>/RESOURCEGROUPS/RG/PROVIDERS/MICROSOFT.NETWORK/APPLICATIONGATEWAYS/App_GW/y=" Year "/m=" Month "/d=" Day "/h=" Hour "/m=00")
dataformat=json
( 
   'https://xxxx.blob.core.windows.net/insights-logs-applicationgatewayfirewalllog;xxxxaccesskeyxxxx'
) with (sampleUris=true, filesPreview=true, validateNotEmpty=true)

Am I missing something?

Answer 1

Hello Greg McGee,

Greetings! Welcome to Microsoft Q&A Platform.

It’s great that you’re setting up an external table in Azure Data Explorer to query your partitioned WAF logs from blob storage!

When you write a query against an Azure Storage external table with partitioning, then only the folders matching the filters are accessed, rather than all the folders. This reduces the amount of data that needs to be processed and has a potential of significantly improving query performance.

If querying the table always returns 0 results, please check on the following ways to overcome this issue,

Check whether the sample URIs should match the expected blob storage paths and verify that your Kusto cluster has the necessary permissions [DB user] to access the blob storage.

Try using Data ingestion, It is the process used to load data from one or more sources into a table in Azure Data Explorer. Once ingested, the data becomes available for query.

Ingestion can be done as a one-time operation, or as a continuous method. Continuous ingestion can be configured via portal only.

For general information on data ingestion, see Azure Data Explorer data ingestion overview.

refer - https://learn.microsoft.com/en-us/azure/data-explorer/get-data-storage?source=recommendations for more detailed guidance.

Related doc's for reference - https://learn.microsoft.com/en-us/azure/application-gateway/log-analytics, https://learn.microsoft.com/en-us/azure/web-application-firewall/ag/ag-overview,

Hope this answer helps! Please let us know if you have any further queries. I’m happy to assist you further.

---

Please do not forget to "Accept the answer” and “up-vote” wherever the information provided helps you, this can be beneficial to other community members.