Azure Log Analytics Integration with Synapse

Integrating Azure Log Analytics with Synapse Analytics enables powerful analysis of operational data. Query logs at scale and combine with business data for comprehensive insights.

Exporting Logs to Synapse

Configure diagnostic settings to export to Data Lake:

resource diagnosticSetting 'Microsoft.Insights/diagnosticSettings@2021-05-01-preview' = {
  name: 'export-to-datalake'
  scope: webApp
  properties: {
    workspaceId: logAnalyticsWorkspace.id
    storageAccountId: storageAccount.id
    logs: [
      {
        category: 'AppServiceHTTPLogs'
        enabled: true
        retentionPolicy: {
          enabled: true
          days: 90
        }
      }
      {
        category: 'AppServiceConsoleLogs'
        enabled: true
      }
      {
        category: 'AppServiceAppLogs'
        enabled: true
      }
    ]
    metrics: [
      {
        category: 'AllMetrics'
        enabled: true
      }
    ]
  }
}

Querying Logs in Synapse

Use serverless SQL to query exported logs:

-- Query App Service HTTP logs from Data Lake
SELECT
    time,
    resourceId,
    JSON_VALUE(properties, '$.Method') as HttpMethod,
    JSON_VALUE(properties, '$.Protocol') as Protocol,
    JSON_VALUE(properties, '$.CsHost') as Host,
    JSON_VALUE(properties, '$.CsUriStem') as Path,
    CAST(JSON_VALUE(properties, '$.ScStatus') AS INT) as StatusCode,
    CAST(JSON_VALUE(properties, '$.TimeTaken') AS INT) as TimeTakenMs,
    JSON_VALUE(properties, '$.UserAgent') as UserAgent
FROM OPENROWSET(
    BULK 'https://datalake.blob.core.windows.net/logs/AppServiceHTTPLogs/**/*.json',
    FORMAT = 'CSV',
    FIELDQUOTE = '0x0b',
    FIELDTERMINATOR = '0x0b',
    ROWTERMINATOR = '\n'
)
WITH (doc NVARCHAR(MAX)) AS logs
CROSS APPLY OPENJSON(doc)
WITH (
    time DATETIME2 '$.time',
    resourceId NVARCHAR(500) '$.resourceId',
    properties NVARCHAR(MAX) '$.properties' AS JSON
)
WHERE time > DATEADD(day, -7, GETUTCDATE());

-- Analyze error rates
WITH HttpLogs AS (
    SELECT
        CAST(time AS DATE) as LogDate,
        DATEPART(HOUR, time) as LogHour,
        CAST(JSON_VALUE(properties, '$.ScStatus') AS INT) as StatusCode
    FROM OPENROWSET(
        BULK 'https://datalake.blob.core.windows.net/logs/AppServiceHTTPLogs/**/*.json',
        FORMAT = 'CSV',
        FIELDQUOTE = '0x0b',
        FIELDTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) AS logs
    CROSS APPLY OPENJSON(doc)
    WITH (
        time DATETIME2 '$.time',
        properties NVARCHAR(MAX) '$.properties' AS JSON
    )
)
SELECT
    LogDate,
    LogHour,
    COUNT(*) as TotalRequests,
    SUM(CASE WHEN StatusCode >= 400 THEN 1 ELSE 0 END) as ErrorCount,
    CAST(SUM(CASE WHEN StatusCode >= 400 THEN 1.0 ELSE 0 END) / COUNT(*) * 100 AS DECIMAL(5,2)) as ErrorRate
FROM HttpLogs
GROUP BY LogDate, LogHour
ORDER BY LogDate DESC, LogHour DESC;

KQL Queries in Log Analytics

Complex log analysis:

// Application performance analysis
requests
| where timestamp > ago(24h)
| summarize
    RequestCount = count(),
    AvgDuration = avg(duration),
    P50 = percentile(duration, 50),
    P95 = percentile(duration, 95),
    P99 = percentile(duration, 99),
    FailureCount = countif(success == false)
    by bin(timestamp, 1h), cloud_RoleName
| render timechart

// Dependency analysis
dependencies
| where timestamp > ago(24h)
| where success == false
| summarize FailureCount = count() by target, type, resultCode
| top 20 by FailureCount
| render barchart

// End-to-end transaction tracing
let operationId = "abc123";
union requests, dependencies, traces, exceptions
| where operation_Id == operationId
| project
    timestamp,
    itemType,
    name = coalesce(name, message, outerMessage),
    duration = coalesce(duration, 0),
    success = coalesce(success, problemId == "")
| order by timestamp asc

// Slow query detection
AppTraces
| where Message contains "SQL"
| parse Message with * "Duration: " Duration:long "ms" *
| where Duration > 1000
| summarize
    SlowQueryCount = count(),
    AvgDuration = avg(Duration),
    MaxDuration = max(Duration)
    by bin(TimeGenerated, 1h)
| render timechart

// Resource utilization correlation
Perf
| where TimeGenerated > ago(1h)
| where ObjectName == "Processor" and CounterName == "% Processor Time"
| summarize AvgCPU = avg(CounterValue) by bin(TimeGenerated, 5m), Computer
| join kind=inner (
    requests
    | where timestamp > ago(1h)
    | summarize RequestCount = count() by bin(timestamp, 5m)
) on $left.TimeGenerated == $right.timestamp
| project TimeGenerated, Computer, AvgCPU, RequestCount
| render timechart

Azure Function for Log Processing

Process logs with Azure Functions:

using Microsoft.Azure.WebJobs;
using Microsoft.Extensions.Logging;
using Azure.Monitor.Query;
using Azure.Identity;

public class LogProcessor
{
    private readonly LogsQueryClient _logsClient;
    private readonly ILogger<LogProcessor> _logger;

    public LogProcessor(ILogger<LogProcessor> logger)
    {
        _logger = logger;
        _logsClient = new LogsQueryClient(new DefaultAzureCredential());
    }

    [FunctionName("ProcessLogs")]
    public async Task Run(
        [TimerTrigger("0 */15 * * * *")] TimerInfo timer)
    {
        var workspaceId = Environment.GetEnvironmentVariable("LOG_ANALYTICS_WORKSPACE_ID");

        var query = @"
            requests
            | where timestamp > ago(15m)
            | where success == false
            | summarize FailureCount = count() by cloud_RoleName, name, resultCode
            | where FailureCount > 10
        ";

        var response = await _logsClient.QueryWorkspaceAsync(
            workspaceId,
            query,
            new QueryTimeRange(TimeSpan.FromMinutes(15)));

        foreach (var row in response.Value.Table.Rows)
        {
            var roleName = row["cloud_RoleName"].ToString();
            var requestName = row["name"].ToString();
            var failureCount = Convert.ToInt32(row["FailureCount"]);

            _logger.LogWarning(
                "High failure rate detected: {RoleName}/{RequestName} - {Count} failures",
                roleName, requestName, failureCount);

            // Send alert
            await SendAlertAsync(roleName, requestName, failureCount);
        }
    }

    private async Task SendAlertAsync(string roleName, string requestName, int failureCount)
    {
        // Send to Teams, PagerDuty, etc.
    }
}

Creating Custom Metrics

Export custom metrics from logs:

// Create custom metric from logs
let BusinessMetrics = AppTraces
| where Message startswith "OrderProcessed"
| parse Message with "OrderProcessed: " OrderId " Total: " Total:real " Items: " ItemCount:int
| summarize
    OrderCount = count(),
    TotalRevenue = sum(Total),
    AvgOrderValue = avg(Total),
    AvgItemsPerOrder = avg(ItemCount)
    by bin(TimeGenerated, 1h);

// Export to custom metrics
BusinessMetrics
| extend
    MetricName = "BusinessMetrics",
    Dimensions = pack("OrderCount", OrderCount, "Revenue", TotalRevenue)

Summary

Log Analytics integration enables:

• Centralized log analysis
• Cross-service correlation
• Performance monitoring
• Custom metric creation
• Automated alerting

Build comprehensive observability for your applications.

---

References:

• Azure Monitor Logs
• Log Analytics Query
• Diagnostic Settings