Forum Discussion
I'm stuck!
- Dec 08, 2025
The issue occurs because _GetWatchlist() returns a table, not a dynamic array.
Operators such as has_any() expect a dynamic list and therefore cannot evaluate a table directly.
To correctly compare your extracted GroupName value with entries in a watchlist, you must:- Project the column containing the group names
- Use the in (...) operator for matching
This is the supported and recommended pattern for Sentinel watchlist lookups.
Assuming your watchlist is named TestList and the group names are stored in the default SearchKey column, the working solution is:
// Load watchlist and select the group-name column let PrivGroups = _GetWatchlist('TestList') | project GroupName = tostring(SearchKey); // Main query: detect Entra ID group membership additions for watchlisted groups AuditLogs | where TimeGenerated > ago(7d) | where OperationName == "Add member to group" | where TargetResources[0].type == "User" | extend GroupName = tostring( parse_json( tostring( parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue ) ) ) | where GroupName in (PrivGroups) // <-- Filter using the watchlist values | extend User = tostring(TargetResources[0].userPrincipalName) | summarize ['Count of Users Added'] = dcount(User), ['List of Users Added'] = make_set(User) by GroupName | sort by GroupName asc
The issue occurs because _GetWatchlist() returns a table, not a dynamic array.
Operators such as has_any() expect a dynamic list and therefore cannot evaluate a table directly.
To correctly compare your extracted GroupName value with entries in a watchlist, you must:
- Project the column containing the group names
- Use the in (...) operator for matching
This is the supported and recommended pattern for Sentinel watchlist lookups.
Assuming your watchlist is named TestList and the group names are stored in the default SearchKey column, the working solution is:
// Load watchlist and select the group-name column
let PrivGroups =
_GetWatchlist('TestList')
| project GroupName = tostring(SearchKey);
// Main query: detect Entra ID group membership additions for watchlisted groups
AuditLogs
| where TimeGenerated > ago(7d)
| where OperationName == "Add member to group"
| where TargetResources[0].type == "User"
| extend GroupName = tostring(
parse_json(
tostring(
parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue
)
)
)
| where GroupName in (PrivGroups) // <-- Filter using the watchlist values
| extend User = tostring(TargetResources[0].userPrincipalName)
| summarize
['Count of Users Added'] = dcount(User),
['List of Users Added'] = make_set(User)
by GroupName
| sort by GroupName asc
GoXATAKAN - it's people like you that promote the best of the internet! Thanks so much!
Importantly, I've learnt something from your response and it all working and was able to add "the cherry" which was to pull the "initatedby" entry into the summary- really appreciate it! 🙌