教程:在 Azure 数据资源管理器和 Azure Monitor 中使用 Kusto 查询Tutorial: Use Kusto queries in Azure Data Explorer and Azure Monitor

了解 Kusto 查询语言的最佳方式是查看一些基本查询,从而“感受”这种语言。The best way to learn about the Kusto Query Language is to look at some basic queries to get a "feel" for the language. 建议使用包含一些示例数据的数据库We recommend using a database with some sample data. 本教程中演示的查询应在该数据库上运行。The queries that are demonstrated in this tutorial should run on that database. 示例数据库中的 StormEvents 表提供了一些有关在美国发生的风暴的信息。The StormEvents table in the sample database provides some information about storms that happened in the United States.

统计行数Count rows

我们的示例数据库有一个名为 StormEvents 的表。Our example database has a table called StormEvents. 若要了解该表的大小,请将其内容传递到一个运算符,该运算符只计算表中的行数。To find out how large the table is, we'll pipe its content into an operator that simply counts the rows in the table.

语法说明:查询是数据源(通常是表名称),可以选择后跟一个或多个管道字符和一些表格运算符。Syntax note: A query is a data source (usually a table name), optionally followed by one or more pairs of the pipe character and some tabular operator.

StormEvents | count

输出如下:Here's the output:

计数Count
5906659066

有关详细信息,请参阅 count 运算符For more information, see count operator.

选择列的子集:projectSelect a subset of columns: project

使用 project 来只选择所需的列。Use project to pick out only the columns you want. 请参阅下面的示例,该示例同时使用 projecttake 运算符。See the following example, which uses both the project and the take operators.

按布尔表达式筛选:whereFilter by Boolean expression: where

仅查看 2007 年 2 月在 California 发生的 flood 事件:Let's see only flood events in California in Feb-2007:

StormEvents
| where StartTime > datetime(2007-02-01) and StartTime < datetime(2007-03-01)
| where EventType == 'Flood' and State == 'CALIFORNIA'
| project StartTime, EndTime , State , EventType , EpisodeNarrative

输出如下:Here's the output:

StartTimeStartTime EndTimeEndTime 状态State EventTypeEventType EpisodeNarrativeEpisodeNarrative
2007-02-19 00:00:00.00000002007-02-19 00:00:00.0000000 2007-02-19 08:00:00.00000002007-02-19 08:00:00.0000000 CALIFORNIACALIFORNIA 洪水Flood 19 日凌晨,一股锋面系统穿过南部的 San Joaquin 山谷,给西部的 Kern 县带来了短时强降雨。A frontal system moving across the Southern San Joaquin Valley brought brief periods of heavy rain to western Kern County in the early morning hours of the 19th. 据报道,Taft 附近的 166 号州高速公路发生不严重的洪水。Minor flooding was reported across State Highway 166 near Taft.

显示 n 行:take Show n rows: take

我们来看一些数据。Let's see some data. 5 行随机示例是怎样的?What's in a random sample of five rows?

StormEvents
| take 5
| project  StartTime, EndTime, EventType, State, EventNarrative  

输出如下:Here's the output:

StartTimeStartTime EndTimeEndTime EventTypeEventType 状态State EventNarrativeEventNarrative
2007-09-18 20:00:00.00000002007-09-18 20:00:00.0000000 2007-09-19 18:00:00.00000002007-09-19 18:00:00.0000000 暴雨Heavy Rain 佛罗里达州FLORIDA 在 24 小时的时间里,Volusia 县沿海部分地区降雨量高达 9 英寸。As much as 9 inches of rain fell in a 24-hour period across parts of coastal Volusia County.
2007-09-20 21:57:00.00000002007-09-20 21:57:00.0000000 2007-09-20 22:05:00.00000002007-09-20 22:05:00.0000000 龙卷风Tornado 佛罗里达州FLORIDA 龙卷风袭击了位于 West Crooked 湖北端的 Eustis 镇。A tornado touched down in the Town of Eustis at the northern end of West Crooked Lake. 该龙卷风迅速增强为 EF1 级别,并向西北偏北移动,穿过 Eustis。The tornado quickly intensified to EF1 strength as it moved north northwest through Eustis. 其移动轨迹将近 2 英里长,最大宽度为 300 码。The track was just under two miles long and had a maximum width of 300 yards. 龙卷风摧毁了 7 座房屋。The tornado destroyed 7 homes. 27 所房屋受到严重破坏,81 所房屋报告有轻微损坏。Twenty seven homes received major damage and 81 homes reported minor damage. 没有发生严重的人员伤亡,财产损失估计为 620 万美元。There were no serious injuries and property damage was set at $6.2 million.
2007-09-29 08:11:00.00000002007-09-29 08:11:00.0000000 2007-09-29 08:11:00.00000002007-09-29 08:11:00.0000000 海龙卷Waterspout 大西洋南部ATLANTIC SOUTH Melbourne 海滩东南的大西洋上形成了一个海龙卷,并短暂地向海岸移动。A waterspout formed in the Atlantic southeast of Melbourne Beach and briefly moved toward shore.
2007-12-20 07:50:00.00000002007-12-20 07:50:00.0000000 2007-12-20 07:53:00.00000002007-12-20 07:53:00.0000000 雷雨大风Thunderstorm Wind 密西西比州MISSISSIPPI 许多大树被吹倒,有些倒在电线上。Numerous large trees were blown down with some down on power lines. 东部 Adams 县遭到破坏。Damage occurred in eastern Adams county.
2007-12-30 16:00:00.00000002007-12-30 16:00:00.0000000 2007-12-30 16:05:00.00000002007-12-30 16:05:00.0000000 雷雨大风Thunderstorm Wind 佐治亚州GEORGIA 县政府报告说,在 206 国道附近的 Quincey Batten 环线上,有几棵树被吹倒。The county dispatch reported several trees were blown down along Quincey Batten Loop near State Road 206. 预估了树木移除费用。The cost of tree removal was estimated.

但是 take 不按特定顺序显示表中的行,所以现在对它们进行排序。But take shows rows from the table in no particular order, so let's sort them. limittake 的别名,具有相同的效果。)(limit is an alias for take and has the same effect.)

对结果排序:sort 和 top Order results: sort, top

  • 语法说明:某些运算符具有由关键字(如 by)引入的参数。Syntax note: Some operators have parameters that are introduced by keywords like by.
  • 在下面的示例中,desc 按降序对结果排序,asc 按升序对结果排序。In the following example, desc orders results in descending order and asc orders results in ascending order.

显示前 n 行,按指定列排序:Show me the first n rows, ordered by a specific column:

StormEvents
| top 5 by StartTime desc
| project  StartTime, EndTime, EventType, State, EventNarrative  

输出如下:Here's the output:

StartTimeStartTime EndTimeEndTime EventTypeEventType 状态State EventNarrativeEventNarrative
2007-12-31 22:30:00.00000002007-12-31 22:30:00.0000000 2007-12-31 23:59:00.00000002007-12-31 23:59:00.0000000 冬季风暴Winter Storm 密歇根州MICHIGAN 这场大雪持续到元旦凌晨。This heavy snow event continued into the early morning hours on New Year's Day.
2007-12-31 22:30:00.00000002007-12-31 22:30:00.0000000 2007-12-31 23:59:00.00000002007-12-31 23:59:00.0000000 冬季风暴Winter Storm 密歇根州MICHIGAN 这场大雪持续到元旦凌晨。This heavy snow event continued into the early morning hours on New Year's Day.
2007-12-31 22:30:00.00000002007-12-31 22:30:00.0000000 2007-12-31 23:59:00.00000002007-12-31 23:59:00.0000000 冬季风暴Winter Storm 密歇根州MICHIGAN 这场大雪持续到元旦凌晨。This heavy snow event continued into the early morning hours on New Year's Day.
2007-12-31 23:53:00.00000002007-12-31 23:53:00.0000000 2007-12-31 23:53:00.00000002007-12-31 23:53:00.0000000 疾风High Wind CALIFORNIACALIFORNIA 据报道,Ventura 县的山区有北到东北阵风,风速约为每小时 58 英里。North to northeast winds gusting to around 58 mph were reported in the mountains of Ventura county.
2007-12-31 23:53:00.00000002007-12-31 23:53:00.0000000 2007-12-31 23:53:00.00000002007-12-31 23:53:00.0000000 疾风High Wind CALIFORNIACALIFORNIA 温泉 RAWS 传感器报告说,北风达到每小时 58 英里。The Warm Springs RAWS sensor reported northerly winds gusting to 58 mph.

你可以先使用 sort,然后使用 take 来获得相同的结果:You can achieve the same result by using sort, and then take:

StormEvents
| sort by StartTime desc
| take 5
| project  StartTime, EndTime, EventType, EventNarrative

计算派生列:extendCompute derived columns: extend

通过计算每行中的值来创建新列:Create a new column by computing a value in every row:

StormEvents
| limit 5
| extend Duration = EndTime - StartTime 
| project StartTime, EndTime, Duration, EventType, State

输出如下:Here's the output:

StartTimeStartTime EndTimeEndTime 持续时间Duration EventTypeEventType 状态State
2007-09-18 20:00:00.00000002007-09-18 20:00:00.0000000 2007-09-19 18:00:00.00000002007-09-19 18:00:00.0000000 22:00:0022:00:00 暴雨Heavy Rain 佛罗里达州FLORIDA
2007-09-20 21:57:00.00000002007-09-20 21:57:00.0000000 2007-09-20 22:05:00.00000002007-09-20 22:05:00.0000000 00:08:0000:08:00 龙卷风Tornado 佛罗里达州FLORIDA
2007-09-29 08:11:00.00000002007-09-29 08:11:00.0000000 2007-09-29 08:11:00.00000002007-09-29 08:11:00.0000000 00:00:0000:00:00 海龙卷Waterspout 大西洋南部ATLANTIC SOUTH
2007-12-20 07:50:00.00000002007-12-20 07:50:00.0000000 2007-12-20 07:53:00.00000002007-12-20 07:53:00.0000000 00:03:0000:03:00 雷雨大风Thunderstorm Wind 密西西比州MISSISSIPPI
2007-12-30 16:00:00.00000002007-12-30 16:00:00.0000000 2007-12-30 16:05:00.00000002007-12-30 16:05:00.0000000 00:05:0000:05:00 雷雨大风Thunderstorm Wind 佐治亚州GEORGIA

可重复使用列名称并将计算结果分配给同一列。It's possible to reuse a column name and assign a calculation result to the same column.

示例:Example:

print x=1
| extend x = x + 1, y = x
| extend x = x + 1

输出如下:Here's the output:

xx yy
33 11

标量表达式可包含所有常用运算符(+-*/%),且可使用一系列有用的函数。Scalar expressions can include all the usual operators (+, -, *, /, %), and a range of useful functions are available.

聚合行组:summarizeAggregate groups of rows: summarize

统计每个州发生的事件数:Count the number of events occur in each state:

StormEvents
| summarize event_count = count() by State

summarize 将在 by 子句中具有相同值的行组合在一起,然后使用聚合函数(如 count)将每个组合并为单个行。summarize groups together rows that have the same values in the by clause, and then uses an aggregation function (for example, count) to combine each group in a single row. 在这种情况下,每个州都有相应的行,还有一个列,用来表示该州的行计数。In this case, there's a row for each state and a column for the count of rows in that state.

有一系列聚合函数可供使用。A range of aggregation functions are available. 可以在一个 summarize 运算符中使用多个聚合函数,以生成多个计算列。You can use several aggregation functions in one summarize operator to produce several computed columns. 例如,我们可以获得每个州的风暴数,以及每个州独特风暴类型的总和。For example, we could get the count of storms in each state and also a sum of a unique type of storms per state. 然后,可以使用 top 来获取受风暴影响最大的州:Then, we could use top to get the most storm-affected states:

StormEvents 
| summarize StormCount = count(), TypeOfStorms = dcount(EventType) by State
| top 5 by StormCount desc

输出如下:Here's the output:

状态State StormCountStormCount TypeOfStormsTypeOfStorms
德克萨斯TEXAS 47014701 2727
KANSASKANSAS 31663166 2121
衣阿华州IOWA 23372337 1919
ILLINOISILLINOIS 20222022 2323
MISSOURIMISSOURI 20162016 2020

summarize 运算符的结果中:In the results of a summarize operator:

  • 每个列都命名为 byEach column is named in by.
  • 每个计算表达式都有一个列。Each computed expression has a column.
  • by 值的每个组合都有一个行。Each combination of by values has a row.

按标量值汇总Summarize by scalar values

可在 by 子句中使用标量(数字、时间或间隔)值,但建议使用 bin() 函数将值放入箱中:You can use scalar (numeric, time, or interval) values in the by clause, but you'll want to put the values into bins by using the bin() function:

StormEvents
| where StartTime > datetime(2007-02-14) and StartTime < datetime(2007-02-21)
| summarize event_count = count() by bin(StartTime, 1d)

查询将所有时间戳缩短为一天的间隔:The query reduces all the timestamps to intervals of one day:

StartTimeStartTime event_countevent_count
2007-02-14 00:00:00.00000002007-02-14 00:00:00.0000000 180180
2007-02-15 00:00:00.00000002007-02-15 00:00:00.0000000 6666
2007-02-16 00:00:00.00000002007-02-16 00:00:00.0000000 164164
2007-02-17 00:00:00.00000002007-02-17 00:00:00.0000000 103103
2007-02-18 00:00:00.00000002007-02-18 00:00:00.0000000 2222
2007-02-19 00:00:00.00000002007-02-19 00:00:00.0000000 5252
2007-02-20 00:00:00.00000002007-02-20 00:00:00.0000000 6060

bin() 在许多语言中与 floor() 函数等效。The bin() is the same as the floor() function in many languages. 它简单地将每个值缩减为最接近提供的系数的倍数,这样 summarize 便可以将行分配给组。It simply reduces every value to the nearest multiple of the modulus that you supply, so that summarize can assign the rows to groups.

显示图表或表:renderDisplay a chart or table: render

可投射两个列,并将它们用作图表的 x 轴和 y 轴:You can project two columns and use them as the x-axis and the y-axis of a chart:

StormEvents 
| summarize event_count=count(), mid = avg(BeginLat) by State 
| sort by mid
| where event_count > 1800
| project State, event_count
| render columnchart

显示按州列出风暴事件计数的柱形图的屏幕截图。

尽管我们在 project 操作中删除了 mid,但如果我们希望图表按该顺序显示州,则仍需要它。Although we removed mid in the project operation, we still need it if we want the chart to display the states in that order.

严格来说,render 是客户端的一项功能,而不是查询语言的功能。Strictly speaking, render is a feature of the client rather than part of the query language. 不过,查询语言还是集成了该功能,该功能对于预想结果非常有用。Still, it's integrated into the language, and it's useful for envisioning your results.

时间图表Timecharts

回到数字箱,显示一个时序:Going back to numeric bins, let's display a time series:

StormEvents
| summarize event_count=count() by bin(StartTime, 1d)
| render timechart

按时间量化的事件折线图的屏幕截图。

多个序列Multiple series

summarize by 子句中使用多个值可为值的每个组合创建单独的行:Use multiple values in a summarize by clause to create a separate row for each combination of values:

StormEvents 
| where StartTime > datetime(2007-06-04) and StartTime < datetime(2007-06-10) 
| where Source in ("Source","Public","Emergency Manager","Trained Spotter","Law Enforcement")
| summarize count() by bin(StartTime, 10h), Source

显示按源计数的表的屏幕截图。

只需将 render 术语添加到前面的示例中:| render timechartJust add the render term to the preceding example: | render timechart.

显示按源计数的折线图的屏幕截图。

注意,render timechart 使用第一列作为 x 轴,然后将其他列显示为单独的行。Notice that render timechart uses the first column as the x-axis, and then displays the other columns as separate lines.

每日平均周期Daily average cycle

平均一天的活动如何变化?How does activity vary over the average day?

按一天的时间取模对事件计数,将其量化到小时。Count events by the time modulo one day, binned into hours. 此处,我们使用 floor 而不是 binHere, we use floor instead of bin:

StormEvents
| extend hour = floor(StartTime % 1d , 1h)
| summarize event_count=count() by hour
| sort by hour asc
| render timechart

显示按小时计数的时间表的屏幕截图。

目前,render 无法正确标记持续时间,但我们可以使用 | render columnchart 代替:Currently, render doesn't label durations properly, but we could use | render columnchart instead:

显示按小时计数的柱形图的屏幕截图。

比较多个每日系列Compare multiple daily series

不同州一天中不同时间的活动如何变化?How does activity vary over the time of day in different states?

StormEvents
| extend hour= floor( StartTime % 1d , 1h)
| where State in ("GULF OF MEXICO","MAINE","VIRGINIA","WISCONSIN","NORTH DAKOTA","NEW JERSEY","OREGON")
| summarize event_count=count() by hour, State
| render timechart

按小时和州计数的时间表的屏幕截图。

除以 1h 以将 x 轴转换为小时数,而不是持续时间:Divide by 1h to turn the x-axis into an hour number instead of a duration:

StormEvents
| extend hour= floor( StartTime % 1d , 1h)/ 1h
| where State in ("GULF OF MEXICO","MAINE","VIRGINIA","WISCONSIN","NORTH DAKOTA","NEW JERSEY","OREGON")
| summarize event_count=count() by hour, State
| render columnchart

显示按小时和州计数的柱形图的屏幕截图。

联接数据类型Join data types

如何查找两个特定的事件类型以及它们分别发生在哪个州?How would you find two specific event types and in which state each of them happened?

可以使用第一个 EventType 和第二个 EventType 拉取风暴事件,然后在 State 上联接这两个集:You can pull storm events with the first EventType and the second EventType, and then join the two sets on State:

StormEvents
| where EventType == "Lightning"
| join (
    StormEvents 
    | where EventType == "Avalanche"
) on State  
| distinct State

显示联接闪电和雪崩事件的屏幕截图。

联接的用户会话示例User session example of join

此部分不使用 StormEvents 表。This section doesn't use the StormEvents table.

假设你有一些描述事件的数据,这些事件使用每个会话的唯一 ID 标记每个用户会话的开始和结束时间。Assume you have data that includes events that mark the start and end of each user session with a unique ID for each session.

如何确定每个用户会话的持续时间?How would you find out how long each user session lasts?

可以使用 extend 为这两个时间戳提供别名,然后计算会话持续时间:You can use extend to provide an alias for the two timestamps, and then compute the session duration:

Events
| where eventName == "session_started"
| project start_time = timestamp, stop_time, country, session_id
| join ( Events
    | where eventName == "session_ended"
    | project stop_time = timestamp, session_id
    ) on session_id
| extend duration = stop_time - start_time
| project start_time, stop_time, country, duration
| take 10

用户会话扩展的结果表的屏幕截图。

执行联接前,最好使用 project 以只选择所需的列。It's a good practice to use project to select only the columns you need before you perform the join. 在相同子句中,重命名 timestamp 列。In the same clauses, rename the timestamp column.

绘制分布图Plot a distribution

返回到 StormEvents 表,其中有多少不同时长的风暴?Returning to the StormEvents table, how many storms are there of different lengths?

StormEvents
| extend  duration = EndTime - StartTime
| where duration > 0s
| where duration < 3h
| summarize event_count = count()
    by bin(duration, 5m)
| sort by duration asc
| render timechart

按持续时间计数的事件结果时间表的屏幕截图。

或者,你可以使用 | render columnchartOr, you can use | render columnchart:

按持续时间计数的事件的柱状图的屏幕截图。

百分位数Percentiles

在不同百分比的风暴中,我们发现了哪些持续时间范围?What ranges of durations do we find in different percentages of storms?

若要获取此信息,请使用上述查询,但将 render 替换为以下内容:To get this information, use the preceding query, but replace render with:

| summarize percentiles(duration, 5, 20, 50, 80, 95)

在此示例中,我们没有使用 by 子句,因此输出是单个行:In this case, we didn't use a by clause, so the output is a single row:

按持续时间计数的汇总百分位数的结果表的屏幕截图。

从输出中可以看到:We can see from the output that:

  • 有 5% 的风暴的持续时间不超过 5 分钟。5% of storms have a duration of less than 5 minutes.
  • 有 50% 的风暴的持续时间不超过 1小时 25 分钟。50% of storms lasted less than one hour and 25 minutes.
  • 有 95% 的风暴的持续时间不超过 2 小时 50 分钟。95% of storms lasted less than two hours and 50 minutes.

若要获取每个州的单独明细,请分别将 state 列与两个 summarize 运算符一起使用:To get a separate breakdown for each state, use the state column separately with both summarize operators:

StormEvents
| extend  duration = EndTime - StartTime
| where duration > 0s
| where duration < 3h
| summarize event_count = count()
    by bin(duration, 5m), State
| sort by duration asc
| summarize percentiles(duration, 5, 20, 50, 80, 95) by State

按州计数的汇总百分位数持续时间表。

将结果分配给变量:letAssign a result to a variable: let

使用 let 分离出上一个 join 示例中查询表达式的各部分。Use let to separate out the parts of the query expression in the preceding join example. 结果不变:The results are unchanged:

let LightningStorms = 
    StormEvents
    | where EventType == "Lightning";
let AvalancheStorms = 
    StormEvents
    | where EventType == "Avalanche";
LightningStorms 
| join (AvalancheStorms) on State
| distinct State

提示

在 Kusto Explorer 中,若要执行整个查询,请不要在查询的各部分之间添加空白行。In Kusto Explorer, to execute the entire query, don't add blank lines between parts of the query.

合并查询中多个数据库中的数据Combine data from several databases in a query

在下面的查询中,Logs 表必须位于默认数据库中:In the following query, the Logs table must be in your default database:

Logs | where ...

若要访问其他数据库中的表,请使用以下语法:To access a table in a different database, use the following syntax:

database("db").Table

例如,如果你有名为 DiagnosticsTelemetry 的数据库,并且想要关联这两个表中的某些数据,则可使用以下查询(假设 Diagnostics 为默认数据库):For example, if you have databases named Diagnostics and Telemetry and you want to correlate some of the data in the two tables, you might use the following query (assuming Diagnostics is your default database):

Logs | join database("Telemetry").Metrics on Request MachineId | ...

如果默认数据库为 Telemetry,请使用以下查询:Use this query if your default database is Telemetry:

union Requests, database("Diagnostics").Logs | ...

前面的两个查询假定两个数据库都位于当前连接到的群集中。The preceding two queries assume that both databases are in the cluster you're currently connected to. 如果 Telemetry 数据库位于名为 TelemetryCluster.kusto.chinacloudapi.cn 的群集中,请使用以下查询:If the Telemetry database was in a cluster named TelemetryCluster.kusto.chinacloudapi.cn, to access it, use this query:

Logs | join cluster("TelemetryCluster").database("Telemetry").Metrics on Request MachineId | ...

备注

指定群集后,数据库是必需的。When the cluster is specified, the database is mandatory.

有关合并查询中多个数据库中的数据的详细信息,请参阅跨数据库查询For more information about combining data from several databases in a query, see cross-database queries.

后续步骤Next steps

了解 Kusto 查询语言的最佳方式是查看一些基本查询,从而“感受”这种语言。The best way to learn about the Kusto Query Language is to look at some basic queries to get a "feel" for the language. 这些查询类似于 Azure 数据资源管理器教程中所用的查询,只是使用的是 Azure Log Analytics 工作区中通用表中的数据。These queries are similar to queries that are used in the Azure Data Explorer tutorial, but they instead use data from common tables in an Azure Log Analytics workspace.

使用 Azure 门户中的 Log Analytics 运行这些查询。Run these queries by using Log Analytics in the Azure portal. Log Analytics 是一种可以用来编写日志查询的工具。Log Analytics is a tool you can use to write log queries. 使用 Azure Monitor 中的日志数据,然后评估日志查询结果。Use log data in Azure Monitor, and then evaluate log query results. 如果你不熟悉 Log Analytics,请完成 Log Analytics 教程If you aren't familiar with Log Analytics, complete the Log Analytics tutorial.

本教程中的所有查询都使用 Log Analytics 演示环境All queries in this tutorial use the Log Analytics demo environment. 你可以使用自己的环境,但可能不具有在此处使用的某些表。You can use your own environment, but you might not have some of the tables that are used here. 由于演示环境中的数据不是静态的,因此查询的结果可能与此处显示的结果略有不同。Because the data in the demo environment isn't static, the results of your queries might vary slightly from the results shown here.

统计行数Count rows

InsightsMetrics 表包含由见解(如用于 VM 的 Azure Monitor 和用于容器的 Azure Monitor)收集的性能数据。The InsightsMetrics table contains performance data that's collected by insights such as Azure Monitor for VMs and Azure Monitor for containers. 若要了解表的大小,请将其内容传递到只计算行数的运算符。To find out how large the table is, we'll pipe its content into an operator that simply counts the rows.

查询是数据源(通常是表名称),可以选择后跟一个或多个管道字符和一些表格运算符。A query is a data source (usually a table name), optionally followed by one or more pairs of the pipe character and some tabular operator. 在这种情况下,将返回 InsightsMetrics 表中的所有记录,然后将其发送到 count 运算符In this case, all records from the InsightsMetrics table are returned and then sent to the count operator. count 运算符显示结果,因为该运算符是查询中的最后一个命令。The count operator displays the results because the operator is the last command in the query.

InsightsMetrics | count

输出如下:Here's the output:

计数Count
1,263,1911,263,191

按布尔表达式筛选:whereFilter by Boolean expression: where

AzureActivity 表包含 Azure 活动日志中的条目,让你能够深入了解 Azure 中发生的任何订阅级别或管理组级事件。The AzureActivity table has entries from the Azure activity log, which provides insight into any subscription-level or management group-level events that occurred in Azure. 让我们仅查看特定周内的 Critical 条目。Let's see only Critical entries during a specific week.

where 运算符在 Kusto 查询语言中是通用的。The where operator is common in the Kusto Query Language. where 将筛选表中符合特定条件的行。where filters a table to rows that match specific criteria. 以下示例命令行使用多个命令。The following example uses multiple commands. 首先,查询检索表中的所有记录。First, the query retrieves all records for the table. 然后,它仅针对时间范围内的记录筛选数据。Then, it filters the data for only records that are in the time range. 最后,它仅针对具有 Critical 级别的记录筛选这些结果。Finally, it filters those results for only records that have a Critical level.

备注

除了使用 TimeGenerated 列在查询中指定筛选器外,还可以在 Log Analytics 中指定时间范围。In addition to specifying a filter in your query by using the TimeGenerated column, you can specify the time range in Log Analytics. 有关详细信息,请参阅 Azure Monitor Log Analytics 中的日志查询范围和时间范围For more information, see Log query scope and time range in Azure Monitor Log Analytics.

AzureActivity
| where TimeGenerated > datetime(10-01-2020) and TimeGenerated < datetime(10-07-2020)
| where Level == 'Critical'

显示 where 运算符示例结果的屏幕截图。

选择列的子集:projectSelect a subset of columns: project

使用 project 以只包含所需的列。Use project to include only the columns you want. 基于前面的示例,将输出限制为特定列:Building on the preceding example, let's limit the output to certain columns:

AzureActivity
| where TimeGenerated > datetime(10-01-2020) and TimeGenerated < datetime(10-07-2020)
| where Level == 'Critical'
| project TimeGenerated, Level, OperationNameValue, ResourceGroup, _ResourceId

显示 project 运算符示例结果的屏幕截图。

显示 n 行:take Show n rows: take

NetworkMonitoring 包含 Azure 虚拟网络的监视数据。NetworkMonitoring contains monitoring data for Azure virtual networks. 我们来使用 take 运算符查看该表中的十个随机示例行。Let's use the take operator to look at ten random sample rows in that table. take 不以任何特定顺序显示表中一定数量的行:The take shows a certain number of rows from a table in no particular order:

NetworkMonitoring
| take 10
| project TimeGenerated, Computer, SourceNetwork, DestinationNetwork, HighLatency, LowLatency

显示 take 运算符示例结果的屏幕截图。

对结果排序:sort 和 top Order results: sort, top

可先按时间进行排序,返回最新的五条记录,而不是随机记录:Instead of random records, we can return the latest five records by first sorting by time:

NetworkMonitoring
| sort by TimeGenerated desc
| take 5
| project TimeGenerated, Computer, SourceNetwork, DestinationNetwork, HighLatency, LowLatency

可改为使用 top 运算符来获取此确切结果:You can get this exact behavior by instead using the top operator:

NetworkMonitoring
| top 5 by TimeGenerated desc
| project TimeGenerated, Computer, SourceNetwork, DestinationNetwork, HighLatency, LowLatency

显示 top 运算符示例结果的屏幕截图。

计算派生列:extendCompute derived columns: extend

extend 运算符与 project 运算符相似,只不过它会添加到一组列中,而不是替换它们。The extend operator is similar to project, but it adds to the set of columns instead of replacing them. 你可使用这两个运算符基于每行上的计算创建一个新列。You can use both operators to create a new column based on a computation on each row.

Perf 表包含的性能数据是从运行 Log Analytics 代理的虚拟机中收集的。The Perf table has performance data that's collected from virtual machines that run the Log Analytics agent.

Perf
| where ObjectName == "LogicalDisk" and CounterName == "Free Megabytes"
| project TimeGenerated, Computer, FreeMegabytes = CounterValue
| extend FreeGigabytes = FreeMegabytes / 1000

显示 extend 运算符示例结果的屏幕截图。

聚合行组:summarizeAggregate groups of rows: summarize

summarize 运算符将在 by 子句中具有相同值的行组合在一起。The summarize operator groups together rows that have the same values in the by clause. 然后,它使用聚合函数(如 count)将每个组合并为单个行。Then, it uses an aggregation function like count to combine each group in a single row. 有一系列聚合函数可供使用。A range of aggregation functions are available. 可以在一个 summarize 运算符中使用多个聚合函数,以生成多个计算列。You can use several aggregation functions in one summarize operator to produce several computed columns.

SecurityEvent 表包含安全事件,如在受监视的计算机上启动的登录和进程。The SecurityEvent table contains security events like logons and processes that started on monitored computers. 可计算每台计算机上发生的每个级别的事件数。You can count how many events of each level occurred on each computer. 在此示例中,将为每个计算机和级别组合生成一个行。In this example, a row is produced for each computer and level combination. 包含事件计数的列。A column contains the count of events.

SecurityEvent
| summarize count() by Computer, Level

显示 summarize count 运算符示例结果的屏幕截图。

按标量值汇总Summarize by scalar values

可按标量值(如数字和时间值)进行聚合,但应使用 bin() 函数将行分组为不同的数据集。You can aggregate by scalar values like numbers and time values, but you should use the bin() function to group rows into distinct sets of data. 例如,如果按 TimeGenerated 进行聚合,则几乎每个时间值都有一行。For example, if you aggregate by TimeGenerated, you'll get a row for almost every time value. 使用 bin() 将这些值合并为小时或天。Use bin() to consolidate those values into hour or day.

InsightsMetrics 表包含由见解(如用于 VM 的 Azure Monitor 和用于容器的 Azure Monitor)收集的性能数据。The InsightsMetrics table contains performance data that's collected by insights such as Azure Monitor for VMs and Azure Monitor for containers. 以下查询显示多台计算机处理器每小时的平均使用率:The following query shows the hourly average processor utilization for multiple computers:

InsightsMetrics
| where Computer startswith "DC"
| where Namespace  == "Processor" and Name == "UtilizationPercentage"
| summarize avg(Val) by Computer, bin(TimeGenerated, 1h)

显示 avg 运算符示例结果的屏幕截图。

显示图表或表:renderDisplay a chart or table: render

render 运算符指定如何呈现查询的输出。The render operator specifies how the output of the query is rendered. 默认情况下,Log Analytics 将输出呈现为一个表。Log Analytics renders output as a table by default. 可以在运行查询后选择不同的图表类型。You can select different chart types after you run the query. render 运算符可用于通常首选特定图表类型的查询。The render operator is useful to include in queries in which a specific chart type usually is preferred.

以下示例显示单台计算机处理器每小时的平均使用率。The following example shows the hourly average processor utilization for a single computer. 它将输出呈现为时间表。It renders the output as a timechart.

InsightsMetrics
| where Computer == "DC00.NA.contosohotels.com"
| where Namespace  == "Processor" and Name == "UtilizationPercentage"
| summarize avg(Val) by Computer, bin(TimeGenerated, 1h)
| render timechart

显示 render 运算符示例结果的屏幕截图。

使用多个序列Work with multiple series

如果在 summarize by 子句中使用多个值,则图表将为每组值显示一个单独的序列:If you use multiple values in a summarize by clause, the chart displays a separate series for each set of values:

InsightsMetrics
| where Computer startswith "DC"
| where Namespace  == "Processor" and Name == "UtilizationPercentage"
| summarize avg(Val) by Computer, bin(TimeGenerated, 1h)
| render timechart

显示具有多个序列示例的 render 运算符结果的屏幕截图。

联接两个表中的数据Join data from two tables

如果需要在单个查询中检索两个表中的数据该怎么办?What if you need to retrieve data from two tables in a single query? 可使用 join 运算符将多个表中的行合并到一个结果集中。You can use the join operator to combine rows from multiple tables in a single result set. 每个表都必须有一个具有匹配值的列,以便 join 识别要匹配的行。Each table must have a column that has a matching value so that the join understands which rows to match.

VMComputer 是 Azure Monitor 用于 VM 的表,用于存储它所监视的虚拟机的详细信息。VMComputer is a table that Azure Monitor uses for VMs to store details about virtual machines that it monitors. InsightsMetrics 包含从这些虚拟机收集的性能数据。InsightsMetrics contains performance data that's collected from those virtual machines. InsightsMetrics 中收集的一个值是可用内存,但不是可用的内存百分比。One value collected in InsightsMetrics is available memory, but not the percentage memory that's available. 若要计算百分比,需要每个虚拟机的物理内存。To calculate the percentage, we need the physical memory for each virtual machine. 该值位于 VMComputerThat value is in VMComputer.

下面的示例查询使用联接来执行此计算。The following example query uses a join to perform this calculation. distinct 运算符与 VMComputer 一起使用,因为会定期从每台计算机中收集详细信息。The distinct operator is used with VMComputer because details are regularly collected from each computer. 因此,会为该表中的每台计算机创建多个行。As result, multiple rows are created for each computer in the table. 使用 Computer 列联接两个表。The two tables are joined by using the Computer column. 在结果集中创建一个行,其中对于 InsightsMetrics 中的每一行,包括两个表中的列,并且 Computer 中的值与 VMComputerComputer 列中的相同值匹配。A row is created in the result set that includes columns from both tables for each row in InsightsMetrics, with a value in Computer that matches the same value in the Computer column in VMComputer.

VMComputer
| distinct Computer, PhysicalMemoryMB
| join kind=inner (
    InsightsMetrics
    | where Namespace == "Memory" and Name == "AvailableMB"
    | project TimeGenerated, Computer, AvailableMemoryMB = Val
) on Computer
| project TimeGenerated, Computer, PercentMemory = AvailableMemoryMB / PhysicalMemoryMB * 100

显示 join 运算符示例结果的屏幕截图。

将结果分配给变量:letAssign a result to a variable: let

使用 let 使查询更易于读取和管理。Use let to make queries easier to read and manage. 可使用此运算符将查询结果分配给以后可以使用的变量。You can use this operator to assign the results of a query to a variable that you can use later. 使用 let 语句,可以将前面示例中的查询重新编写为:By using the let statement, the query in the preceding example can be rewritten as:

let PhysicalComputer = VMComputer
    | distinct Computer, PhysicalMemoryMB;
    let AvailableMemory = 
InsightsMetrics
    | where Namespace == "Memory" and Name == "AvailableMB"
    | project TimeGenerated, Computer, AvailableMemoryMB = Val;
PhysicalComputer
| join kind=inner (AvailableMemory) on Computer
| project TimeGenerated, Computer, PercentMemory = AvailableMemoryMB / PhysicalMemoryMB * 100

显示 let 运算符示例结果的屏幕截图。

后续步骤Next steps