CODEWITHSUNDEEP
azure-data-explorerkql
Manu Cohen Yashar
Manu Cohen Yashar

Reputation: 169

Aggregate by custom time windows in Kusto KQL Query

I am trying to aggregate metric values in specific time windows provided by another table (which captures when a test was executed).

let TestTimes = datatable (start:datetime, end:datetime, testId:string)
[
 datetime(2022-09-15T09:29-4), datetime(2022-09-15T13:10-4), "test1",
 datetime(2022-09-15T17:51-4), datetime(2022-09-15T22:12-4), "test2",
 datetime(2022-09-20T10:50-4), datetime(2022-09-20T15:10-4), "test3",
 datetime(2022-09-21T09:00-4), datetime(2022-09-21T14:20-4), "test4",
 datetime(2022-09-21T16:00-4), datetime(2022-09-21T20:00-4), "test5",
 . . .
];

I create a simple function that accepts a metric table and start/end times

let CountMetric = (T:(Timestamp:datetime, Name:string),startTime:datetime, endTime:datetime, metricName:string) {
  T 
  | where Timestamp between(startTime..endTime)        
  | where Name == metricName
  | summarize count = count()
};

I can call the function in isolation:

CountMetric(MyMetricsTable, datetime(2022-09-15T09:29-4), datetime(2022-09-15T13:10-4), "Metric1");

But, I cannot call it in the context of a query such as running it on the test times table rows:

TestTimes
| extend Metric1Counts = CountMetric(MyMetricsTable, start, end, "Metric1")

The error I get is: Tabular expression is not expected in the current context

Any idea how to solve this simple problem?

Upvotes: 1

Views: 609

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44981

That required some acrobatics...

let MyMetricsTable = // data sample generation. Not part of the solution.
    materialize
    (
        range i from 1 to 10000 step 1
        | extend Timestamp = datetime(2022-09-10) + 15d*rand()
                ,Name = strcat("Metric", tostring(toint(rand(3) + 1)))
    );
let TestTimes = datatable (start:datetime, end:datetime, testId:string)
    [
        datetime(2022-09-15T09:29:00-04), datetime(2022-09-15T13:10:00-04), "test1"
       ,datetime(2022-09-15T17:51:00-04), datetime(2022-09-15T22:12:00-04), "test2"
       ,datetime(2022-09-20T10:50:00-04), datetime(2022-09-20T15:10:00-04), "test3"
       ,datetime(2022-09-21T09:00:00-04), datetime(2022-09-21T14:20:00-04), "test4"
       ,datetime(2022-09-21T16:00:00-04), datetime(2022-09-21T20:00:00-04), "test5"
    ];
let CountMetric = (T:(Timestamp:datetime, Name:string),startTime:datetime, endTime:datetime, metricName:string)
    {
    T 
    | where Timestamp between(startTime..endTime)        
    | where Name == metricName
    | summarize count = count()
    }; 
let CountMetricWrapper = (T:(start:datetime, end:datetime, testId:string), metricsTableName:string, metricName:string)
{
    let p_start  = toscalar(T | project start);
    let p_end    = toscalar(T | project end);
    let p_testId = toscalar(T | project testId);
    CountMetric(table(metricsTableName), p_start, p_end, metricName)
    | extend testId = p_testId
};
let TestTimesSerialized =
materialize
(
    TestTimes
    | serialize
    | extend params_batch_id  = row_number(0) / 64 + 1
            ,params_set_id    = row_number(0) % 64 + 1
);  
union  // Each line supports additional 64 parameters sets
    (TestTimesSerialized | where params_batch_id == 1 | partition hint.strategy=legacy by params_set_id (invoke CountMetricWrapper("MyMetricsTable", "Metric1")))
   ,(TestTimesSerialized | where params_batch_id == 2 | partition hint.strategy=legacy by params_set_id (invoke CountMetricWrapper("MyMetricsTable", "Metric1")))
   ,(TestTimesSerialized | where params_batch_id == 3 | partition hint.strategy=legacy by params_set_id (invoke CountMetricWrapper("MyMetricsTable", "Metric1")))
count testId
32 test1
33 test5
51 test4
44 test2
45 test3

Fiddle

Upvotes: 1

Related Questions