在Microsoft Sentinel日志分析中KQL左外连接无法返回IdentityInfo列

问题分析 #

你的KQL在Defender中能正常返回IdentityInfo的列，但在Sentinel Log Analytics中丢失，大概率是以下原因：

• 后续的innerunique join操作覆盖或隐藏了之前关联的列
• Sentinel中IdentityInfo表的数据同步延迟或匹配键不匹配
• 未显式指定保留关联后的列，导致查询优化时被丢弃

解决方案 #

1. 显式保留并重命名IdentityInfo的列

在leftouter join后，用extend把需要的列重命名（加前缀），避免和后续表的列冲突：

DeviceFileEvents
| where (tolower(FileName) endswith ".msi" or tolower(FileName) endswith ".exe")
| where SHA1 != ""
| where 
    // Edge
    InitiatingProcessFolderPath endswith @"windows\system32\svchost.exe"  // 注：原代码此处有乱码，已修正为合理路径
    // Internet Explorer x64
    or InitiatingProcessFolderPath endswith @"program files\internet explorer\iexplore.exe"
    // Internet Explorer x32
    or InitiatingProcessFolderPath endswith @"program files (x86)\internet explorer\iexplore.exe"
    // Chrome
    or (InitiatingProcessFileName =~ "chrome.exe")
    // Firefox
    or (InitiatingProcessFileName =~ "firefox.exe" and (FileName !endswith ".js" or FolderPath !has "profile"))
| join kind=leftouter (IdentityInfo) on $left.RequestAccountName == $right.AccountName
// 显式保留IdentityInfo的列并加前缀，避免被后续join覆盖
| extend Identity_GivenName = GivenName, Identity_Surname = Surname, Identity_AccountUpn = AccountUpn
// 移除原IdentityInfo的重复列，防止冲突
| project-away GivenName, Surname, AccountUpn, AccountName1
| join kind=innerunique(DeviceProcessEvents  
    | where SHA1 != ""
    | where FileName contains ".exe" 
    | where (ProcessCommandLine contains ".exe") 
    )
    on $left.FileName == $right.FileName and $left.DeviceId == $right.DeviceId
| sort by TimeGenerated desc

2. 验证IdentityInfo在Sentinel中的数据可用性

先单独查询Sentinel中的IdentityInfo表，确认目标账号的列是否存在：

IdentityInfo
| where AccountName == "目标账号名"
| project AccountName, GivenName, Surname, AccountUpn

如果没有数据，说明Sentinel未同步该账号的身份信息，需要检查数据连接器或等待同步。

3. 调整join顺序（可选）

如果第二个join的优先级导致列丢失，可以先完成innerunique join，再关联IdentityInfo：

DeviceFileEvents
| where (tolower(FileName) endswith ".msi" or tolower(FileName) endswith ".exe")
| where SHA1 != ""
| where 
    // Edge
    InitiatingProcessFolderPath endswith @"windows\system32\svchost.exe"
    // Internet Explorer x64
    or InitiatingProcessFolderPath endswith @"program files\internet explorer\iexplore.exe"
    // Internet Explorer x32
    or InitiatingProcessFolderPath endswith @"program files (x86)\internet explorer\iexplore.exe"
    // Chrome
    or (InitiatingProcessFileName =~ "chrome.exe")
    // Firefox
    or (InitiatingProcessFileName =~ "firefox.exe" and (FileName !endswith ".js" or FolderPath !has "profile"))
| join kind=innerunique(DeviceProcessEvents  
    | where SHA1 != ""
    | where FileName contains ".exe" 
    | where (ProcessCommandLine contains ".exe") 
    )
    on $left.FileName == $right.FileName and $left.DeviceId == $right.DeviceId
// 移到最后关联IdentityInfo，减少列冲突
| join kind=leftouter (IdentityInfo) on $left.RequestAccountName == $right.AccountName
| sort by TimeGenerated desc

关键注意事项 #

• 原查询中Edge的路径存在乱码（windows\system3 ChooseFind因而 whenever尽b GeraOptional支Look"十字 strains Interlocked），已修正为合理的windows\system32\svchost.exe，请根据实际场景调整。
• 当多个join操作存在相同列名时，KQL会自动添加后缀（如AccountName1），显式重命名可以避免混淆。

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