Sentinel - KQL Query for combining

Hello, I'm trying to figure out what's the best way of addressing an issue I have. I have an analytic rule. Some users are whitelisted in the rule. What I need to do is whitelist users what will ...

KQL

kusto

siem

Clive_Watson

Oct 11, 2022

idontknowanything

There could be a few ways of doing this, I'd go for a Watchlist if you need to make this scale, but as a quick demo I used a DataTable in the query (which works but you have to keep updating/releasing the Rule if you make changes).
Essentially you provide a UserName and a list of allowedServers in the Table or Watchlist.

let allowedServers =datatable(UserPrincipalName:string, ApprovedComputer:string)
[
"email address removed for privacy reasons","KQL1",
"email address removed for privacy reasons","KQL2"
];
// left Table
allowedServers
| join kind=inner  
    (
        //right Table
        SigninLogs
        | where DeviceDetail.operatingSystem =~ "Windows"
        | extend deviceId_ = tostring(DeviceDetail.displayName)
    ) on UserPrincipalName
// is it an allowed Device?    
| where ApprovedComputer != deviceId_
| project UserPrincipalName, deviceId_, ApprovedComputer

let allowedServers =datatable(UserPrincipalName:string, ApprovedComputer:string) [ "email address removed for privacy reasons","KQL1", "email address removed for privacy reasons","KQL2" ]; // left Table allowedServers | join kind=inner ( //right Table SigninLogs | where DeviceDetail.operatingSystem =~ "Windows" | extend deviceId_ = tostring(DeviceDetail.displayName) ) on UserPrincipalName // is it an allowed Device? | where ApprovedComputer != deviceId_ | project UserPrincipalName, deviceId_, ApprovedComputer

Forum Discussion

Sentinel - KQL Query for combining

Resources