// Module 19: KQL Query Examples for Monitoring and Observability

// ========================================
// PERFORMANCE QUERIES
// ========================================

// 1. Request Performance Overview (Last 24 Hours)
requests
| where timestamp > ago(24h)
| summarize 
    RequestCount = count(),
    AvgDuration = avg(duration),
    P50 = percentile(duration, 50),
    P90 = percentile(duration, 90),
    P95 = percentile(duration, 95),
    P99 = percentile(duration, 99),
    FailureRate = countif(success == false) * 100.0 / count()
    by operation_Name, bin(timestamp, 5m)
| project 
    timestamp,
    operation_Name,
    RequestCount,
    AvgDuration = round(AvgDuration, 2),
    P50 = round(P50, 2),
    P90 = round(P90, 2),
    P95 = round(P95, 2),
    P99 = round(P99, 2),
    FailureRate = round(FailureRate, 2)
| render timechart

// 2. Slowest Operations
requests
| where timestamp > ago(1h)
| summarize 
    AvgDuration = avg(duration),
    MaxDuration = max(duration),
    RequestCount = count(),
    P95 = percentile(duration, 95)
    by operation_Name, cloud_RoleName
| order by P95 desc
| take 20
| project 
    Service = cloud_RoleName,
    Operation = operation_Name,
    AvgDuration = round(AvgDuration, 2),
    P95Duration = round(P95, 2),
    MaxDuration = round(MaxDuration, 2),
    RequestCount

// ========================================
// ERROR ANALYSIS QUERIES
// ========================================

// 3. Error Rate by Service and Operation
requests
| where timestamp > ago(6h)
| summarize 
    TotalRequests = count(),
    FailedRequests = countif(success == false),
    ErrorTypes = make_set(resultCode)
    by cloud_RoleName, operation_Name, bin(timestamp, 5m)
| extend ErrorRate = round(FailedRequests * 100.0 / TotalRequests, 2)
| where ErrorRate > 0
| project 
    timestamp,
    Service = cloud_RoleName,
    Operation = operation_Name,
    ErrorRate,
    FailedRequests,
    TotalRequests,
    ErrorCodes = tostring(ErrorTypes)
| order by ErrorRate desc

// 4. Exception Analysis
exceptions
| where timestamp > ago(24h)
| extend 
    ErrorMessage = tostring(customDimensions["Error Message"]),
    StackTrace = tostring(customDimensions["Stack Trace"]),
    CorrelationId = tostring(customDimensions["correlation_id"])
| summarize 
    Count = count(),
    FirstSeen = min(timestamp),
    LastSeen = max(timestamp),
    AffectedUsers = dcount(user_Id),
    SampleMessage = any(ErrorMessage),
    SampleStack = any(StackTrace)
    by type, problemId, cloud_RoleName
| order by Count desc
| project 
    ExceptionType = type,
    Service = cloud_RoleName,
    Count,
    FirstSeen,
    LastSeen,
    AffectedUsers,
    TimeSpan = LastSeen - FirstSeen,
    SampleMessage = substring(SampleMessage, 0, 200)

// ========================================
// DISTRIBUTED TRACING QUERIES
// ========================================

// 5. End-to-End Transaction Analysis
let traceId = "YOUR_TRACE_ID_HERE";
union requests, dependencies, exceptions, traces
| where timestamp > ago(1h)
| where operation_Id == traceId or customDimensions["TraceId"] == traceId
| project 
    timestamp,
    itemType,
    ServiceName = cloud_RoleName,
    OperationName = iif(itemType == "request", name, operation_Name),
    Duration = iif(itemType == "request" or itemType == "dependency", duration, 0),
    Success = iif(itemType == "request" or itemType == "dependency", success, true),
    Details = iif(itemType == "exception", tostring(customDimensions["Error Message"]), ""),
    ParentId = operation_ParentId
| order by timestamp asc
| project-reorder timestamp, itemType, ServiceName, OperationName, Duration, Success, Details

// 6. Service Dependency Map Data
dependencies
| where timestamp > ago(1h)
| summarize 
    CallCount = count(),
    AvgDuration = avg(duration),
    FailureCount = countif(success == false)
    by Source = cloud_RoleName, Target = target, DependencyType = type
| extend FailureRate = round(FailureCount * 100.0 / CallCount, 2)
| project 
    Source,
    Target,
    DependencyType,
    CallCount,
    AvgDuration = round(AvgDuration, 2),
    FailureRate,
    Health = case(
        FailureRate > 5, "Critical",
        FailureRate > 1, "Warning",
        AvgDuration > 1000, "Slow",
        "Healthy"
    )

// ========================================
// BUSINESS METRICS QUERIES
// ========================================

// 7. Custom Business Events Analysis
customEvents
| where timestamp > ago(24h)
| where name in ("OrderCreated", "PaymentProcessed", "UserRegistered")
| extend 
    Amount = todouble(customDimensions["amount"]),
    UserId = tostring(customDimensions["user_id"]),
    ProductId = tostring(customDimensions["product_id"])
| summarize 
    EventCount = count(),
    TotalAmount = sum(Amount),
    UniqueUsers = dcount(UserId),
    UniqueProducts = dcount(ProductId)
    by name, bin(timestamp, 1h)
| project 
    timestamp,
    EventType = name,
    Count = EventCount,
    Revenue = round(TotalAmount, 2),
    UniqueUsers,
    UniqueProducts,
    AvgOrderValue = round(TotalAmount / EventCount, 2)
| render columnchart

// 8. User Journey Funnel
let startTime = ago(24h);
let endTime = now();
customEvents
| where timestamp between (startTime .. endTime)
| where name in ("PageView", "AddToCart", "Checkout", "Purchase")
| extend SessionId = tostring(customDimensions["session_id"])
| summarize 
    UserCount = dcount(user_Id)
    by name
| extend StepOrder = case(
    name == "PageView", 1,
    name == "AddToCart", 2,
    name == "Checkout", 3,
    name == "Purchase", 4,
    99
)
| order by StepOrder asc
| extend ConversionRate = round(UserCount * 100.0 / first(UserCount), 2)
| project 
    Step = name,
    Users = UserCount,
    ConversionRate,
    DropOffRate = 100 - ConversionRate

// ========================================
// PERFORMANCE OPTIMIZATION QUERIES
// ========================================

// 9. Resource Utilization Analysis
performanceCounters
| where timestamp > ago(1h)
| where counter in ("% Processor Time", "Available MBytes", "% Used Memory")
| summarize 
    AvgValue = avg(value),
    MaxValue = max(value),
    P95Value = percentile(value, 95)
    by counter, cloud_RoleInstance, bin(timestamp, 5m)
| extend AlertLevel = case(
    counter == "% Processor Time" and P95Value > 80, "High",
    counter == "% Used Memory" and P95Value > 90, "Critical",
    counter == "Available MBytes" and AvgValue < 500, "Low Memory",
    "Normal"
)
| where AlertLevel != "Normal"
| project 
    timestamp,
    Instance = cloud_RoleInstance,
    Metric = counter,
    AvgValue = round(AvgValue, 2),
    P95Value = round(P95Value, 2),
    MaxValue = round(MaxValue, 2),
    AlertLevel

// 10. Cost Analysis by Operation
requests
| where timestamp > ago(7d)
| summarize 
    RequestCount = count(),
    DataVolumeMB = sum(_BilledSize) / (1024 * 1024)
    by operation_Name, cloud_RoleName, bin(timestamp, 1d)
| extend EstimatedCost = DataVolumeMB * 2.3  // Approximate cost per GB
| summarize 
    TotalRequests = sum(RequestCount),
    TotalDataMB = sum(DataVolumeMB),
    TotalCost = sum(EstimatedCost)
    by operation_Name, cloud_RoleName
| order by TotalCost desc
| project 
    Service = cloud_RoleName,
    Operation = operation_Name,
    RequestCount = TotalRequests,
    DataVolumeMB = round(TotalDataMB, 2),
    EstimatedCostUSD = round(TotalCost, 2),
    CostPerRequest = round(TotalCost / TotalRequests * 1000, 4)  // Cost per 1K requests

// ========================================
// ALERTING QUERIES
// ========================================

// 11. SLO Monitoring - Error Budget Burn Rate
let sloTarget = 99.9;
let timeRange = 30d;
requests
| where timestamp > ago(timeRange)
| summarize 
    TotalRequests = count(),
    FailedRequests = countif(success == false)
    by bin(timestamp, 1h), operation_Name
| extend 
    SuccessRate = (TotalRequests - FailedRequests) * 100.0 / TotalRequests,
    ErrorRate = FailedRequests * 100.0 / TotalRequests
| summarize 
    AvgSuccessRate = avg(SuccessRate),
    ErrorBudgetConsumed = sum(ErrorRate) / (24 * 30)  // Hourly error rate over 30 days
    by operation_Name
| extend 
    SLOStatus = case(
        AvgSuccessRate >= sloTarget, "Meeting SLO",
        AvgSuccessRate >= sloTarget - 0.5, "At Risk",
        "Violating SLO"
    ),
    ErrorBudgetRemaining = (100 - sloTarget) - ErrorBudgetConsumed,
    DaysUntilBudgetExhausted = ErrorBudgetRemaining / (ErrorBudgetConsumed / 30)
| where SLOStatus != "Meeting SLO" or ErrorBudgetRemaining < 0.1
| project 
    Operation = operation_Name,
    CurrentSuccessRate = round(AvgSuccessRate, 3),
    SLOTarget = sloTarget,
    Status = SLOStatus,
    ErrorBudgetRemaining = round(ErrorBudgetRemaining, 3),
    DaysRemaining = round(DaysUntilBudgetExhausted, 1)

// 12. Anomaly Detection for Alert Configuration
let baselineWindow = 7d;
let detectionWindow = 1h;
requests
| where timestamp > ago(baselineWindow)
| summarize 
    RequestRate = count() / (1.0 * 60)  // Requests per minute
    by bin(timestamp, 5m), operation_Name
| summarize 
    AvgRate = avg(RequestRate),
    StdDev = stdev(RequestRate),
    CurrentRate = arg_max(timestamp, RequestRate)
    by operation_Name
| extend 
    ZScore = (CurrentRate - AvgRate) / StdDev,
    IsAnomaly = abs(ZScore) > 3
| where IsAnomaly
| project 
    Operation = operation_Name,
    CurrentRate = round(CurrentRate, 2),
    ExpectedRate = round(AvgRate, 2),
    StandardDeviation = round(StdDev, 2),
    ZScore = round(ZScore, 2),
    AnomalyType = iif(ZScore > 0, "Spike", "Drop"),
    Severity = case(
        abs(ZScore) > 5, "Critical",
        abs(ZScore) > 4, "High",
        "Medium"
    )