Azure Data Explorer topics

Kusto: summarize consecutive segments of the same value per batch

Deleted — Wed, 28 Jan 2026 19:12:49 GMT

Problem statement (Azure Data Explorer / Kusto)

I have a table in Azure Data Explorer (Kusto) with the following columns:

batch_number (string)
team (string)
datetime (datetime)

Each row represents work done by a team on a batch at a given time.

The data is chronological per batch, but teams can change over time and may come back later.

Example data (ordered by batch_number, then datetime):

batch_number | datetime | team
001 | 2024-01-01 08:00:00 | A
001 | 2024-01-01 08:01:00 | A
001 | 2024-01-01 08:02:00 | A
001 | 2024-01-01 08:03:00 | B
001 | 2024-01-01 08:04:00 | B
001 | 2024-01-01 08:05:00 | A
001 | 2024-01-01 08:06:00 | A
002 | 2024-01-01 08:00:00 | A
002 | 2024-01-01 08:01:00 | A
002 | 2024-01-01 08:02:00 | B
002 | 2024-01-01 08:03:00 | C

Goal

For each batch, I want to group consecutive rows where the team stays the same and compute:

start_time = min(datetime)
end_time = max(datetime)

The result must preserve team sequences, not just distinct teams.

Expected result:

batch_number | team | start_time | end_time
001 | A | 2024-01-01 08:00:00 | 2024-01-01 08:02:00
001 | B | 2024-01-01 08:03:00 | 2024-01-01 08:04:00
001 | A | 2024-01-01 08:05:00 | 2024-01-01 08:06:00
002 | A | 2024-01-01 08:00:00 | 2024-01-01 08:01:00
002 | B | 2024-01-01 08:02:00 | 2024-01-01 08:02:00
002 | C | 2024-01-01 08:03:00 | 2024-01-01 08:03:00

Notes

Team A appears twice for batch 001 because it appears in two separate consecutive segments.
The grouping must be done per batch, not across batches.

Important constraints

A simple summarize by batch_number and team is not correct because it merges non-consecutive segments.
I can easily implement this logic in pandas using a cumulative sequence identifier, but I have not found a reliable equivalent in Kusto.

Question

What is the correct and reliable Kusto query to compute these consecutive team segments per batch?

How to use existing cache for external table when acceleration in progress

kunpengfan — Wed, 30 Jul 2025 03:23:49 GMT

I enabled query acceleration for my external table which binds a delta table (1TB) on ADLS. But acceleration progress needs 1.5 hours to complete.

I found during acceleration in progress, querying on the table is quite slower than case when acceleration completed.

How can I use the existing acceleration cache/index and after acceleration completed, Kusto will switch to new index?

timechart legend in Azure Data Explorer

panug — Tue, 29 Jul 2025 22:43:37 GMT

Hi, I'm creating a timechart dashboard in Azure Data Explorer and facing an issue with the legend labels. The legends have extra prefixes and suffixes, such as "Endpoint" or "Count". How can I remove these and show only the actual value in the legend?

Thank you!

Parameter controls are not showing Display text

SonPham — Mon, 23 Jun 2025 10:01:42 GMT

Hi,

After a recent update to the Azure Data Explorer Web UI, the Parameter controls are not displaying correctly. The Display Text for parameters is not shown by default; instead, the raw Value is displayed until the control is clicked, at which point the correct Display Text appears.

Could you please investigate this issue and provide guidance on a resolution?

Thank you,

Export to Excel is not working

SonPham — Mon, 23 Jun 2025 09:49:38 GMT

Hi,

After the recent Azure Data Explorer Web UI update, the "Export to Excel" feature is no longer functioning as expected. While it works for simple tables but it takes longer time than before, it fails for tables containing complex data outputs, such as Empty, Null, Array [], or JSON data. Clicking the "Export to Excel" option does not produce the expected results.

Could you please investigate this issue and provide guidance on a resolution?

Thank you,

KQL Query output limit of 5 lakh rows

jatinkolhe — Tue, 27 May 2025 10:55:19 GMT

Hi , i have a kusto table which has more than 5 lakh rows and i want to pull that into power bi. When i run the kql query it gives error due to the 5 lakh row limit but when i use set notruncation before the query then i do not get this row limit error on power bi desktop but get this error in power bi service after applying incremental refresh on that table. My question is that will set notruncation always work and i will not face any error further for millions of rows and is this the only limit or there are other limits on ADE due to which i may face error due to huge volume of data or i should export the data from kusto table to azure blob storage and pull the data from blob storage to power bi. Which will be the best way to do it?

Union and then distinct values of a column

LeHello — Tue, 15 Apr 2025 07:32:18 GMT

Hi,
Here is a description of my problem.

I have 3 tables, but i will take the example of 2. in each table, I have a column "common_col". I want to combine the values of this 2 tables to get one column and then return the distinct values : like that i will be sure that i have the values of each tables.

********************Script 1**********************

((table1
| project common_col
| distinct common_col)

| union

(table2
| project common_col
| distinct common_col))

| distinct common_col

*******************************************************

******************Script 2 ********************************

((table1
| project common_col
| distinct common_col)

| union

(mv_pdb_process_stl
| project common_col
| distinct common_col))

| summarize by common_col

**********************************************************

The first and second script, because when after importing the data in Power BI, I tried this :

Nb_distinct_values = DISTINCTCOUNT('Newtable'[common_col])

Nb_Total_rows = COUNTROWS('common_col')

I don't get the same number and it is don't normal. So there is an issue in my script and I need help please ! Thanks !

Azure ADX - UpdatePolicy fails to insert data

Saihariharan1988 — Tue, 01 Apr 2025 06:37:26 GMT

Hi Everyone,

I believe everyone is doing good and safe.

I am facing challenge with ADX. Please find the problem details below.

Problem statement: We are unable to insert a result data into a target table from source table using an UpdatePolicy.

Description: We have written an UpdatePolicy on a table. This UpdatePolicy will accept query parameters as an ADX function. This function returns output result in the form of table. Further, This table output result received should be inserted into target table.

Additional Details:

UpdatePolicy helps to update the data into a target table from source table dynamically/automatically.
UpdatePolicy is almost equivalent to Triggers in SQL Server to do dynamic insert into a target table.
Syntax of UpdatePolicy

.alter table TargetTable policy update

```

[

{

"IsEnabled": true,

"Source": "SourceTable",

"Query": "SourceTable

| extend Result = G3MS_ClearAlarm(Id, CountryCode, OccuredTime)

| project AlarmId = Result.AlarmId, ClearAlarmId = Result.ClearAlarmId, ClearTime = Result.ClearTime",

"IsTransactional": true,

"PropagateIngestionProperties": false

}

]

```

Error Received when executed

Error during execution of a policy operation: Request is invalid and cannot be processed: Semantic error: SEM0085: Tabular expression is not expected in the current context.

If anyone has any suggestions/thoughts on this will be very beneficial to complete the requirement.

ADX data receiving stop after sometime

darksaturn — Wed, 05 Mar 2025 18:28:24 GMT

I have very strange problem. I have IOT application where device send data to IOT hub, then it routed to event hub. There is function with trigger as azure function ,this function insert data in to ADX .

I trace the event from device to event hub. I can see all data I can also see function get trigger ,no error.But in ADX empty record, no data from event. Just date field which add explicitly.

Note- Again after some time I can see data in ADX (no change in ADX not even restart any service).

Can any body have clue what exactly could be issue?

External Table in ADX

JasonWorlin — Tue, 18 Feb 2025 20:27:55 GMT

Hi,

I'm trying to create an external table in ADX which uses a Synapse Analytics (SA) database view (called undelivered). The undelivered view itself is query data from a Cosmos analytical store

I've create a user defined idenity
- Added the identiy to the ADX cluster, SA and Cosmos
Updated the ADX database:
.alter-merge cluster policy managed_identity[
{
"ObjectId": "a3d7ddcd-d625-4715-be6f-c099c56e1567",
"AllowedUsages": "ExternalTable"
}
]
Created the database users in SA
-- Create a database user for the ADX Managed Identity
CREATE USER [adx-synapse-identity] FROM EXTERNAL PROVIDER;
-- Grant read permissions
ALTER ROLE db_datareader ADD MEMBER [adx-synapse-identity];
GRANT SELECT ON OBJECT::undelivered TO [adx-synapse-identity];
From within SA I can "SELECT * FROM undelivered" and the correct information is returned
But when I come to create the external table in ADX:
.create-or-alter external table MyExternalTable
(
Status: string
)
kind=sql
table=undelivered
(
h@'Server=tcp:synapse-xxxxx.sql.azuresynapse.net,1433;Database="Registration";ManagedIdentityClientId=<key>;Authentication=Active Directory Managed Identity;'
)
with (
managed_identity = "<key>"
)
I get the error: Managed Identity 'system' is not allowed by the managed_identity policy for usage: ExternalTable

So even with me specifying the managed identity I want to use it is still trying to use the system one.

How can I get the external table created with the correct managed identity?
Any questions please just ask
Thanks

Issue with mysql_request Plugin on Dashboard

SonPhamT — Tue, 21 Jan 2025 15:34:39 GMT

Today, while using the Azure Data Explorer dashboard, we noticed that the mysql_request plugin is no longer functioning as expected. Specifically, we are encountering the following error message:

evaluate mysql_request(): the following error(s) occurred while evaluating the output schema: The 'mysql_request' plugin cannot be used as the request property request_readonly_hardline is set.

Interestingly, the plugin works perfectly fine within the Query tab. However, when called from the dashboard, it fails to execute. This issue was not present yesterday, as it was working seamlessly at that time.

Looking further, I realize that in Query Tab, the ADX UI send with

"Options":{

"request_readonly_hardline": false

}

And the request from Dashboard such as Table, Chart. It send with request_readonly_hardline: true

Is this on purpose?

Thanks

Extending by a function output

ic2092 — Tue, 17 Sep 2024 10:22:32 GMT

datatable(ids: dynamic) [ dynamic(["value1", "value2"]) ] | function(ids)

This above snippet works fine and returns a table

datatable(date: datetime, ids: dynamic) [ datetime(2022-01-01), dynamic(["value1", "value2"]), datetime(2022-01-02), dynamic(["value3", "value4"]) ] | extend outputs = function(ids)

This one however complains that extend expects a scalar and not table that the function returns

datatable(date: datetime, ids: dynamic) [ datetime(2022-01-01), dynamic(["value1", "value2"]), datetime(2022-01-02), dynamic(["value3", "value4"]) ] | extend outputs = toscalar(function(ids))

When using toscalar, ids cannot be referenced. Is there a workaround?

The function take in dynamic and returns a tubular expression of one row and two columns

Disappearing files after ending session

EGLHUBJEOC3 — Tue, 16 Jul 2024 15:27:41 GMT

Good day Any idea why my files on a Surface Hub 2s keep disappearing whenever I end session and log back in? Is there a way I can permanently keep them in my Documents folder or at the File Explorer folder and access them at anytime even after ending session?

attached I uploaded two pictures. One with the created folder called JEOC Docs and in the other picture the files disappeared after ending session or login out. Like in the below picture.

SQL Server emulation layer; support for prepared statements

vizzly_james — Mon, 03 Jun 2024 07:27:13 GMT

ADX supports very basic transformation of T-SQL to KQL, as it is limited to normal SQL statements.

Currently support for prepared statements doesn't seem to be in-place. This would greatly improve security of the emulation layer by enabling prepared statements to be used.

This would also allow semantic layers and customer facing analytics tools such as Vizzly to integrate in a secured manner.

Why does ADX caching result from related dimension table/mv/function

Bartek_insight — Mon, 25 Mar 2024 14:03:18 GMT

I'm testing materialized views based on the sample queries below:

Lookup to another materialized view (bar_mv) storing last timestamps in field LAST_FOO_TS for some key(a,b,c)

.create materialized-view with (dimensionMaterializedViews = "bar_mv") 
foo_mv on table events_table{
events_table 
|lookup materialized_view('bar_mv') on a,b,c
|where TIMESTAMP > LAST_FOO_TS
}

Lookup to stored function (bar_v) returning last timestamps in field LAST_FOO_TS for some key(a,b,c)

.create materialized-view
foo_mv1 on table events_table{
events_table 
|lookup bar_v() on a,b,c
|where TIMESTAMP > LAST_FOO_TS
}

In both cases, looks like ADX is caching results of the lookup materialized view/function and not refresing it for long time or ever. Is there any way to force refresh of those to get the newest values of LAST_FOO_TS in "where" condition?

Kusto ADX plugin for Azure Devops (dashboards)

Girish_Dasarathy — Fri, 16 Feb 2024 14:51:14 GMT

The existing kusto plugin that we are using in our Azure DevOps (Kusto and Application Insights Widget - Visual Studio Marketplace) is broken and there is no ETA on a fix.

Would be great if one of you could point me to a work around/a different plugin that we could use to access kusto data from the devops dashboards.

Connect using SAS Token or APIKey

Messan APETE — Tue, 30 Jan 2024 14:55:24 GMT

Hello,
There is a lot of samples showing how to connect to ADX using an interactive user or a service principal but I would like to know if there is a way to connect to ADX within a non-interactive service using something like a SAS token or a kind of APIKey ?

Regards

Materialized view with inner join over 2 tables skips data

Deyan_Petrov — Mon, 22 Jan 2024 14:05:54 GMT

Hi,

I have 2 tables which are ingested from event hubs with standard injection policy (10 minutes etc).

I have created a materialized view which has roughly the following definition:

.create materialized-view TransactionsWithCustDataMatView on table Transactions

{

Transactions

| join kind=inner Customers on CustomerId

| where CreatedOn > LastModifiedOn1

| summarize arg_max(LastModifiedOn1, *) by TransactionId

}

The problem is that sometimes a customer registers and within 1 minute also performs a transactions. However, since Customers and Transactions are different tables, each with its own ingestion (batching), it happens that sometimes the transaction of a new customer is ingested before the corresponding customer is ingested, and then the above materialized view "skips" the transaction due to the inner join.

Is there a way how to control/sync the ingestions of the 2 source table, or schedule the materialized view to run with some delay of 10-20 minutes (when ingestions to both tables would have happened)?

Br,
Deyan

SLA Query

JKPrasannaDonthireddy — Mon, 22 Jan 2024 13:20:12 GMT

How to Calculate SLA using KQL Query?

'State' Graph from ADO

JKPrasannaDonthireddy — Fri, 12 Jan 2024 17:49:27 GMT

How to get 'State' like Stages of State (New-Active-Inprogress-Closed) from ADO State graph in to KQL query