Forum Discussion
Solved
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...
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
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
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
While inner will omit the rows
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