如何在KQL中从动态数组查找并提取指定键值？

问题原因 #

AdditionalDetails字段是键值对结构的动态数组（每个元素格式为{"key": "字段名称", "value": "字段内容"}），并非扁平的JSON对象，因此直接通过AD.TicketSystem这种方式访问会返回空值，必须根据key字段匹配提取对应的value。

修正后的查询方案 #

以下是两种可行的修正方式，推荐第二种更易维护：

方式1：使用数组函数精准定位

通过array_find_index找到目标key的数组索引，再用array_element_at提取对应元素的value：

AuditLogs
| where TimeGenerated > now() - 7d
| where Category == 'RoleManagement'
| where TargetResources contains "Global Administrator"
  and OperationName in (
      "Add member to role completed (PIM activation)",
      "Add eligible member to role in PIM completed",
      "Add member to role outside of PIM",
      "Add member to role request approved (PIM activation)"
  )
| extend _InitiatedByUPN = tostring(InitiatedBy.user.userPrincipalName) // 原parse_json多余，userPrincipalName本身是字符串
| extend _TargetResources = parse_json(TargetResources)
| extend _Target_displayName = tostring(_TargetResources[0].displayName)
| extend _Target_type = tostring(_TargetResources[1].type)
// 提取TicketSystem和TicketNumber
| extend _AdditionalDetails = parse_json(AdditionalDetails)
| extend TicketSystem = tostring(array_element_at(_AdditionalDetails, array_find_index(_AdditionalDetails, x => x.key == "TicketSystem"))).value
| extend TicketNumber = tostring(array_element_at(_AdditionalDetails, array_find_index(_AdditionalDetails, x => x.key == "TicketNumber"))).value
| project 
    时间 = format_datetime(ActivityDateTime,'yyyy-MM-dd HH:mm:ss'),
    身份 = Identity,
    发起者UPN = _InitiatedByUPN,
    操作名称 = OperationName,
    目标角色名称 = _Target_displayName,
    结果原因 = ResultReason,
    TicketSystem,
    TicketNumber

方式2：使用mv-apply展开数组后透视（更直观）

将数组展开为单行键值对，再通过聚合转换为列：

AuditLogs
| where TimeGenerated > now() - 7d
| where Category == 'RoleManagement'
| where TargetResources contains "Global Administrator"
  and OperationName in (
      "Add member to role completed (PIM activation)",
      "Add eligible member to role in PIM completed",
      "Add member to role outside of PIM",
      "Add member to role request approved (PIM activation)"
  )
| extend _InitiatedByUPN = tostring(InitiatedBy.user.userPrincipalName)
| extend _TargetResources = parse_json(TargetResources)
| extend _Target_displayName = tostring(_TargetResources[0].displayName)
| extend _Target_type = tostring(_TargetResources[1].type)
// 展开AdditionalDetails数组并提取目标字段
| mv-apply AD = parse_json(AdditionalDetails) on (
    summarize TicketSystem = max_if(tostring(AD.value), AD.key == "TicketSystem"),
              TicketNumber = max_if(tostring(AD.value), AD.key == "TicketNumber")
)
| project 
    时间 = format_datetime(ActivityDateTime,'yyyy-MM-dd HH:mm:ss'),
    身份 = Identity,
    发起者UPN = _InitiatedByUPN,
    操作名称 = OperationName,
    目标角色名称 = _Target_displayName,
    结果原因 = ResultReason,
    TicketSystem,
    TicketNumber

额外优化点 #

1. 将OperationName contains替换为in，更高效且避免模糊匹配的误判
2. 移除了多余的parse_json：InitiatedBy.user.userPrincipalName本身就是字符串类型，无需再次解析
3. 给列名添加了中文别名，提升可读性

内容的提问来源于stack exchange，提问作者VlijmenFileer