KQL Search

Query Details

Analytics Privileged Identity Info

Query

// This query can help you to obtain which accounts in IdentityInfo table have privileged groups or roles, according to values specified in certain Watchlists.
// IdentityInfo table renews itself completely every ~14 days.
//
// Click "Save as function", in Parameters write in the fields:
// "datetime"  "query_date"     ""
// "timespan"  "query_period"   "14d"
//
// If you name the function "PrivilegedIdentityInfo", you can check the function with queries like the following:
//
// PrivilegedIdentityInfo
//
// PrivilegedIdentityInfo(now(), 14d)
//
// let Function = (query_date:datetime, query_period:timespan = 14d){
let _PrivilegedGroupRegex = toscalar(
    union
        (
        _GetWatchlist("SID-AuditADObjects")
        | where Notes has_any ("[Privileged]", "[Unpopulated]")
        | project RegEx = regex_quote(SAMAccountName)
        ),
        (
        _GetWatchlist("RegEx-PrivDomainGroups")
        | project RegEx
        )
    | summarize RegEx = make_set(RegEx)
    | extend RegEx = strcat(@"^(", strcat_array(RegEx, "|"), @")$")
);
let _PrivilegedRoleRegex = toscalar(
    _GetWatchlist("RegEx-PrivAADRoles")
    | summarize RegEx = make_list(RegEx)
    | extend RegEx = strcat(@"^(", strcat_array(RegEx, "|"), @")$")
);
IdentityInfo
| where TimeGenerated between ((query_date - query_period) .. query_date)
| summarize arg_max(TimeGenerated, *) by AccountObjectId, AccountSID
| mv-expand GroupMember = GroupMembership to typeof(string), AssignedRole = AssignedRoles to typeof(string)
| where GroupMember matches regex _PrivilegedGroupRegex or AssignedRole matches regex _PrivilegedRoleRegex
| project-away GroupMember, AssignedRole
| summarize take_any(*) by AccountObjectId, AccountSID
// };
// Function(now(), 14d)

Explanation

This query is designed to identify accounts in the IdentityInfo table that have privileged groups or roles. It uses specific patterns defined in watchlists to determine what constitutes a "privileged" group or role. Here's a simple breakdown of what the query does:

  1. Watchlists for Privileged Groups and Roles:

    • It retrieves patterns from two watchlists: "SID-AuditADObjects" and "RegEx-PrivDomainGroups" for privileged groups, and "RegEx-PrivAADRoles" for privileged roles.
    • These patterns are used to create regular expressions that help identify privileged groups and roles.

  2. Data Filtering:

    • The query looks at the IdentityInfo table, which is updated every 14 days.
    • It filters the data to include only records generated within a specified time period (query_period), ending at a specified date (query_date).

  3. Data Processing:

    • It identifies the most recent entry for each account by using arg_max based on TimeGenerated.
    • It expands the GroupMembership and AssignedRoles fields to check each group and role individually.

  4. Matching Privileged Groups/Roles:

    • It filters the expanded data to find accounts where the group membership or assigned roles match the privileged patterns.

  5. Result Compilation:

    • It removes the GroupMember and AssignedRole fields from the results.
    • It summarizes the data to ensure each account is listed only once, using take_any to select any record for each account.

In essence, this query helps you find accounts with privileged access by checking against predefined patterns of privileged groups and roles, using data from the IdentityInfo table.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: February 3, 2025

Tables

IdentityInfo

Keywords

IdentityInfoWatchlistsPrivilegedGroupsRolesAccountSIDTimeGeneratedGroupMembershipAssignedRolesAccountObjectId

Operators

toscalarunionwherehas_anyprojectregex_quotesummarizemake_setextendstrcatstrcat_arraymake_listIdentityInfobetweenarg_maxbymv-expandmatchesregexproject-awaytake_any

Actions