We’ve been running Azure Log Analytics for compliance auditing across multiple subscriptions and I’m curious how others are handling identity mapping in their Kusto queries. Our current challenge is correlating service principal activities with actual application owners when querying diagnostic logs. We have diagnostic settings configured to capture sign-in logs and activity logs, but the raw data shows object IDs rather than meaningful identity information. When we join AuditLogs with IdentityInfo tables, we’re finding gaps where service principals aren’t properly tagged or documented. This makes it difficult to produce audit reports that satisfy our compliance requirements. What strategies have you found effective for maintaining a clear mapping between service identities and their business context? Are there specific tagging conventions or additional data sources you integrate into your queries?
We faced similar issues last year. The key is establishing a consistent tagging strategy at the service principal creation level. We implemented a mandatory tag structure that includes owner email, application name, and cost center. Then in our Kusto queries, we join the activity logs with Azure Resource Graph data to pull in those tags. This gives us the business context we need without relying solely on the IdentityInfo table which can lag behind.
Building on that tagging approach - we also export our service principal metadata to a custom table in Log Analytics using a scheduled Logic App. Every 24 hours it queries Azure AD for all service principals, enriches them with data from our CMDB, and writes to a custom log. This creates a reliable lookup table for our audit queries. The diagnostic settings alone won’t give you everything you need for compliance reporting. You need that additional layer of business context that comes from outside Azure.
For anyone implementing these approaches, remember that Azure Policy can enforce your tagging requirements at creation time. We have policies that prevent service principal creation without the required tags. This prevents the mapping problem at the source rather than trying to fix it later in queries.
Let me synthesize the best practices we’ve collectively identified here into a comprehensive approach. For effective identity mapping in Log Analytics audit queries, you need a multi-layered strategy addressing diagnostic settings configuration, Kusto query optimization, and service principal metadata enrichment.
First, configure your diagnostic settings to capture the right log categories - specifically AuditLogs, SignInLogs, and ServicePrincipalSignInLogs. These provide the foundation but contain only object IDs. Second, implement a mandatory tagging convention for all service principals at creation time using Azure Policy enforcement. Required tags should include: owner contact, application name, cost center, and environment. This gives you business context that’s otherwise missing from raw logs.
Third, establish a daily enrichment pipeline using Logic Apps or Azure Functions to extract service principal metadata from Azure AD, augment it with CMDB data, and write it to a custom table in your Log Analytics workspace. This creates a reliable lookup source that’s queryable via Kusto joins. Fourth, optimize your Kusto queries by filtering on time ranges first, using materialize() for repeated lookup table references, and creating pre-aggregated summary tables for common compliance reports.
For managed identities specifically, ensure your diagnostic settings capture both the identity object ID and the associated resource ID. Create a separate enrichment process that maps managed identities to their host resources. Finally, consider implementing Azure Resource Graph queries alongside Log Analytics for real-time identity-to-resource mappings during investigations.
The key insight is that diagnostic settings alone provide the raw data, but compliance-ready audit queries require additional business context through tagging, enrichment pipelines, and query optimization. This three-tier approach - proper ingestion configuration, metadata enrichment, and optimized querying - solves the identity mapping challenge while maintaining query performance at scale.
Query performance is critical here. First, always filter by time range as early as possible in your query. Second, use materialize() for your lookup tables if you’re referencing them multiple times. Third, consider pre-aggregating your audit data into summary tables using scheduled queries. For example, we create daily summary tables that already have the identity mappings resolved. Then our compliance reports query these summaries instead of raw logs. This reduced our query times from 5+ minutes to under 30 seconds. Also make sure your diagnostic settings are configured to only capture the log categories you actually need - unnecessary data just slows everything down.
That custom table approach sounds promising. How do you handle the Kusto query performance when joining across multiple data sources? We’ve noticed that complex joins with AuditLogs can timeout during our monthly compliance runs.