Sentinel - KQL Query for combining

Occasional Contributor



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 be only logging to specific servers. 

For example (just a random table)



| where user != john and computer != xxxxx1 (I don't want to see an alert if John logs into xxxxx1) but if John logs into xxxxx2, then I do want to see an alert for that)

| where user != anna and computer != xxxxx2 (same as above)


I do want to track those users logging into other servers but not the ones that they are supposed to be logging into.  Is there a way of doing this?



1 Reply
best response confirmed by GBushey (Microsoft)


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
| join kind=inner  
        //right Table
        | where DeviceDetail.operatingSystem =~ "Windows"
        | extend deviceId_ = tostring(DeviceDetail.displayName)
    ) on UserPrincipalName
// is it an allowed Device?    
| where ApprovedComputer != deviceId_
| project UserPrincipalName, deviceId_, ApprovedComputer