在 Azure Monitor 中编写高级查询Writing advanced queries in Azure Monitor

备注

在完成本课程之前,应先完成 Azure Monitor Log Analytics 入门查询入门You should complete Get started with Azure Monitor Log Analytics 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.

通过 let 重用代码Reusing code with let

使用 let 将结果分配给变量,并稍后在查询中引用它:Use let to assign results to a variable, and refer to it later in the query:

// get all events that have level 2 (indicates warning level)
let warning_events=
Event
| where EventLevel == 2;
// count the number of warning events per computer
warning_events
| summarize count() by Computer 

也可向变量分配常数值。You can also assign constant values to variables. 它支持为每次执行查询时需要更改的字段设置参数。This supports a method to set up parameters for the fields that you need to change every time you execute the query. 根据需要修改这些参数。Modify those parameters as needed. 例如,要在给定的时间段计算可用磁盘空间和可用内存(按百分位数计):For example, to calculate the free disk space and free memory (in percentiles), in a given time window:

let startDate = datetime(2018-08-01T12:55:02);
let endDate = datetime(2018-08-02T13:21:35);
let FreeDiskSpace =
Perf
| where TimeGenerated between (startDate .. endDate)
| where ObjectName=="Logical Disk" and CounterName=="Free Megabytes"
| summarize percentiles(CounterValue, 50, 75, 90, 99);
let FreeMemory =
Perf
| where TimeGenerated between (startDate .. endDate)
| where ObjectName=="Memory" and CounterName=="Available MBytes Memory"
| summarize percentiles(CounterValue, 50, 75, 90, 99);
union FreeDiskSpace, FreeMemory

这样,即可在下次运行查询时更改结束时间的开头。This makes it easy to change the start of end time the next time you run the query.

本地函数和参数Local functions and parameters

使用 let 语句创建可在同一查询中使用的函数。Use let statements to create functions that can be used in the same query. 例如,定义一个采用日期/时间字段(按 UTC 格式)并将其转换为标准中国格式的函数。For example, define a function that takes a datetime field (in the UTC format) and converts it to a standard US format.

let utc_to_us_date_format = (t:datetime)
{
  strcat(getmonth(t), "/", dayofmonth(t),"/", getyear(t), " ",
  bin((t-1h)%12h+1h,1s), iff(t%24h<12h, "AM", "PM"))
};
Event 
| where TimeGenerated > ago(1h) 
| extend USTimeGenerated = utc_to_us_date_format(TimeGenerated)
| project TimeGenerated, USTimeGenerated, Source, Computer, EventLevel, EventData 

PrintPrint

print 将返回一个单列单行的表,其中显示计算结果。print will return a table with a single column and a single row, showing the result of a calculation. 这通常用于需要简单计算的情况。This is often used in cases where you need a simple calcuation. 例如,要在 PST 中查找当前时间并添加具有 EST 的列:For example, to find the current time in PST and add a column with EST:

print nowPst = now()-8h
| extend nowEst = nowPst+3h

DatatableDatatable

datatable 可以定义一组数据。datatable allows you to define a set of data. 你提供一个架构和一组值,然后将表传输到其他任何查询元素。You provide a schema and a set of values and then pipe the table into any other query elements. 例如,要创建 RAM 使用量表并计算每小时的平均值:For example to create a table of RAM usage and calculate their average value per hour:

datatable (TimeGenerated: datetime, usage_percent: double)
[
  "2018-06-02T15:15:46.3418323Z", 15.5,
  "2018-06-02T15:45:43.1561235Z", 20.2,
  "2018-06-02T16:16:49.2354895Z", 17.3,
  "2018-06-02T16:46:44.9813459Z", 45.7,
  "2018-06-02T17:15:41.7895423Z", 10.9,
  "2018-06-02T17:44:23.9813459Z", 24.7,
  "2018-06-02T18:14:59.7283023Z", 22.3,
  "2018-06-02T18:45:12.1895483Z", 25.4
]
| summarize avg(usage_percent) by bin(TimeGenerated, 1h)

创建查找表时,Datatable 构造也非常有用。Datatable constructs are also very useful when creating a lookup table. 例如,要将表数据(如事件 ID)从 SecurityEvent 表映射到其他位置列出的事件类型,请使用 datatable 创建包含事件类型的查找表,并将此数据表与 SecurityEvent 数据联接:For example, to map table data such as event IDs from the SecurityEvent table, to event types listed elsewhere, create a lookup table with the event types using datatable and join this datatable with SecurityEvent data:

let eventCodes = datatable (EventID: int, EventType:string)
[
    4625, "Account activity",
    4688, "Process action",
    4634, "Account activity",
    4672, "Access",
    4624, "Account activity",
    4799, "Access management",
    4798, "Access management",
    5059, "Key operation",
    4648, "A logon was attempted using explicit credentials",
    4768, "Access management",
    4662, "Other",
    8002, "Process action",
    4904, "Security event management",
    4905, "Security event management",
];
SecurityEvent
| where TimeGenerated > ago(1h) 
| join kind=leftouter (
  eventCodes
) on EventID
| project TimeGenerated, Account, AccountType, Computer, EventType

后续步骤Next steps

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