Sentinel Watchlist and KQL query

[Jeff Walzer]

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 

[#2 · Accepted solution]

@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

[#3]

@Gary Bushey - 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 

[#4]

@Gary Bushey 

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

[#5]

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

[#6]

@Gary Bushey 

Thx again for the reply.

I used this statement to alias User_Acccount_ to "User Account"

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

and my final working KQL queries look like this:

let watchlist = (_GetWatchlist('VIP') | project 'User Principal Name');
SecurityAlert
| extend User_Account_ = tostring(parse_json(ExtendedProperties).["User Account"])
| where 'User Principal Name' in (watchlist)

and

//Watchlist as a variable
let watchlist = (_GetWatchlist('VIP') | project 'User Principal Name');
SigninLogs
| where 'User Principal Name' in (watchlist)
| where isnotempty(ResultDescription)
| project TimeGenerated, UserPrincipalName, ResultDescription, Identity, Location, AppDisplayName