azure - 外部应用于 kusto/Azure AppInsights

Question

我是 azure appinsight 和 kusto 的新手，我正在尝试编写一个 kusto 查询，它将对表中的项目进行分组并按时间查找最新记录。

这是我迄今为止尝试过的，

示例表:

let Temptable=datatable(RunId:string,Module:string,AppName:string,timestamp:datetime) [
  "1", "start",   "App1", '2020-02-27T04:30:01.6062658Z',
  "1", "end",   "App1", '2020-02-27T04:31:01.6062658Z',
  "2", "start",   "App1", '2020-02-27T04:00:01.6062658Z',
  "2", "end",   "App1", '2020-02-27T04:01:01.6062658Z',
  "3", "start",   "App1", '2020-02-27T03:30:01.6062658Z',
  "3", "end",   "App1", '2020-02-27T03:31:01.6062658Z',
  "4", "start",   "App1", '2020-02-27T03:00:01.6062658Z',
  "4", "end",   "App1", '2020-02-27T03:01:01.6062658Z',
  "5", "start",   "App1", '2020-02-27T02:30:01.6062658Z',
  "5", "end",   "App1", '2020-02-27T02:31:01.6062658Z',
  "6", "start",   "App2", '2020-02-27T04:00:01.6062658Z',
  "6", "end",   "App2", '2020-02-27T04:01:01.6062658Z',
  "7", "start",   "App2", '2020-02-27T03:00:01.6062658Z',
  "7", "end",   "App2", '2020-02-27T03:01:01.6062658Z',
  "8", "start",   "App2", '2020-02-27T02:00:01.6062658Z',
  "8", "end",   "App2", '2020-02-27T02:01:01.6062658Z',
  "9", "start",   "App2", '2020-02-27T01:00:01.6062658Z',
  "9", "end",   "App2", '2020-02-27T01:01:01.6062658Z',
  "10", "start",   "App2", '2020-02-27T00:30:01.6062658Z',
  "10", "end",   "App2", '2020-02-27T00:32:01.6062658Z'
];

我正在运行以下查询，

let FactTable = Temptable
| where Module == "start"
| summarize by AppName
| project AppName;
FactTable
| lookup kind = inner (Temptable | partition by AppName( summarize Maxtime = max(timestamp) by AppName | top 1 by Maxtime desc nulls last )) on AppName;

我的输出:

我需要检索最新记录的所有列。

下面是 SQL 中的查询，它将检索特定 AppName 的前 1 条记录。

select cs.* 
    from (select AppName from #TempTable where Module = 'start' group by AppName) a
    outer apply (select top 1 * from #TempTable b where b.AppName = a.AppName order by [TimeStamp] desc) cs

如何在 Kusto 查询中实现此目的。

有没有办法在 kusto 中编写查询，并且可以在子查询中访问外列，或者有其他更好的方法吗？

Answer 1

看起来您正在寻找arg_max()聚合函数:

例如:

Temptable
| summarize arg_max(timestamp,*) by AppName