kusto-analyst

# Kusto Analyst Analyze Android authentication telemetry using Azure Data Explorer (Kusto) for error analysis, latency investigation, and cross-cluster correlation. ## Working an Aria health-metric alert? If the user is investigating an IcM titled "Aria detected an incident in `<project>` for `<metric>`", use the [`aria-alert-investigator`](../aria-alert-investigator/SKILL.md) skill. It defines the canonical four-view query pattern (raw count, rate per 1k requests, rate per 1k devices, same-day-of-week) and the rules for confirming the metric slice before running queries. This skill provides the underlying Kusto reference but not the Aria-specific workflow. ## Available MCP Tools **Always use these tools to execute Kusto queries:** - `mcp_my-mcp-server_execute_query` - Execute Kusto queries - `mcp_my-mcp-server_list_tables` - Discover available tables - `mcp_my-mcp-server_get_table_schema` - Explore field schema --- ## Android Telemetry Cluster ### Cluster Information | Property | Value | |----------|-------| | **Cluster URL** | `https://idsharedeus2.kusto.windows.net/` | | **Production Database** | `ad-accounts-android-otel` | | **Sandbox Database** | `android-broker-otel-sandbox` | ### Primary Tables | Table | Purpose | Retention | |-------|---------|-----------| | `android_spans` | Authentication telemetry spans | 30 days | | `android_metrics` | Aggregated metrics data | 30 days | ### Materialized Views - **46 pre-aggregated views** for faster queries - **Retention:** 90 days (longer than raw tables!) - **Update frequency:** Hourly - **Discover with:** `.show materialized-views` query - **Categories:** Error Analysis, Silent/Interactive Auth, PRT Operations, Broker & Apps, Devices, Performance ### User Intent Translation | User Says | Span Name | |-----------|-----------| | "Interactive request" | `AcquireTokenInteractive` | | "Silent request" | `AcquireTokenSilent` | | "PRT operation" | Various PRT-related spans | --- ## android_spans Key Fields ### Span Identification | Field | Description | |-------|-------------| | `span_id` | Unique identifier for the span | | `parent_span_id` | Parent span ID for hierarchical relationships | | `trace_id` | Trace ID linking related spans | | `correlation_id_v2` | **Primary correlation ID — use this for eSTS correlation and incident lookups.** | | `correlation_id` | Legacy column. Unpopulated in practice (0% of rows). | | `span_name` | Operation name (e.g., "AcquireTokenInteractive") | > ⚠️ **Correlation column gotcha:** Across the common calling packages, `correlation_id` is effectively unpopulated while `correlation_id_v2` is populated for the large majority of rows. **Always query `correlation_id_v2` first** for any incident lookup. Querying only `correlation_id` will give a false-negative zero-row result. When confirming "the broker never saw this CID," check `correlation_id_v2` as the primary signal and `correlation_id` only as a completeness check. > > Caveat: a few packages have low `correlation_id_v2` coverage — for those, telemetry alone may not be sufficient to confirm presence/absence. > ⚠️ **`android_spans` head-sampling caveat (CRITICAL for absence-as-evidence claims):** The Android broker emits telemetry via head-based per-trace sampling, so only a fraction of traces are recorded. Implications: > > - **A single missing CID is more likely a sampling drop than a real absence**, and two missing CIDs from the same incident are quite likely to both have been sampled out even if the broker DID handle both. > - **Never** conclude "the broker was not invoked" from a zero-row CID lookup alone. Always combine with the Zero-Row Guard checks (reference healthy trace + same-tenant/same-window cross-check) AND state the sampling caveat explicitly in any IcM update. > - Sampling is keyed by trace ID (not correlation_id, device, or tenant) and has no awareness of correlation_id_v2 — it cannot "systematically" drop a specific CID, but for any single CID the sampling prior strongly favors "absent." > - The customer **broker client log bundle** is the authoritative ground truth for whether the broker received a request. Cluster telemetry is suggestive at best for any single-correlation-id question. ### Error Information | Field | Description | |-------|-------------| | `error_code` | Error code (e.g., "auth_cancelled_by_sdk") | | `error_message` | Detailed error message | | `span_status` | Status ("OK", "ERROR") | ### Broker Information | Field | Description | |-------|-------------| | `active_broker_package_name` | Currently active broker package | | `current_broker_package_name` | Current broker package | | `calling_package_name` | Package that initiated the call | **Common Broker Packages:** - `com.microsoft.windowsintune.companyportal` - Company Portal - `com.azure.authenticator` - Azure Authenticator - `com.microsoft.appmanager` - Microsoft App Manager ### Device & Timing | Field | Description | |-------|-------------| | `DeviceInfo_Id` | Unique device identifier | | `DeviceInfo_Model` | Device model (e.g., "Pixel 7 Pro") | | `EventInfo_Time` | Event timestamp (use `ago(Xd)` for filtering) | | `elapsed_time` | Total operation duration | --- ## Common Query Patterns ### Discovery Queries **Find top span names:** ```kql android_spans | where EventInfo_Time >= ago(7d) | summarize count() by span_name | order by count_ desc | take 30 ``` **Find common error codes:** ```kql android_spans | where EventInfo_Time >= ago(7d) | where isnotempty(error_code) | summarize count() by error_code | order by count_ desc | take 20 ``` ### Error Analysis **Error patterns for specific span:** ```kql android_spans | where EventInfo_Time >= ago(7d) | where span_name == "AcquireTokenInteractive" | where isnotempty(error_code) | summarize error_count = count() by error_code, error_message | order by error_count desc ``` **Device-level error aggregation:** ```kql android_spans | where EventInfo_Time >= ago(7d) | summarize total_devices = dcount(DeviceInfo_Id), error_count = count() by error_code ``` ### Company Portal Detection ```kql android_spans | where EventInfo_Time >= ago(7d) | extend has_cp = iff( active_broker_package_name contains "companyportal" or calling_package_name contains "companyportal", 1, 0) | summarize total = count(), with_cp = countif(has_cp == 1) | extend cp_percentage = round(100.0 * with_cp / total, 2) ``` ### Parent-Child Span Relationships ```kql let parentSpans = android_spans | where EventInfo_Time >= ago(7d) | where span_name == "AcquireTokenInteractive" | project parent_span_id = span_id, trace_id; let childSpans = android_spans | where EventInfo_Time >= ago(7d) | where span_name == "ProcessWebCpRedirects" | project child_span_id = span_id, parent_span_id, trace_id; parentSpans | join kind=inner (childSpans) on trace_id ``` --- ## Latency Investigation Workflow When investigating latency increases (e.g., AcquireTokenSilent), follow these steps: ### Step 1: Identify the Increase ```kql android_spans | where EventInfo_Time >= ago(7d) | where span_name == "AcquireTokenSilent" | summarize p50 = percentile(elapsed_time, 50), p90 = percentile(elapsed_time, 90), p95 = percentile(elapsed_time, 95), p99 = percentile(elapsed_time, 99) by bin(EventInfo_Time, 1h) | order by EventInfo_Time desc ``` ### Step 2: Find Culprit Dimensions ```kql android_spans | where EventInfo_Time >= ago(3d) | where span_name == "AcquireTokenSilent" | summarize count = count(), p90_latency = percentile(elapsed_time, 90) by active_broker_package_name, current_broker_package_name | order by p90_latency desc ``` ### Step 3: Check Error Rate Correlation ```kql android_spans | where EventInfo_Time >= ago(7d) | where span_name == "AcquireTokenSilent" | summarize total = count(), errors = countif(isnotempty(error_code)), avg_latency = avg(elapsed_time) by bin(EventInfo_Time, 1h) | extend error_rate = round(100.0 * errors / total, 2) | order by EventInfo_Time desc ``` ### Step 4: Analyze Elapsed Time Breakdown ```kql android_spans | where EventInfo_Time >= ago(3d) | where span_name == "AcquireTokenSilent" | where isnotempty(elapsed_time_cache_load) or isnotempty(elapsed_time_network_acquire_at) | summarize avg_cache = avg(elapsed_time_cache_load), avg_network = avg(elapsed_time_network_acquire_at), avg_total = avg(elapsed_time) by bin(EventInfo_Time, 1h) ``` --- ## MATS telemetry ### Cluster Information | Property | Value | |----------|-------| | **Cluster URL** | `https://idsharedeus2.kusto.windows.net/` | | **Database** | `MATS_Office` | | **Database ID** | `faab4ead691e451eb230afc98a28e0f2` | --- ## eSTS (Token Service) Cluster ### Cluster Information | Property | Value | |----------|-------| | **Cluster URL** | `https://estswus2.kusto.windows.net/` | | **Database** | `ESTS` | | **Primary Table** | `AllPerRequestTable` (cross-cluster union view) | ### Android-Specific Filtering **⚠️ ALWAYS filter by Android platform unless an explicit platform is specified:** ```kql AllPerRequestTable | where env_time >= ago(7d) | where DevicePlatformForUI == "Android" ``` ### Key eSTS Fields | Category | Field | Description | |----------|-------|-------------| | **Request ID** | `CorrelationId` | **Links to Android `correlation_id_v2`** (round-trip verified). The `correlation_id` column on Android side is empty and does NOT carry the value eSTS receives. | | | `RequestId` | Unique eSTS request ID | | | `env_time` | Request timestamp | | **Request Type** | `Call` | Auth call type (e.g., "token") | | | `IsInteractive` | User interaction required | | | `Prompt` | Prompt type ("none", "login") | | **Status** | `Result` | "Success" or "Failure" | | | `ErrorCode` | Error code if failed | | | `HttpStatusCode` | HTTP status | | **PRT** | `PrtData` | PRT-related data (JSON) | | **Device** | `DeviceId` | Device identifier | | | `ApplicationId` | Client app ID | | **User** | `TenantId` | Tenant ID | | | `UserPrincipalObjectID` | User's Entra ID object ID | | | `AccountType` | AAD, MSA, etc. | | | `DevicePlatformForUI` | `"Android"`, `"iOS"`, etc. — **authoritative source for platform attribution**, more reliable than IcM title/custom fields. | > ⚠️ **Required cross-cluster validation step.** Before relying on any "CID present in eSTS but absent in `android_spans`" conclusion (or the inverse), perform this round-trip sanity check at the start of the investigation: > > 1. Pick any healthy `android_spans` row for the same calling-app family in the last hour: `android_spans | take 1 | project correlation_id_v2`. > 2. Look up that GUID in eSTS: `AllPerRequestTable | where CorrelationId == '<guid>'` — confirm a hit. > 3. Conversely, pick any recent eSTS `CorrelationId` for the same platform: `AllPerRequestTable | where DevicePlatformForUI == 'Android' | take 1 | project CorrelationId`. > 4. Confirm the GUID appears in `android_spans.correlation_id_v2` (allowing for the head-sampling caveat — may need a few tries). > > Verified empirically: `correlation_id_v2` IS the same identifier as `CorrelationId`. But re-validate quickly per investigation in case the field semantics change in a future broker release. Without this check, an outdated assumption about which column carries the cross-cluster identifier can invalidate the entire conclusion. > > Also: do NOT trust the IcM title or `Impacted Users` custom field as proof of platform. Use eSTS `DevicePlatformForUI` for the same CIDs as the authoritative source. --- ## Cross-Cluster Correlation To trace a complete flow (Android → Broker → eSTS):