Query exported data from Azure Monitor in Azure Data Explorer
Exporting data from Azure Monitor to an Azure Storage account enables low-cost retention and the ability to reallocate logs to different regions. Use Azure Data Explorer to query data that was exported from your Log Analytics workspaces. After configuration, supported tables that are sent from your workspaces to a storage account will be available as a data source for Azure Data Explorer.
The process flow is to:
- Export data from the Log Analytics workspace to the storage account.
- Create an external table in your Azure Data Explorer cluster and mapping for the data types.
- Query data from Azure Data Explorer.
Send data to Azure Storage
Azure Monitor logs can be exported to a storage account by using any of the following options:
- Export all data from your Log Analytics workspace to a storage account or event hub. Use the Log Analytics workspace data export feature of Azure Monitor Logs. For more information, see Log Analytics workspace data export in Azure Monitor.
- Scheduled export from a log query by using a logic app workflow. This method is similar to the data export feature but allows you to send filtered or aggregated data to Azure Storage. This method is subject to log query limits. For more information, see Archive data from a Log Analytics workspace to Azure Storage by using Azure Logic Apps.
- One-time export by using a logic app workflow. For more information, see Azure Monitor Logs connector for Azure Logic Apps.
- One-time export to a local machine by using a PowerShell script. For more information, see Invoke-AzOperationalInsightsQueryExport.
Tip
You can use an existing Azure Data Explorer cluster or create a new dedicated cluster with the needed configurations.
Create an external table in Azure Data Explorer
An external table in Azure Data Explorer is a schema entity that refers to data stored outside of your cluster, such as in Azure Blob Storage or Azure Data Lake Store Gen2. Similar to tables, an external table has a defined schema. However, unlike tables, the data is stored and managed externally, separate from your cluster.
To access your exported Azure Monitor data, follow these steps to create an external table:
Use the getschema operator from Log Analytics to get the schema of the exported table. This information includes the table's columns and their data types.
Create an external table using the Azure Data Explorer web UI wizard. In the schema tab, the tool attempts to automatically detect a schema. Make sure that the detected schema matches the schema from the previous step. If there are any discrepancies, adjust the schema by selecting the arrow on a column and accessing the menu.
Query the exported data from Azure Data Explorer
Query the exported data from Azure Data Explorer with the external_table function, as shown in the following example:
external_table("HBTest","map") | take 10000
