Microsoft Azure SQL Database DTU and CPU Utilization Saturation Sentinel

id: azure-sql-database-dtu-cpu-saturation-sentinel
namespace: company.cloud

inputs:
  - id: subscription_id
    type: STRING
    description: Azure Subscription ID.
  - id: resource_group
    type: STRING
    description: Azure Resource Group name.
  - id: server_name
    type: STRING
    description: Azure SQL Logical Server name.
  - id: database_name
    type: STRING
    description: Azure SQL Database name.
  - id: dtu_threshold_pct
    type: INT
    defaults: 85
    description: "Maximum allowable DTU consumption percentage before triggering an
      alert (default 85%)."

tasks:
  - id: check_sql_dtu_metrics
    type: io.kestra.plugin.scripts.shell.Commands
    containerImage: mcr.microsoft.com/azure-cli:latest
    commands:
      - |
        az login --service-principal -u "{{ secret('AZURE_CLIENT_ID') }}" -p "{{ secret('AZURE_CLIENT_SECRET') }}" --tenant "{{ secret('AZURE_TENANT_ID') }}" --output none
        az account set --subscription "{{ inputs.subscription_id }}"

        RESOURCE_ID="/subscriptions/{{ inputs.subscription_id }}/resourceGroups/{{ inputs.resource_group }}/providers/Microsoft.Sql/servers/{{ inputs.server_name }}/databases/{{ inputs.database_name }}"

        az monitor metrics list           --resource "$RESOURCE_ID"           --metric "dtu_consumption_percent"           --interval PT5M           --output json > /tmp/sql_telemetry.json

        MAX_DTU=$(jq '[.value[]?.timeseries[]?.data[]?.maximum // 0] | max // 0' /tmp/sql_telemetry.json)
        echo "::set-output name=max_dtu_percent::$MAX_DTU::"

  - id: evaluate_dtu_saturation
    type: io.kestra.plugin.core.flow.If
    condition: "{{ outputs.check_sql_dtu_metrics.vars.max_dtu_percent | float >=
      inputs.dtu_threshold_pct }}"
    then:
      - id: notify_dba_team
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        url: "{{ secret('SLACK_WEBHOOK') }}"
        payload: |
          {
            "channel": "#cloud-ops",
            "attachments": [
              {
                "color": "danger",
                "title": "🚨 Azure SQL Database DTU Saturation Alert",
                "text": "Azure SQL Database `{{ inputs.database_name }}` on server `{{ inputs.server_name }}` reached peak DTU consumption of *{{ outputs.check_sql_dtu_metrics.vars.max_dtu_percent }}%* over the last 5 minutes (threshold {{ inputs.dtu_threshold_pct }}%).",
                "footer": "Kestra Cloud Sentinel • Microsoft Azure"
              }
            ]
          }

errors:
  - id: alert_pipeline_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    url: "{{ secret('SLACK_WEBHOOK') }}"
    payload: |
      {
        "channel": "#cloud-ops",
        "attachments": [
          {
            "color": "danger",
            "title": "⚠️ Azure SQL Database DTU Sentinel Failure",
            "text": "Failed to retrieve Azure Monitor metrics for Azure SQL Database. Verify resource group and database existence.",
            "footer": "Kestra Workflow Failure Sentinel"
          }
        ]
      }

triggers:
  - id: periodic_schedule
    type: io.kestra.plugin.core.trigger.Schedule
    cron: "*/15 * * * *"
    disabled: true