按主机名查询Log Analytics动态用户登录信息问题

问题背景 #

我有一个Log Analytics表，其中包含名为UserLoggingInfo的动态列，用于存储各主机（HostName）的用户登录信息，存在三种数据形态：

• 单主机对应单个用户（如bill、bob）
• 单主机无用户（值为null）
• 单主机对应多个用户（如johndoe、janedoe）

原查询代码 #

datatable(timegenerated:datetime, hostname:string, userlogginginfo:dynamic)
[
    '2023-10-05T08:00:06', 'server-01', dynamic({
        "UserName": "bill",
        "IdleTime": "01:01:00",
        "LogonTime": "2023-10-05T00:23:00.0000000Z"
    }),
    '2023-10-05T08:00:17', 'server-02', dynamic({
        "UserName": "bob",
        "IdleTime": "00:10:00",
        "LogonTime": "2023-10-05T00:24:00.0000000Z"
    }),
    '2023-10-05T08:00:28', 'server-03', dynamic(
        null
    ),
    '2023-10-05T08:00:29', 'server-04', dynamic([
        {
            "UserName": "johndoe",
            "IdleTime": "2.09:51:00",
            "LogonTime": "2023-09-30T10:45:00.0000000Z"
        },
        {
            "UserName": "janedoe",
            "IdleTime": "00:36:00",
            "LogonTime": "2023-10-05T07:23:00.0000000Z"
        }
    ])
]
| evaluate bag_unpack(userlogginginfo)
| extend UserName = userlogginginfo.UserName, IdleTime = userlogginginfo.IdleTime, LogonTime = userlogginginfo.LogonTime

原查询问题 #

• server-03的空记录未被过滤
• server-04的多用户数组未被正确展开，无法展示两条独立记录
• 仅前两条单用户记录返回正确

期望结果 #

得到4条有效记录，展示指定字段并排除空值：

修正后的查询代码 #

datatable(timegenerated:datetime, hostname:string, userlogginginfo:dynamic)
[
    '2023-10-05T08:00:06', 'server-01', dynamic({
        "UserName": "bill",
        "IdleTime": "01:01:00",
        "LogonTime": "2023-10-05T00:23:00.0000000Z"
    }),
    '2023-10-05T08:00:17', 'server-02', dynamic({
        "UserName": "bob",
        "IdleTime": "00:10:00",
        "LogonTime": "2023-10-05T00:24:00.0000000Z"
    }),
    '2023-10-05T08:00:28', 'server-03', dynamic(null),
    '2023-10-05T08:00:29', 'server-04', dynamic([
        {
            "UserName": "johndoe",
            "IdleTime": "2.09:51:00",
            "LogonTime": "2023-09-30T10:45:00.0000000Z"
        },
        {
            "UserName": "janedoe",
            "IdleTime": "00:36:00",
            "LogonTime": "2023-10-05T07:23:00.0000000Z"
        }
    ])
]
// 过滤无用户的空记录
| where userlogginginfo != dynamic(null)
// 统一数据格式：将单个用户对象转为数组，确保和多用户数组处理逻辑一致
| extend userlogginginfo = iff(is_array(userlogginginfo), userlogginginfo, pack_array(userlogginginfo))
// 展开数组为单条记录
| mv-expand userlogginginfo
// 拆移动态对象的字段为独立列
| evaluate bag_unpack(userlogginginfo)
// 整理输出字段
| project HostName = hostname, UserName, IdleTime, LogonTime

关键步骤说明 #

1. 过滤空值：通过where userlogginginfo != dynamic(null)直接剔除无用户的记录
2. 统一数据格式：用iff判断动态列类型，将单个用户对象转为数组，保证后续展开逻辑统一
3. 展开数组：mv-expand将多用户数组拆分为多条独立记录，每个用户对应一行
4. 拆分动态字段：bag_unpack把动态对象中的UserName、IdleTime、LogonTime转为单独列
5. 整理输出：project重命名字段并保留需要的列

内容的提问来源于stack exchange，提问作者Mr. Dr. Sushi