Forum Discussion
I'm stuck!
Logically, I'm not sure how\if I can do this.
I want to monitor for EntraID Group additions - I can get this to work for a single entry using this:
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 == "NameOfGroup" <-- This returns the single entry
| 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
However, I have a list of 20 Priv groups that I need to monitor. I can do this using:
let PrivGroups = dynamic[('name1','name2','name3'});
and then call that like this:
blahblah
| where TargetResources[0].type == "User"
| extend GroupName = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))
| where GroupName has_any (PrivGroup)
But that's a bit dirty to update - I wanted to call a watchlist. I've tried defining with:
let PrivGroup = (_GetWatchlist('TestList'));
and tried calling like:
blahblah
| where TargetResources[0].type == "User"
| extend GroupName = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))
| where GroupName has_any ('PrivGroup')
I've tried dropping the let and attempted to lookup the watchlist directly:
| where GroupName has_any (_GetWatchlist('TestList'))
The query runs but doesn't return any results (Obvs I know the result exists) - How do I lookup that extracted value on a Watchlist.
Any ideas or pointers why I'm wrong would be appreciated!
Many thanks
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
2 Replies
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 ascGoXATAKAN - 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! 🙌
