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

 

 

 

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?

4 Replies

    • 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?

Resources