Sentinel Watchlist and KQL query

I created a Sentinel VIP user watchlist and would like to use the SecurityAlert logs

I have the following query:

SecurityAlert
| extend User_Account_ = tostring(parse_json(ExtendedProperties).["User Account"])

The VIP user watchlist uses User Principal Name as a field so how can I create an alias for the User_Account field to match the User Principal Name of the User VIP watchlist?

Thx

GaryBushey
Oct 06, 2021
Jeff Walzer Is there any reason you cannot just change the extend in line 2 to use the User Principal Name like:
| extend ['User Principal Name'] = tostring(parse_json(ExtendedProperties).["User Account"])

If you need to keep that User_Account variable you can do
| extend ['User Principal Name'] = User_Account

GaryBushey
Bronze Contributor
Oct 06, 2021
Jeff Walzer Is there any reason you cannot just change the extend in line 2 to use the User Principal Name like:
| extend ['User Principal Name'] = tostring(parse_json(ExtendedProperties).["User Account"])

If you need to keep that User_Account variable you can do
| extend ['User Principal Name'] = User_Account
- Jeff Walzer
  Iron Contributor
  Oct 07, 2021
  GaryBushey
  
  I went back to the Watchlist documentation (https://docs.microsoft.com/en-us/azure/sentinel/watchlists) and saw this:
  
  With that, I went back and rewrote the query to as follows:
  SecurityAlert | lookup kind=leftouter _GetWatchlist('VIP') on $left.CompromisedEntity == $right.SearchKey | project TimeGenerated, CompromisedEntity, AlertName, AlertSeverity, Description
  
  and I'm still seeing the same issue in which I see users who aren't on the VIP list
  - GaryBushey
    Bronze Contributor
    Oct 08, 2021
    I think you want to use inner rather than leftouter. From the KQL Documentation page: leftouter is used, which means all those rows will appear in the output with null values used for the missing values of RightTable columns added by the operator.
    
    While inner will omit the rows
- Jeff Walzer
  Iron Contributor
  Oct 07, 2021
  GaryBushey - thx for the rely
  
  So I now have the following query:
  
  let watchlist = (_GetWatchlist('VIP') | project 'User Principal Name'); SecurityAlert | extend ['User Principal Name'] = tostring(parse_json(ExtendedProperties).["User Account"]) | where 'User Principal Name' in (watchlist) | project TimeGenerated, ['User Principal Name']
  But when I run the query, I still see user names that aren't part of the VIP list
  
  Thx

Forum Discussion

Sentinel Watchlist and KQL query

Share

Resources