Forum Discussion
Sentinel Watchlist and KQL query
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 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_AccountI 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, Descriptionand I'm still seeing the same issue in which I see users who aren't on the VIP list
- 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 rowsThx 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
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
