KQL Search

Query Details

HomeAI ToolsDevice Query

Entra ID Service Principal Sign In Insights

Query

//This query provides insights about service principal sign-in requests, resources, and source IPs
//Useful for analyzing service principal usage before implementing IP restrictions with conditional access
let LookbackPeriod = 365d;
AADServicePrincipalSignInLogs
| where TimeGenerated > ago(LookbackPeriod)
| summarize make_set(IPAddress), RequestCount = count() by ServicePrincipalName, ResourceDisplayName
| order by RequestCount desc

Explanation

This query is designed to analyze the sign-in activity of service principals over the past year (365 days). It retrieves data from the AADServicePrincipalSignInLogs and performs the following steps:

  1. Filter by Time: It selects records where the sign-in occurred within the last 365 days.
  2. Summarize Data: For each service principal and the resources they accessed, it:
    • Collects a list of unique IP addresses (make_set(IPAddress)) from which the sign-ins originated.
    • Counts the total number of sign-in requests (RequestCount).
  3. Order Results: It sorts the results in descending order based on the number of sign-in requests, so the most frequently accessed resources appear first.

This query is particularly useful for understanding how service principals are being used, which can help in planning and implementing IP restrictions through conditional access policies.

Details

Lukasz Kozubal profile picture

Lukasz Kozubal

Released: November 10, 2024

Tables

AADServicePrincipalSignInLogs

Keywords

ServicePrincipalSignInLogsIPAddressServicePrincipalNameResourceDisplayName

Operators

letwhereago()summarizemake_set()count()byorder bydesc

Actions