Run KQL queries against the Microsoft Sentinel data lake (preview)

Data lake exploration in the Defender portal, provides a unified interface for analyzing your data lake, enabling you to run KQL (Kusto Query Language) queries, and create and manage jobs.

The KQL queries page under Data lake exploration allows you to edit and run KQL queries against data lake resources. You can create jobs to promote data from the data lake to the analytics tier. Jobs can be run on-demand or scheduled. The Jobs page provides an interface to manage jobs, enabling, disabling, editing, or deleting jobs. For more information, see Create jobs in the Microsoft Sentinel data lake (preview).

Prerequisites

The following prerequisites are required to run KQL queries in the Microsoft Sentinel data lake.

Onboard to the data lake

KQL queries can be run in the Microsoft Defender portal after the onboarding process is complete. For more information on onboarding, see Onboarding to Microsoft Sentinel data lake.

Permissions

Microsoft Entra ID roles provide broad access across all workspaces in the data lake. Alternatively you can grant access to individual workspaces using Azure RBAC roles. Users with Azure RBAC permissions to Microsoft Sentinel workspaces can run KQL queries against those workspaces in the data lake tier. For more information on roles and permissions, see Microsoft Sentinel data lake roles and permissions.

Write KQL queries

Writing queries for the data lake is similar to writing queries in the advanced hunting experience. You can use the same KQL syntax and functions including. KQL supports machine learning functions and advanced analytics. The query editor provides a powerful interface for writing and running KQL queries, with features such as IntelliSense and autocomplete to help you write your queries efficiently. For a detailed overview of KQL syntax and functions, see Kusto Query Language (KQL) overview.

KQL queries in the Defender portal

Select New query to create a new query tab. Your last query in each tab is saved. Switch between tabs to work on multiple queries simultaneously.

Select a workspace

Queries are run against a single workspace. Choose your workspace in the upper right corner of the query editor using the Selected workspace dropdown. The workspace you select determines the data available for querying. The default workspace contains data from Microsoft Entra, Microsoft 365, and Microsoft Resource Graph.

Time range selection

Use the time picker above the query editor to select the time range for your query. Using the Custom time range option, you can set a specific start and end time. Time ranges can be up to 12 years in duration. You can also specify a time range in the KQL query syntax.

View schema information

The schema browser provides a list of available tables and their columns in the selected workspace. Use the schema browser to explore the data available in your data lake and discover tables and columns. Use the search box to quickly find specific tables.

Result window

The result window displays the results of your query. You can view the results in a table format, and you can export the results to a CSV file using the Export button in the upper left corner of the result window. Toggle the visibility of empty columns using the Show empty columns button. The Customize columns button allows you to select which columns to display in the result window.

You can search the results using the search box in the upper right corner of the result window.

Jobs

Jobs are used to run KQL queries against the data in the data lake tier and promote the results to the analytics tier. You can create one-time or scheduled jobs, and you can enable, disable, edit, or delete jobs from the Jobs page. To create a job based on your current query, select the Create job button. For more information on creating and managing jobs, see Create jobs in the Microsoft Sentinel data lake.

Azure Data Explorer

You can run KQL queries against the Microsoft Sentinel data lake using Azure Data Explorer (ADX). ADX provides a powerful query engine and advanced analytics capabilities. To connect to the data lake using ADX, create a new connection using the following URI: https://api.securityplatform.microsoft.com/lake/kql

When querying tables in the data lake using ADX, you must use the external_table() function to access the data. For example:

external_table("microsoft.entra.id.AADRiskyUsers")
| take 100

Query considerations and limitations

• Queries are run against a single workspace. Make sure you select the correct workspace before running a query.

• Executing KQL queries on the Microsoft Sentinel data lake incurs charges based on query billing meters. For more information, see Plan costs and understand Microsoft Sentinel pricing and billing.

• Review data ingestion and table retention policy. Before setting query time range, be aware of data retention on your data lake tables and whether data is available for selected time range. For more information, see Manage data tiers and retention in Microsoft Defender portal (preview).

• KQL queries against the data lake are lower performant than queries on analytics tier. It’s recommended to use KQL queries against the data lake only when exploring historical data or when tables are stored in data lake-only mode.

• The following KQL control commands are currently supported:

  • .show version
  • .show databases
  • .show databases entities
  • .show database

• Using out of the box or custom functions isn't supported in KQL queries against the data lake.

• Calling external data via KQL query against the data lake isn't supported.

• Ingestion_time() function isn't supported on tables in data lake.

Service parameters and limits for KQL queries in the lake tier

The following service parameters limitations apply when writing queries in Microsoft Sentinel data lake (Preview).

For troubleshooting KQL queries, see Troubleshoot KQL queries in the Microsoft Sentinel data lake.

Related content

• Microsoft Sentinel data lake overview (preview)
• Onboarding to Microsoft Sentinel data lake (preview)
• Create jobs in the Microsoft Sentinel data lake (preview)
• Manage jobs in the Microsoft Sentinel data lake (preview)
• Troubleshoot KQL queries in the Microsoft Sentinel data lake (preview).