Sybase ASE Transaction Log Capacity and Suspension Sentinel

id: sybase-transaction-log-capacity-and-suspension-sentinel
namespace: company.database

inputs:
  - id: sybase_url
    type: STRING
    defaults: "jdbc:sybase:Tds:localhost:5000/master"
    description: JDBC connection URL for the target Sybase ASE instance (e.g.
      jdbc:sybase:Tds:host:port/database).

  - id: sybase_database
    type: STRING
    defaults: "master"
    description: Target database name to monitor for transaction log (syslogs) space
      consumption.

  - id: max_log_used_percent_threshold
    type: FLOAT
    defaults: 75.0
    description: Warning threshold percentage of transaction log utilization before
      triggering an operational alert.

  - id: critical_log_used_percent_threshold
    type: FLOAT
    defaults: 90.0
    description: Critical threshold percentage approaching the Last Chance Threshold
      (LCT) that risks freezing write transactions.

triggers:
  - id: periodic_schedule
    type: io.kestra.plugin.core.trigger.Schedule
    cron: "0 */2 * * *"
    disabled: true
    inputs:
      sybase_url: "jdbc:sybase:Tds:localhost:5000/master"
      sybase_database: "master"
      max_log_used_percent_threshold: 75.0
      critical_log_used_percent_threshold: 90.0

tasks:
  - id: probe_transaction_log_space
    type: io.kestra.plugin.jdbc.sybase.Query
    url: "{{ inputs.sybase_url }}"
    username: "{{ secret('SYBASE_USERNAME') }}"
    password: "{{ secret('SYBASE_PASSWORD') }}"
    fetchType: FETCH_ONE
    allowFailure: true
    sql: |
      SELECT
        d.name AS db_name,
        sum(u.size) AS total_pages,
        lct_admin("logsegment_freepgs", d.dbid) AS free_log_pages,
        ROUND(100.0 - (100.0 * lct_admin("logsegment_freepgs", d.dbid) / NULLIF(sum(u.size), 0)), 2) AS log_used_percent
      FROM master..sysdatabases d
      JOIN master..sysusages u ON d.dbid = u.dbid
      WHERE d.name = '{{ inputs.sybase_database }}' AND u.segmap & 4 = 4
      GROUP BY d.name, d.dbid
    description: Probes Sybase sysusages and queries the Last Chance Threshold admin
      function to calculate exact transaction log segment space utilization.

  - id: classify_sybase_log_posture
    type: io.kestra.plugin.core.debug.Return
    format: >-
      {{
        (outputs.probe_transaction_log_space is not defined or outputs.probe_transaction_log_space.row is not defined or outputs.probe_transaction_log_space.row.log_used_percent is not defined) ? 'SYBASE_CONNECTION_FAILED' :
        (outputs.probe_transaction_log_space.row.log_used_percent >= inputs.critical_log_used_percent_threshold) ? 'CRITICAL_LOG_SPACE_EXHAUSTION' :
        (outputs.probe_transaction_log_space.row.log_used_percent >= inputs.max_log_used_percent_threshold) ? 'WARNING_LOG_SPACE_SURGE' :
        'HEALTHY'
      }}
    description: Classifies transaction log health into operational risk postures
      based on evaluated threshold percentages.

  - id: route_posture_action
    type: io.kestra.plugin.core.flow.Switch
    value: "{{ outputs.classify_sybase_log_posture.value }}"
    cases:
      CRITICAL_LOG_SPACE_EXHAUSTION:
        - id: alert_critical_log_exhaustion
          type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
          url: "{{ secret('SLACK_WEBHOOK_URL') }}"
          payload: "{{ {'text': '🚨 *CRITICAL: Sybase ASE Transaction Log Imminent
            Freezing Risk*

            • *Database:* `' ~ inputs.sybase_database ~ '`

            • *Log Utilization:* `' ~
            outputs.probe_transaction_log_space.row.log_used_percent ~ '%`
            (Critical Ceiling: `' ~ inputs.critical_log_used_percent_threshold ~
            '%`)

            • *Free Log Pages:* ' ~
            outputs.probe_transaction_log_space.row.free_log_pages ~ '

            • *Execution ID:* `' ~ execution.id ~ '`

            *Action Required:* Transaction log is approaching Last Chance
            Threshold (LCT); dump transaction log or allocate additional log
            space immediately to avoid suspended write transactions.'} | toJson
            }}"

      WARNING_LOG_SPACE_SURGE:
        - id: alert_warning_log_surge
          type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
          url: "{{ secret('SLACK_WEBHOOK_URL') }}"
          payload: "{{ {'text': '⚠️ *WARNING: Sybase ASE Transaction Log Growth Surge*

            • *Database:* `' ~ inputs.sybase_database ~ '`

            • *Log Utilization:* `' ~
            outputs.probe_transaction_log_space.row.log_used_percent ~ '%`
            (Warning Threshold: `' ~ inputs.max_log_used_percent_threshold ~
            '%`)

            • *Execution ID:* `' ~ execution.id ~ '`

            *Notice:* High volume uncommitted transactions or deferred log
            truncation observed.'} | toJson }}"

      SYBASE_CONNECTION_FAILED:
        - id: alert_connection_failure
          type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
          url: "{{ secret('SLACK_WEBHOOK_URL') }}"
          payload: "{{ {'text': '🔥 *ALERT: Sybase ASE Instance Unreachable*

            • *URL:* `' ~ inputs.sybase_url ~ '`

            • *Database:* `' ~ inputs.sybase_database ~ '`

            • *Error:* Unable to establish JDBC connection or authenticate with
            Sybase TDS driver.

            • *Execution ID:* `' ~ execution.id ~ '`'} | toJson }}"

      HEALTHY:
        - id: log_sybase_healthy
          type: io.kestra.plugin.core.log.Log
          message: "Sybase ASE database '{{ inputs.sybase_database }}' transaction log is
            healthy. Used: {{
            outputs.probe_transaction_log_space.row.log_used_percent }}%, Free
            pages: {{ outputs.probe_transaction_log_space.row.free_log_pages
            }}."
    description: Routes operational notifications to Slack or diagnostic execution
      logs based on evaluated log capacity posture.

  - id: audit_sentinel_run
    type: io.kestra.plugin.core.log.Log
    message: "Sybase transaction log sentinel audit complete for execution {{
      execution.id }}. Verdict: {{ outputs.classify_sybase_log_posture.value }},
      Log Used: {{ outputs.probe_transaction_log_space.row.log_used_percent ??
      'N/A' }}%, Free Pages: {{
      outputs.probe_transaction_log_space.row.free_log_pages ?? 'N/A' }}."
    description: Emits an immutable structured audit log entry recording Sybase
      capacity metrics and cycle verdicts.

errors:
  - id: alert_flow_error
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
    payload: "{{ {'text': '❌ *Sybase Sentinel Workflow Failure*

      Flow failed unexpectedly during execution `' ~ execution.id ~ '` against
      `' ~ inputs.sybase_url ~ '`. Inspect Kestra task error logs.'} | toJson
      }}"