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 descExplanation
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:
- Filter by Time: It selects records where the sign-in occurred within the last 365 days.
- 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).
- Collects a list of unique IP addresses (
- 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
Released: November 10, 2024
Tables
AADServicePrincipalSignInLogs
Keywords
ServicePrincipalSignInLogsIPAddressNameResourceDisplay
Operators
letwhereago()summarizemake_set()count()byorder bydesc