CODEWITHSUNDEEP
kqlazure-sentinel
Stu
Stu

Reputation: 1

Sentinel KQL Query not projecting columns from leftouter join

I have a KQL query which works in Defender and returns all the data I need, however when I run it in Log Analytics in Sentinel, the IdentityInfo columns referenced by the leftouter join are not returned. I need this data to create custom emails from a Logic App

The KQL query is below. I need to return the GivenName, Surname and AccountUpn columns from IdentityInfo

 DeviceFileEvents
| where (tolower(FileName) endswith ".msi" or tolower(FileName) endswith ".exe")
| where SHA1 != ""
| where 
    // Edge
    InitiatingProcessFolderPath endswith @"windows\system32\browser_broker.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
| 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

Upvotes: 0

Views: 448

Answers (1)

Jahnavi
Jahnavi

Reputation: 7923

Check if the IdentityInfo table is existed in your Sentinel workspace. Make sure that the IdentityInfo database has been ingested into your workspace. Verify it using the below query:

Identiyinfo
| take 10

If there are no logs existed regarding the above table, it doesn't retrieve any output as shown.

enter image description here

If still the issue persists, try using union operator to combine the Device file events & identity info instead of join operator as shown.

union DeviceFileEvents, IdentityInfo
....your query here

Upvotes: 0

Related Questions