在 Azure Monitor 日志查询中使用日期时间值Working with date time values 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 how to work with date and time data in Azure Monitor log queries.

日期时间基本信息Date time basics

Kusto 查询语言主要具有两种与日期和时间相关的数据类型:日期/时间和时间跨度。The Kusto query language has two main data types associated with dates and times: datetime and timespan. 所有日期均以 UTC 表示。All dates are expressed in UTC. 尽管支持多种日期时间格式,但首选 ISO8601 格式。While multiple datetime formats are supported, the ISO8601 format is preferred.

时间跨度的表示形式是十进制后面接时间单位:Timespans are expressed as a decimal followed by a time unit:

简写shorthand 时间单位time unit
dd dayday
hh hourhour
mm minuteminute
ss second
msms 毫秒millisecond
微秒microsecond 微秒microsecond
时钟周期tick 纳秒nanosecond

可使用 todatetime 运算符强制转换字符串,从而创建日期/时间。Datetimes can be created by casting a string using the todatetime operator. 例如,要查看在特定时间范围内发送的 VM 检测信号,使用指定时间范围的 between 运算符。For example, to review the VM heartbeats sent in a specific timeframe, use the between operator to specify a time range.

Heartbeat
| where TimeGenerated between(datetime("2018-06-30 22:46:42") .. datetime("2018-07-01 00:57:27"))

还有一个常见的情况是将日期时间与当前时间进行比较。Another common scenario is comparing a datetime to the present. 例如,要查看过去两分钟内的所有检测信号,可结合使用 now 运算符和表示两分钟的时间跨度:For example, to see all heartbeats over the last two minutes, you can use the now operator together with a timespan that represents two minutes:

Heartbeat
| where TimeGenerated > now() - 2m

还可通过快捷方式使用此函数:A shortcut is also available for this function:

Heartbeat
| where TimeGenerated > now(-2m)

但是,最简短且可读性最强的方式是使用 ago 运算符:The shortest and most readable method though is using the ago operator:

Heartbeat
| where TimeGenerated > ago(2m)

假设你不知道开始和结束时间,但知道开始时间和持续时间。Suppose that instead of knowing the start and end time, you know the start time and the duration. 可按如下方式重写查询:You can rewrite the query as follows:

let startDatetime = todatetime("2018-06-30 20:12:42.9");
let duration = totimespan(25m);
Heartbeat
| where TimeGenerated between(startDatetime .. (startDatetime+duration) )
| extend timeFromStart = TimeGenerated - startDatetime

转换时间单位Converting time units

你可能希望以非默认时间单位表示日期/时间或时间跨度。You may want to express a datetime or timespan in a time unit other than the default. 例如,假设要检查过去 30 分钟内的错误事件,且需要一个显示事件发生时间的计算列:For example, if you're reviewing error events from the last 30 minutes and need a calculated column showing how long ago the event happened:

Event
| where TimeGenerated > ago(30m)
| where EventLevelName == "Error"
| extend timeAgo = now() - TimeGenerated 

timeAgo 列包含以下值:“00:09:31.5118992”,这意味着它们的格式为 hh:mm:ss.fffffff。The timeAgo column holds values such as: "00:09:31.5118992", meaning they're formatted as hh:mm:ss.fffffff. 如果要将这些值的格式设置为自开始时间以来的 numver 分钟数,则用该值除以“1 分钟”:If you want to format these values to the numver of minutes since the start time, divide that value by "1 minute":

Event
| where TimeGenerated > ago(30m)
| where EventLevelName == "Error"
| extend timeAgo = now() - TimeGenerated
| extend timeAgoMinutes = timeAgo/1m 

按时间间隔进行聚合和分桶Aggregations and bucketing by time intervals

还有一种常见的情况是,需要按特定时间粒度获取某一时间段内的统计数据。Another common scenario is the need to obtain statistics over a certain time period in a particular time grain. 为此,可在 summarize 子句中使用 bin 运算符。For this scenario, a bin operator can be used as part of a summarize clause.

使用以下查询获取过去半小时内每 5 分钟发生的事件数:Use the following query to get the number of events that occurred every 5 minutes during the last half hour:

Event
| where TimeGenerated > ago(30m)
| summarize events_count=count() by bin(TimeGenerated, 5m) 

此查询会生成以下表:This query produces the following table:

TimeGenerated(UTC)TimeGenerated(UTC) events_countevents_count
2018-08-01T09:30:00.0002018-08-01T09:30:00.000 5454
2018-08-01T09:35:00.0002018-08-01T09:35:00.000 4141
2018-08-01T09:40:00.0002018-08-01T09:40:00.000 4242
2018-08-01T09:45:00.0002018-08-01T09:45:00.000 4141
2018-08-01T09:50:00.0002018-08-01T09:50:00.000 4141
2018-08-01T09:55:00.0002018-08-01T09:55:00.000 1616

还可使用函数(例如 startofday)来创建结果存储桶:Another way to create buckets of results is to use functions, such as startofday:

Event
| where TimeGenerated > ago(4d)
| summarize events_count=count() by startofday(TimeGenerated) 

此查询会生成以下结果:This query produces the following results:

timestamptimestamp count_count_
2018-07-28T00:00:00.0002018-07-28T00:00:00.000 7,1367,136
2018-07-29T00:00:00.0002018-07-29T00:00:00.000 12,31512,315
2018-07-30T00:00:00.0002018-07-30T00:00:00.000 16,84716,847
2018-07-31T00:00:00.0002018-07-31T00:00:00.000 12,61612,616
2018-08-01T00:00:00.0002018-08-01T00:00:00.000 5,4165,416

时区Time zones

由于所有日期/时间值都以 UTC 表示,因此将这些值转换为本地时区通常很有用。Since all datetime values are expressed in UTC, it's often useful to convert these values into the local timezone. 例如,使用此计算将 UTC 转换为 PST 时间:For example, use this calculation to convert UTC to PST times:

Event
| extend localTimestamp = TimeGenerated - 8h
CategoryCategory 函数Function
转换数据类型Convert data types todatetime totimespantodatetime totimespan
将值舍入到箱大小Round value to bin size binbin
获取特定的日期或时间Get a specific date or time ago nowago now
获取部分值Get part of value datetime_part getmonth monthofyear getyear dayofmonth dayofweek dayofyear weekofyeardatetime_part getmonth monthofyear getyear dayofmonth dayofweek dayofyear weekofyear
获取相对日期值Get a relative date value endofday endofweek endofmonth endofyear startofday startofweek startofmonth startofyearendofday endofweek endofmonth endofyear startofday startofweek startofmonth startofyear

后续步骤Next steps

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