Forum Discussion

MiguelCuba's avatar
MiguelCuba
Copper Contributor
Oct 11, 2022
Solved

Comparing Watchlist items with json object

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 watchli...
investigation
KQL
siem
  • Clive_Watson's avatar
    Clive_Watson
    Oct 11, 2022
    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's avatar
Clive_Watson
Bronze Contributor
Oct 11, 2022
is SearchKey projecting the UPN, or another column?
MiguelCuba's avatar
MiguelCuba
Copper Contributor
Oct 11, 2022
SearchKey is projecting userPrincipalName that contains users accounts but when i try to compare it with AccountUPN it does not working.
  • Clive_Watson's avatar
    Clive_Watson
    Bronze Contributor
    Oct 11, 2022
    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?
    • MiguelCuba's avatar
      MiguelCuba
      Copper Contributor
      Oct 11, 2022

      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:

       

Resources