Comparing Watchlist items with json object

[MiguelCuba]

I'm using watchlist as a whitelist in an analytic rule in sentinel but when i try to compare with a variable in auditlog named "AccountUPN" it still showing me users that are included in this watchlist. Also when I change the time range it shows me "Function used in this query is using a specific time definition". I dont know if this could cause problems when this rule gets into production.

let watchlst_ = (_GetWatchlist('SS-PE-PAC-WL-Grp_Pacifico_MFA_BYOD_Users') | project SearchKey);
AuditLogs
| where OperationName in ('Remove member from group', 'Remove owner from group')
| extend AccountUPN = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| where AccountUPN startswith 'tivit.'
| where AccountUPN !in~ (watchlst_)
| summarize test = count() by AccountUPN

 Results:

Watchlist content:

[#2]

is SearchKey projecting the UPN, or another column?

[#3]

SearchKey is projecting userPrincipalName that contains users accounts but when i try to compare it with AccountUPN it does not working.

[#4 · Accepted solution]

Here is my version and it works with my Watchlist

let watchlst_ =_GetWatchlist("upnList") | project Analysts, _DTItemId;
AuditLogs
| where OperationName in~ ('Remove member from group', 'Remove owner from group')
| extend AccountUPN = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| where isnotempty(AccountUPN)
| where AccountUPN !startswith 'adm'
| where AccountUPN !in~ (watchlst_)
| summarize test = count() by AccountUPN
| order by test desc

Maybe the watchlist has issues - control characters/ trailing spaces etc,.. in the rows?

[#5]

@Clive_Watson 

I tried to apply a non-array variable as a search key and it works correctly. I'm not sure if that's the problem but I merge Audit Logs with SigninLogs to use User Id as my flag variable and it works fine.

 let watchlst_ =_GetWatchlist("SS-PE-PAC-WL-Grp_Pacifico_MFA_BYOD_Users")
    | project SearchKey;
AuditLogs
| extend AccountID = tostring(parse_json(tostring(InitiatedBy.user)).id)
| extend AccountUPN = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| where OperationName in~ ('Remove member from group', 'Remove owner from group')
| extend oldValue_ = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].oldValue)))
| extend oldValueID_ = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[0].oldValue)))
| extend newValue_ = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))
| extend newValueID_ = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[0].newValue)))
| extend AccountUPN = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)
| extend Afectado = tostring(TargetResources[0].userPrincipalName)
| where oldValue_ in ('Grp_Pacifico_MFA_BYOD_Users', 'Grp_Office365_MFA_External_Users', 'Grp_Pacifico_Home_Office_Users', ' Grp_Pacifico_Home_Office_BYOD_Users_MAC', 'Grp_Test_Sentinel') or newValue_ in ('Grp_Pacifico_MFA_BYOD_Users', 'Grp_Office365_MFA_External_Users', 'Grp_Pacifico_Home_Office_Users', ' Grp_Pacifico_Home_Office_BYOD_Users_MAC', 'Grp_Test_Sentinel')
| join kind = inner SigninLogs on $left.AccountID == $right.UserId
| where UserId !in~ (watchlst_)
| summarize by TimeGenerated, AccountUPN, Afectado, CorrelationId, oldValue_, newValue_

Now output only list users that are not in watchlist: