在Microsoft Sentinel中用KQL解析非统一JSON数组的问题

在Microsoft Sentinel的告警数据中，Entities列存储的是JSON格式数据，但每个告警的JSON字段结构不统一，目标字段位置各异，导致静态解析无法获取全部数据。此前使用coalesce逐个索引取值的KQL查询方法较为繁琐，且易遗漏数据。

示例告警JSON数据 #

[{
        "$id": "2",
        "MailboxPrimaryAddress": "user.one@org.com",
        "DisplayName": "User One",
        "Upn": "user.one@org.com",
        "AadId": "redacted",
        "CreatedTimeUtc": "2024-02-21T15:10:24.6466667",
        "ThreatAnalysisSummary": [{
                "AnalyzersResult": [],
                "Verdict": "Unknown",
                "AnalysisDate": "2024-02-21T15:10:24.6466667"
            }
        ],
        "LastVerdict": "Unknown",
        "Type": "mailbox"
    }, {
        "$id": "3",
        "MailboxPrimaryAddress": "user.two@org.com",
        "DisplayName": "User Two",
        "Upn": "user.two@org.com",
        "AadId": "redacted",
        "CreatedTimeUtc": "2024-02-21T15:10:24.6466667",
        "ThreatAnalysisSummary": [{
                "AnalyzersResult": [],
                "Verdict": "Unknown",
                "AnalysisDate": "2024-02-21T15:10:24.6466667"
            }
        ],
        "LastVerdict": "Unknown",
        "Type": "mailbox"
    }, {
        "$id": "4",
        "Recipient": "user.one@org.com",
        "Sender": "sender@domain.com",
        "P1Sender": "",
        "P1SenderDisplayName": "",
        "P1SenderDomain": "",
        "SenderIP": "redacted",
        "P2Sender": "sender@domain.com",
        "P2SenderDisplayName": "",
        "P2SenderDomain": "",
        "ReceivedDate": "2024-02-21T15:08:29",
        "NetworkMessageId": "redacted",
        "InternetMessageId": "redacted",
        "Subject": "redacted",
        "AntispamDirection": "Unknown",
        "DeliveryAction": "Unknown",
        "DeliveryLocation": "Unknown",
        "CreatedTimeUtc": "2024-02-21T15:10:24.6466667",
        "ThreatAnalysisSummary": [{
                "AnalyzersResult": [],
                "Verdict": "Unknown",
                "AnalysisDate": "2024-02-21T15:10:24.6466667"
            }
        ],
        "LastVerdict": "Unknown",
        "Type": "mailMessage"
    }
]

原使用的KQL查询 #

SecurityAlert
| where AlertName startswith "Phish"
| extend sendIP = coalesce(
tostring(parse_json(Entities)[1].SenderIP), 
tostring(parse_json(Entities)[2].SenderIP), 
tostring(parse_json(Entities)[3].SenderIP), 
tostring(parse_json(Entities)[4].SenderIP),
tostring(parse_json(Entities)[5].SenderIP),
tostring(parse_json(Entities)[6].SenderIP),
tostring(parse_json(Entities)[7].SenderIP)
)
| extend P1Sender = coalesce(
tostring(parse_json(Entities)[1].Sender),
tostring(parse_json(Entities)[2].Sender),
tostring(parse_json(Entities)[3].Sender),
tostring(parse_json(Entities)[4].Sender),
tostring(parse_json(Entities)[5].Sender),
tostring(parse_json(Entities)[6].Sender),
tostring(parse_json(Entities)[7].Sender)
)
| extend NetworkMessageID = coalesce(
tostring(parse_json(Entities)[1].NetworkMessageId),
tostring(parse_json(Entities)[2].NetworkMessageId),
tostring(parse_json(Entities)[3].NetworkMessageId),
tostring(parse_json(Entities)[4].NetworkMessageId),
tostring(parse_json(Entities)[5].NetworkMessageId),
tostring(parse_json(Entities)[6].NetworkMessageId)
)
| extend Subject = coalesce(
tostring(parse_json(Entities)[1].Subject),
tostring(parse_json(Entities)[2].Subject),
tostring(parse_json(Entities)[3].Subject),
tostring(parse_json(Entities)[4].Subject),
tostring(parse_json(Entities)[5].Subject),
tostring(parse_json(Entities)[6].Subject)
)

优化后的KQL查询 #

经提示使用mv-apply后，优化后的查询写法如下：

SecurityAlert
| where AlertName startswith "Phish"
| mv-apply todynamic(Entities) on (
    extend entity = parse_json(Entities)
    | project sendIP = entity.SenderIP, P1Sender = entity.Sender, NetworkMessageID = entity.NetworkMessageId, Subject = entity.Subject
)
| where isnotempty(sendIP)
| summarize count() by tostring(sendIP), tostring(P1Sender), tostring(NetworkMessageID), tostring(Subject)

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