Azure Monitor 日志查询中的聚合Aggregations in Azure Monitor log queries

备注

在学习本课程之前,需完成 Analytics 门户入门查询入门You should complete Get started with the Analytics portal and Getting started with queries before completing this lesson.

备注

可以在自己的 Log Analytics 环境中完成此练习,也可以使用我们的演示环境,其中包含大量样本数据。You can work through this exercise in your own Log Analytics environment, or you can use our Demo environment, which includes plenty of sample data.

本文介绍 Azure Monitor 日志查询中的聚合函数,这些函数提供了有用的数据分析方式。This article describes aggregation functions in Azure Monitor log queries that offer useful ways to analyze your data. 这些函数都适用于 summarize 运算符,后者生成一个包含输入表中聚合结果的表。These functions all work with the summarize operator that produces a table with aggregated results of the input table.

计数Counts

计数count

在应用任一筛选器后,计算结果集中的行数。Count the number of rows in the result set after any filters are applied. 以下示例返回过去 30 分钟内 Perf 表中的总行数 。The following example returns the total number of rows in the Perf table from the last 30 minutes. 结果将在名为“count_”的列中返回,除非为其指定特定名称 :The result is returned in a column named count_ unless you assign it a specific name:

Perf
| where TimeGenerated > ago(30m) 
| summarize count()
Perf
| where TimeGenerated > ago(30m) 
| summarize num_of_records=count() 

时间表可视化效果可用于查看随时间变化的趋势:A timechart visualization can be useful to see a trend over time:

Perf 
| where TimeGenerated > ago(30m) 
| summarize count() by bin(TimeGenerated, 5m)
| render timechart

本例中输出以 5 分钟为间隔显示了 perf 记录计数趋势线:The output from this example shows the perf record count trendline in 5 minutes' intervals:

计数趋势

dcount, dcountifdcount, dcountif

使用 dcountdcountif 计算特定列中非重复值的数量。Use dcount and dcountif to count distinct values in a specific column. 以下查询计算过去一小时内发送检测信号的非重复计算机的数量:The following query evaluates how many distinct computers sent heartbeats in the last hour:

Heartbeat 
| where TimeGenerated > ago(1h) 
| summarize dcount(Computer)

要想只计算发送检测信号的 Linux 计算机,请使用 dcountifTo count only the Linux computers that sent heartbeats, use dcountif:

Heartbeat 
| where TimeGenerated > ago(1h) 
| summarize dcountif(Computer, OSType=="Linux")

计算子组Evaluating subgroups

要在数据中的子组上执行计数或其他聚合,请使用 by 关键字。To perform a count or other aggregations on subgroups in your data, use the by keyword. 例如,要计算每个国家/地区发送检测信号的非重复 Linux 计算机的数量:For example, to count the number of distinct Linux computers that sent heartbeats in each country/region:

Heartbeat 
| where TimeGenerated > ago(1h) 
| summarize distinct_computers=dcountif(Computer, OSType=="Linux") by RemoteIPCountry
RemoteIPCountryRemoteIPCountry distinct_computersdistinct_computers
中国China 1919

要分析更小的数据子组,请在 by 部分中添加其他列名称。To analyze even smaller subgroups of your data, add additional column names to the by section. 例如,想要根据每个 OSType 来计算来自每个国家/地区的非重复计算机的数量:For example, you might want to count the distinct computers from each country/region per OSType:

Heartbeat 
| where TimeGenerated > ago(1h) 
| summarize distinct_computers=dcountif(Computer, OSType=="Linux") by RemoteIPCountry, OSType

百分位数和方差Percentiles and Variance

计算数值时,通常的做法是使用 summarize avg(expression) 求其平均值。When evaluating numerical values, a common practice is to average them using summarize avg(expression). 平均值受到仅描述少数情况的极值的影响。Averages are affected by extreme values that characterize only a few cases. 要解决该问题,可使用敏感程度较低的函数,例如 medianvarianceTo address that issue, you can use less sensitive functions such as median or variance.

百分位数Percentile

要查找中值,请使用带值的 percentile 函数指定百分位数:To find the median value, use the percentile function with a value to specify the percentile:

Perf
| where TimeGenerated > ago(30m) 
| where CounterName == "% Processor Time" and InstanceName == "_Total" 
| summarize percentiles(CounterValue, 50) by Computer

此外,可指定不同的百分位数,以获取每一个的聚合结果:You can also specify different percentiles to get an aggregated result for each:

Perf
| where TimeGenerated > ago(30m) 
| where CounterName == "% Processor Time" and InstanceName == "_Total" 
| summarize percentiles(CounterValue, 25, 50, 75, 90) by Computer

这可能会显示出一些计算机 CPU 具有相似的中值,但一些在中值附近稳定,另外一些计算机报告显示 CPU 值高出或低出很多(这意味着它们经历了峰值)。This might show that some computer CPUs have similar median values, but while some are steady around the median, other computers have reported much lower and higher CPU values meaning they experienced spikes.

VarianceVariance

要直接计算值的方差,请使用标准偏差和方差方法:To directly evaluate the variance of a value, use the standard deviation and variance methods:

Perf
| where TimeGenerated > ago(30m) 
| where CounterName == "% Processor Time" and InstanceName == "_Total" 
| summarize stdev(CounterValue), variance(CounterValue) by Computer

要分析 CPU 使用量的稳定性,一种很好的方式是将 stdev 与中值计算相结合:A good way to analyze the stability of the CPU usage is to combine stdev with the median calculation:

Perf
| where TimeGenerated > ago(130m) 
| where CounterName == "% Processor Time" and InstanceName == "_Total" 
| summarize stdev(CounterValue), percentiles(CounterValue, 50) by Computer

请参阅有关将 Kusto 查询语言与 Azure Monitor 日志数据配合使用的其他课程:See other lessons for using the Kusto query language with Azure Monitor log data: