CODEWITHSUNDEEP
kql
Symon Turner
Symon Turner

Reputation: 175

How to calculate time difference between the latest entries of two indirectly tables in Kusto (KQL)?

I have two tables in Azure Data Explorer (KQL): AzureMetric and AegDataplaneRequest. These tables are part of the same process but contain very different data, and there is no field that can be used to join them.

In SQL, I would have stored the latest timestamp of each table in a variable, then calculated the difference between them. However, in KQL, we must use a different approach due to the lack of support for variables like in SQL.

I want to calculate the time difference between the latest entry (most recent timestamp) of each table. How can I achieve this in KQL without being able to join the tables directly?

Upvotes: 0

Views: 50

Answers (2)

Gyp the Cat
Gyp the Cat

Reputation: 721

You could do something with toscalar() values and the like, would something like this work in your use case?

let AzureMetric = datatable(timestamp:datetime, metric:string) [
datetime(2024-10-28 12:00:00), 'metric1',
datetime(2024-10-28 15:00:00), 'metric2'
];
let AegDataplaneRequest = datatable(timedate:datetime, request:string) [
datetime(2024-10-27 12:01:00), 'request1',
datetime(2024-10-28 13:01:00), 'request2'
];
let MostRecentAzureMetric = toscalar(AzureMetric | summarize max(timestamp));
let MostRecentAegDataplaneRequest = toscalar(AegDataplaneRequest | summarize max(timedate));
let TimeDifference = datetime_diff('minute', MostRecentAzureMetric, MostRecentAegDataplaneRequest);
print TimeDifference
print_0
119

Upvotes: 1

Symon Turner
Symon Turner

Reputation: 175

The only way I have found is to create a column in both let statements, which Ive called A

This does work

let Functionapp = 
    workspace("AI-EazyFuel-EU-PRD-LAW").AzureMetrics 
    | where Resource == 'EG-ANALYTICS-EU-PRD'
    and TimeGenerated >= ago(24h)
    and UnitName == 'Count'
    and MetricName != 'MatchedEventCount'
    | summarize maxTimeFunction = max(TimeGenerated) by A='A';

let webhook = 
    workspace("AI-EazyFuel-EU-PRD-LAW").AegDataPlaneRequests
    | where TimeGenerated >= ago(24h)
    and OperationResult == 'Success'
    | summarize maxTimeWebhook = max(TimeGenerated) by A='A';

Functionapp 
| join kind=inner webhook on A
| extend timeDifference = datetime_diff('minute', maxTimeWebhook,maxTimeFunction)

Upvotes: 0

Related Questions