KQL Search

Query Details

HomeAI ToolsDevice Query

Monthly Report Entra Eligible Role Activation

Query

// Monthly Report Entra Eligible Role Activation
// https://www.linkedin.com/posts/activity-7195656523899965442-aZTj/

// Entra offers an “Alert detail” view that monitors whether eligible administrators are activating their privileged roles. However, it lacks a built-in feature to measure the frequency of these activations. To overcome this, you can schedule the following KQL query to run every 30 days. This will generate a report detailing the activation history of eligible administrators’ roles over a specified timeframe. It’s important to adhere to the principle of least privilege and revoke any roles that are excessively provisioned and not necessary. 😎

IdentityInfo
| where Timestamp > ago(30d)
| where AssignedRoles != ""
| mv-expand AssignedRoles
| extend EntraAdminRole = tostring(AssignedRoles)
| distinct EntraAdminRole, AccountUpn
| where AccountUpn != ""
| sort by EntraAdminRole asc

Explanation

This KQL query is designed to generate a monthly report on the activation history of eligible administrators' roles in Entra. Here's a simplified breakdown:

  1. Data Source: The query pulls data from the IdentityInfo table.
  2. Time Frame: It filters the data to include only records from the past 30 days.
  3. Role Assignment: It looks for records where roles have been assigned (AssignedRoles is not empty).
  4. Expand Roles: It expands the list of assigned roles so each role is treated as a separate entry.
  5. Role and User Info: It extracts and converts the assigned roles to a string format and ensures each role is associated with a user (AccountUpn).
  6. Unique Entries: It selects distinct combinations of roles and user accounts.
  7. Non-Empty Users: It filters out any entries where the user account (AccountUpn) is empty.
  8. Sorting: Finally, it sorts the results by the role name in ascending order.

The purpose of this query is to help monitor and report on how often eligible administrators activate their privileged roles, ensuring adherence to the principle of least privilege by identifying and potentially revoking unnecessary role assignments.

Details

Steven Lim profile picture

Steven Lim

Released: August 2, 2024

Tables

IdentityInfo

Keywords

IdentityInfoEntraAdminRoleAccountUpnTimestampAssignedRoles

Operators

where>ago!=mv-expandextendtostringdistinctsort byasc

Actions