azure-log-analytics - 库斯托 : How to unpivot - turn columns into rows?

Question

使用帮助集群上 Samples 数据库中的 StormEvents 表:

StormEvents
| where State startswith "AL"
| where EventType has "Wind"
| where StartTime == "2007-01-02T02:16:00Z"
| project StartTime, State, EventType, InjuriesDirect, InjuriesIndirect, DeathsDirect, DeathsIndirect

我想要表单的基于行的输出:

我看到了 pivot() 函数，但它似乎只向另一个方向发展，从行到列。

我一直在尝试各种 pack() 想法，但似乎无法获得所需的输出。

例子:

StormEvents
| where State startswith "AL"
| where EventType has "Wind"
| where StartTime == "2007-01-02T02:16:00Z"
| project StartTime, State, EventType, InjuriesDirect, InjuriesIndirect, DeathsDirect, DeathsIndirect
| extend Packed =   pack(
                    "CasualtyType", "InjuriesDirect", "CasualtyCount", InjuriesDirect,
                    "CasualtyType", "InjuriesIndirect", "CasualtyCount", InjuriesIndirect,
                    "CasualtyType", "DeathsDirect", "CasualtyCount", DeathsDirect,
                    "CasualtyType", "DeathsIndirect", "CasualtyCount", DeathsIndirect
                )
| project-away InjuriesDirect, InjuriesIndirect, DeathsDirect, DeathsIndirect
| mv-expand Packed

这给了我太多行，我不清楚如何将它们转换为列。

用于所需输出的正确模式是什么？

Answer 1

您可以尝试以下方法:

let casualty_types = dynamic(["InjuriesDirect", "DeathsDirect", "InjuriesIndirect", "DeathsIndirect"]);
StormEvents
| where State startswith "AL"
| where EventType has "Wind"
| where StartTime == "2007-01-02T02:16:00Z"
| project StartTime, State, EventType, properties = pack_all()
| mv-apply casualty_type = casualty_types to typeof(string) on (
    project casualty_type, casualty_count = tolong(properties[casualty_type])
)
| project-away properties