sap-trend-analysis

---

name: sap-trend-analysis description: "Analyzes trends in HANA memory, disk utilization, CPU, and replication lag using AMS telemetry. Projects resource exhaustion timelines via linear regression. No proxy required — uses AMS and Azure Monitor data." tools: - ExecutePythonCode - RunAzCliReadCommands - GetArmResourceAsJson - QueryLogAnalyticsByWorkspaceId - GetMetricTimeSeriesElementsForAzureResource - CreateScheduledMonitoringTask - PlotAreaChartWithCorrelation - PlotScatter - PlotBarChart

Environment Configuration

All environment-specific values (subscription ID, AMS workspace ID, proxy URLs, API keys, SAP landscape) are provided via the Team Onboarding instructions. The agent reads these from the onboarding context at runtime. Do not hardcode environment values in this skill.

Authentication: Use the agent's built-in tools (RunAzCliReadCommands, GetArmResourceAsJson, QueryLogAnalyticsByWorkspaceId, GetMetricTimeSeriesElementsForAzureResource) for Azure API calls. These authenticate automatically via the agent's Managed Identity.

Data Reuse (AAU Optimization): Before calling any API or proxy, check if the data was already retrieved earlier in this conversation. Reuse landscape registry, VM power states, config files, and AMS query results from context. Do not re-fetch data that is already available.

Config reads & proxy fallback: Stored SAP/OS configs are read directly from the sap-configs blob container using the agent's own Managed Identity (--auth-mode login) — there is no config proxy. The MCP command proxy is optional and runs only live VM commands; if it is not deployed or errors (timeout, 5xx, unreachable), continue with stored blob configs + Azure-native sources (AMS, ARM API, Azure Monitor). Never block the skill on the proxy.

Telemetry source adaptation: Forecasting depth depends on which telemetry source is listed in ## Deployed Infrastructure. Adapt automatically — never hard-fail because a specific source is absent:

• AMS (Azure Monitor for SAP) listed → forecast from the AMS KQL series below (HANA memory, MVCC, HSR lag).
• No AMS, but an SAP APM connector is listed (SAP Cloud ALM / Focus Run / Dynatrace) → pull HANA memory/lag series from the SAP APM Connector; use Azure Monitor for disk/CPU trends.
• No SAP telemetry source at all → forecast only Azure platform metrics (disk %, VM CPU/memory) and disclose: "HANA-internal trends need AMS or an APM connector; forecasting is limited to Azure platform metrics."

Mode Selection

• User asks "Analyze memory trends for AB1" → On-demand analysis
• Scheduled (every 6 hours via CreateScheduledMonitoringTask) → Continuous monitoring
• Threshold crossed → Alert via Teams + recommend action + await approval

When to Use

• "Is HANA running out of memory?"
• "Predict when AB1 will hit disk capacity"
• "Memory trend analysis for AB3"
• "Any anomalies on HSO?"
• "When will /hana/log fill up?"

Topology handling (all 8 system types)

Read the system's architecture + deployment and forecast per node, not per SID:

• scale-out → trend each DB node separately (group by HOST_s); forecast per-worker imbalance and standby-node headroom — an aggregate hides a single node filling up.
• standalone / distributed → skip HSR-lag trending (no replication).
• high-availability → trend sync HSR lag against the HA baseline.
• disaster-recovery → trend the async DR replica lag against its own (looser) baseline, separately from any sync HA lag.

Authentication

IMPORTANT — Azure API Access: Do NOT use IMDS tokens (169.254.169.254) or ManagedIdentityCredential — they are not available in the agent sandbox. Instead:

• For Azure Resource Manager queries: Use the built-in GetArmResourceAsJson or RunAzCliReadCommands tools
• For Log Analytics queries: Use the built-in QueryLogAnalyticsByWorkspaceId tool
• For metrics: Use the built-in GetMetricTimeSeriesElementsForAzureResource tool
• For live VM commands: invoke the SAP Command Runner skill (the sap-sre-proxy MCP connector) — never make direct HTTP or proxy calls

# Use the built-in tools above for Azure API access.
import json, math
from datetime import datetime, timedelta, timezone

# SUB_ID: Use subscription_id from Team Onboarding
# AMS_WORKSPACE_ID: Use ams_workspace_id from Team Onboarding (only if AMS is listed)

# Live VM state (e.g. df -h) is optional: invoke the SAP Command Runner skill (MCP run_batch)
# only when the MCP command proxy is configured. Otherwise use Azure Monitor disk % / blob configs.
# When T3 mode needs to act on a recommendation, invoke the SAP Command Runner skill — never call the proxy directly.

Forecasting Metrics

Metric	Primary Source	Fallback Source	Query Window	Alert Threshold
HANA memory utilization	SapHana_LoadHistory_CL	—	7 days	Projected OOM within 72h
/hana/log utilization	MCP proxy (run_batch: df -h)	Azure Monitor disk % or blob df	7 days	Projected full within 48h
/hana/data utilization	Azure Monitor disk %	—	7 days	Projected full within 72h
Host CPU trend	SapHana_LoadHistory_CL	—	7 days	Sustained >90% for 4h+
HSR replication lag	SapHana_SystemReplication_CL	—	24h	Increasing trend (lag growing)
MVCC version count	SapHana_Mvcc_CL	—	24h	Monotonically increasing

Trend Analysis — Linear Regression

def linear_regression(data_points):
    """Simple linear regression on (timestamp, value) pairs.
    Returns slope, intercept, r_squared, and projected crossover time."""
    n = len(data_points)
    if n < 10:
        return None  # Insufficient data

    x = [p[0] for p in data_points]  # timestamps as epoch seconds
    y = [p[1] for p in data_points]  # metric values

    sum_x = sum(x)
    sum_y = sum(y)
    sum_xy = sum(xi * yi for xi, yi in zip(x, y))
    sum_x2 = sum(xi ** 2 for xi in x)

    denom = n * sum_x2 - sum_x ** 2
    if denom == 0:
        return None

    slope = (n * sum_xy - sum_x * sum_y) / denom
    intercept = (sum_y - slope * sum_x) / n

    # R-squared
    y_mean = sum_y / n
    ss_res = sum((yi - (slope * xi + intercept)) ** 2 for xi, yi in zip(x, y))
    ss_tot = sum((yi - y_mean) ** 2 for yi in y)
    r_squared = 1 - (ss_res / ss_tot) if ss_tot > 0 else 0

    return {"slope": slope, "intercept": intercept, "r_squared": r_squared}

def project_crossover(regression, threshold, current_time_epoch):
    """Project when a metric will cross a threshold."""
    if regression is None or regression["slope"] <= 0:
        return None  # No upward trend
    crossover_epoch = (threshold - regression["intercept"]) / regression["slope"]
    hours_until = (crossover_epoch - current_time_epoch) / 3600
    return hours_until if hours_until > 0 else None

HANA Memory Trend Query

For scale-up there is one DB host per SID. For scale-out, add HOST_s to the grouping so each master/worker node is trended separately — never average across nodes (a hot worker gets hidden by the aggregate):

Run getschema FIRST (mandatory): SapHana_LoadHistory_CL | getschema (and any other SapHana_*_CL / Prometheus_*_CL table you trend). Memory/CPU column names vary by AMS collector version — the MEMORY_USED_d / MEMORY_SIZE_d fields below are a template; confirm the real names before running. Also state the actual data window available (a newly onboarded or recently started system may have only a short history) and don't extrapolate a trend from too few points.

SapHana_LoadHistory_CL
| where TimeGenerated > ago(7d)
| where sapsid_s == "<SID>"
| summarize avg_mem_pct = avg(MEMORY_USED_d / MEMORY_SIZE_d * 100) by bin(TimeGenerated, 1h), HOST_s
| order by HOST_s asc, TimeGenerated asc

Action Recommendations

Projected Event	Time Horizon	Recommended Action	Auto-Executable
HANA OOM	<72h	Identify top memory consumers, recommend service restart	Yes (with approval)
HANA OOM	<12h	Emergency: restart non-critical services	Yes (with approval) + P1 escalation
/hana/log full	<48h	Trigger log backup + catalog cleanup	Yes (with approval)
/hana/log full	<12h	Emergency log backup	Yes (via T4 Self-Healing)
CPU sustained >90%	>4h	Identify top consumers, check for runaway queries	No (investigation)
MVCC growing	>24h trend	Identify long-running transaction	No (investigation)

T3 Approval Flow

def recommend_action(metric, projection, system):
    """Generate recommendation for approval."""
    recommendation = {
        "system": system,
        "metric": metric,
        "current_value": projection["current"],
        "projected_threshold_hours": projection["hours_until"],
        "recommended_action": projection["action"],
        "risk_level": "LOW" if projection["hours_until"] > 48 else "MEDIUM",
        "auto_executable": projection["auto_executable"]
    }
    # In production: send Teams Adaptive Card via Teams connector
    # In demo: display in chat and ask for approval
    return recommendation

Output Format

SAP Anomaly Forecast — AB1

📈 HANA Memory: 72% → trending +1.5%/day (R²=0.89)
   Projected OOM: ~18 days (May 25)
   Status: 🟡 WATCH (no immediate action needed)

📈 /hana/log: 45% → stable (slope ≈ 0)
   Status: 🟢 STABLE

📈 CPU: 34% avg → stable
   Status: 🟢 STABLE

📈 MVCC versions: 45,000 → stable
   Status: 🟢 STABLE

No immediate actions required. Next check in 6 hours.

Or when threshold is crossed:

🔴 SAP Anomaly Alert — AB1

📈 HANA Memory: 89% → trending +3.2%/day (R²=0.94)
   Projected OOM: ~36 hours (May 9 15:00 UTC)

   TOP MEMORY CONSUMERS:
   1. indexserver: 28 GB (62% of allocated)
   2. preprocessor: 4 GB (9%)
   3. compileserver: 2 GB (4%)

   RECOMMENDED ACTION: Restart preprocessor service (4 GB freed)
   Risk: LOW (non-critical service, auto-restarts)

   [Approve Restart] [Deny] [Show Full Analysis]