SOLVED

Comparing Watchlist items with json object

Occasional Contributor

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:

MiguelCuba_1-1665500976658.png

 

Watchlist content:

MiguelCuba_0-1665500934086.png

 

 

 

4 Replies
is SearchKey projecting the UPN, or another column?
SearchKey is projecting userPrincipalName that contains users accounts but when i try to compare it with AccountUPN it does not working.
best response confirmed by MiguelCuba (Occasional Contributor)
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?

@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:

MiguelCuba_0-1665524650912.png