如何用KQL将同表两列合并为一列并提取邮件域名？

要把RecipientEmailAddress和SenderFromAddress合并到同一列，核心是用pack_array()把两列打包成动态数组，再用mv-expand将数组元素拆分为单独行。以下是修正后的完整查询：

let emaildomain = dynamic(['aaa', 'bbb']);
EmailEvents
| where RecipientEmailAddress in (emaildomain) or SenderFromDomain in (emaildomain)
// 将两列打包为数组，再展开为单行单列
| extend mail_addresses = pack_array(RecipientEmailAddress, SenderFromAddress)
| mv-expand mail_addresses to typeof(string)
// 过滤空值（避免某列无数据时产生空行）
| where isnotempty(mail_addresses)
// 提取域名部分
| extend domainnames = split(mail_addresses, '@')[1]
// 转换为字符串并去重，排除指定域名
| distinct tostring(domainnames)
| where domainnames !has "myCompany"

针对你简化后的查询，合并两列的版本如下：

let emaildomain = dynamic(['AAA.com']);
EmailEvents
| where RecipientEmailAddress in (emaildomain) or SenderFromDomain in (emaildomain)
| extend mail_addresses = pack_array(RecipientEmailAddress, SenderFromAddress)
| mv-expand mail_addresses to typeof(string)
| where isnotempty(mail_addresses)
| distinct mail_addresses

关键说明： #

• pack_array(col1, col2)：把两列的值打包成一个动态数组，每行包含两个元素（收件人、发件人地址）
• mv-expand：将数组中的每个元素拆分为独立行，实现两列数据合并到同一列
• to typeof(string)：确保展开后的数据类型为字符串，避免后续字符串操作报错
• isnotempty()：过滤掉空值行，避免无效数据干扰结果

内容的提问来源于stack exchange，提问作者kbo-eqs