Azure应用程序洞察Kusto语言汇总时生成值的位置（where Time Generated Value）

Question

有没有办法用Kusto语言将where子句放在不同的列中。我知道“pivot”语法，它也与SQL一起用于创建基于唯一值的列。但别以为这对我的案子有帮助。还有一个SO的问题几乎和我有同样的问题。但他的解决方案也不奏效。

我的查询的上下文：这个查询获取每个月每台机器的运行时。您可能想知道为什么我要用这么长的查询来实现这一点。欢迎您的任何意见和调整。我对这门语言很陌生。并且我已经使用top查询来获取另一个项目中每个VM的启动和停止时间。

原始查询：

AzureActivity
| where ResourceProvider == "Microsoft.Compute"
and ActivityStatus == "Succeeded"
and OperationName == "Deallocate Virtual Machine"
| project DeallocateResource=Resource
,DeallocatedDate=format_datetime(EventSubmissionTimestamp, 'yyyy-MM-dd')
,DeallocatedTime=format_datetime(EventSubmissionTimestamp, 'HH:mm:ss')
| join kind=fullouter (AzureActivity
| where ResourceProvider == "Microsoft.Compute"
and ActivityStatus == "Succeeded"
and OperationName == "Start Virtual Machine"
| project StartupResource=Resource
,StartDate=format_datetime(EventSubmissionTimestamp, 'yyyy-MM-dd')
,StartTime=format_datetime(EventSubmissionTimestamp, 'HH:mm:ss')
) on $right.StartupResource == $left.DeallocateResource
| where StartDate == DeallocatedDate
| project Resource=coalesce(StartupResource, DeallocateResource) ,
Runtime = round(todouble(datetime_diff('minute', todatetime(strcat(StartDate , " " , DeallocatedTime )) , todatetime(strcat(StartDate , " " , StartTime )))) / 60)
| summarize sum(Runtime) by Resource

| where TimeGenerated > ago(30d)
| where TimeGenerated between(ago(30d) .. ago(60d) )
| where TimeGenerated between(ago(60d) .. ago(90d) )

| summarize sum(Runtime) by Resource , bin(TimeGenerated, 1m)
| summarize Fistmonth = TimeGenerated > ago(30d),  
            SecondMonth = TimeGenerated between(ago(30d) .. ago(60d)) ,
            ThirdMonth = Runtime_,TimeGenerated between(ago(60d) .. ago(90d) ) by Resource

有没有人知道我在这里错过了什么。这对库斯托可能吗？并且我是否使用了一个开销的查询来完成一些可以在几行中完成的事情。

Answer 1

如果我正确地理解了您的场景，那么您可以使用sumif来实现这一目标，前提是您事先知道目标的月份。

这里有一个例子：

datatable(Resource:string, Runtime:double, TimeGenerated:datetime)
[
    "A", 13.4, datetime(2019-01-01 11:11:11),
    "B", 1.34, datetime(2019-01-01 10:10:10),
    "C", 0.13, datetime(2019-01-01 12:12:12),
    "A", 12.4, datetime(2019-02-01 11:11:11),
    "B", 1.24, datetime(2019-02-01 09:09:09),
    "B", 2.24, datetime(2019-02-01 09:10:09),
    "B", 3.24, datetime(2019-02-01 09:11:09),
    "C", 0.12, datetime(2019-02-01 08:08:08),
    "A", 14.4, datetime(2019-03-01 07:07:07),
    "B", 1.44, datetime(2019-03-01 05:05:05),
    "C", 0.14, datetime(2019-03-01 06:06:06),
]
| summarize Month1 = sumif(Runtime, TimeGenerated between(datetime(2019-01-01)..datetime(2019-02-01))),
            Month2 = sumif(Runtime, TimeGenerated between(datetime(2019-02-01)..datetime(2019-03-01))),
            Month3 = sumif(Runtime, TimeGenerated between(datetime(2019-03-01)..datetime(2019-04-01))) 
         by Resource