MCP Server Insights

system("You are an analytical assistant querying BigQuery for MCP server telemetry.")

parameter("servername", type=string, title="Server Name")

require mcp BigQuery

components {
    schema("TenantStats") {
        tenant_name: string
        tenant_id: string
        status: string
        total_tool_calls: int
        total_errors: int
        error_rate: float
        active_users: int
        distinct_tools_called: int
        active_days: int
        first_call: string
        last_call: string
        days_since_last_call: int
        connected_but_never_called: bool
    }

    schema("DailyTrend") {
        event_date: string
        total_calls: int
        errors: int
        error_rate: float
        active_tenants: int
    }

    schema("ToolStats") {
        tool_name: string
        total_calls: int
        errors: int
        error_rate: float
        tenant_count: int
    }

    schema("ErrorPattern") {
        tenant_id: string
        mcp: string
        tool_name: string
        error_message: string
        occurrences: int
        last_seen: string
    }

    schema("UserActivity") {
        tenant_id: string
        tenant_name: string
        user_id: string
        total_exec_calls: int
        last_active_date: string
        stage: string
        recency: string
    }
}

session("tenant_breakdown") {
    use mcp BigQuery
    
    + Run this BigQuery SQL to get the per-tenant breakdown for the '{{ .params.servername }}' MCP server:
      ```sql
      SELECT
        tenant_name,
        tenant_id,
        status,
        total_tool_calls,
        total_errors,
        SAFE_DIVIDE(total_errors, NULLIF(total_tool_calls, 0))  AS error_rate,
        active_users,
        distinct_tools_called,
        active_days,
        CAST(first_tool_call AS STRING)                         AS first_call,
        CAST(last_tool_call AS STRING)                          AS last_call,
        days_since_last_call,
        connected_but_never_called
      FROM `barndoor-production.gtm_analytics.mcp_block`
      WHERE mcp_name = '{{ .params.servername }}'
      -- mcp_block has one row per (tenant, server, status). Collapse to a single
      -- representative row per tenant so a tenant with Active+Pending+Error
      -- connections isn't triple-counted; prefer Error so problems surface.
      QUALIFY ROW_NUMBER() OVER (
        PARTITION BY tenant_id
        ORDER BY CASE LOWER(status) WHEN 'error' THEN 0 WHEN 'active' THEN 1 WHEN 'pending' THEN 2 ELSE 3 END,
                 total_tool_calls DESC
      ) = 1
      ORDER BY total_tool_calls DESC;
      ```
    
    - Map the query results into the schema.
    
    schema $TenantStats[]
}

session("daily_trend", after="tenant_breakdown") {
    use mcp BigQuery
    
    + Run this BigQuery SQL to get the daily call volume and error trend for the '{{ .params.servername }}' MCP server over the last 30 days:
      ```sql
      SELECT
        CAST(event_date AS STRING) AS event_date,
        SUM(call_count)   AS total_calls,
        SUM(error_count)  AS errors,
        SAFE_DIVIDE(SUM(error_count), NULLIF(SUM(call_count), 0)) AS error_rate,
        COUNT(DISTINCT tenant_id) AS active_tenants
      FROM `barndoor-production.gtm_analytics.fact_tool_call_daily`
      WHERE mcp = '{{ .params.servername }}'
        AND event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
      GROUP BY event_date
      ORDER BY event_date ASC;
      ```
      
    - Map the results.
    
    schema $DailyTrend[]
}

session("tool_breakdown", after="daily_trend") {
    use mcp BigQuery
    
    + Run this BigQuery SQL to get the tool-level breakdown for the '{{ .params.servername }}' MCP server:
      ```sql
      SELECT
        tool_name,
        SUM(call_count)  AS total_calls,
        SUM(error_count) AS errors,
        SAFE_DIVIDE(SUM(error_count), NULLIF(SUM(call_count), 0)) AS error_rate,
        COUNT(DISTINCT tenant_id) AS tenant_count
      FROM `barndoor-production.gtm_analytics.fact_tool_call_daily`
      WHERE mcp = '{{ .params.servername }}'
        AND event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
      GROUP BY tool_name
      ORDER BY total_calls DESC;
      ```
      
    - Extract and format the results.
    
    schema $ToolStats[]
}

session("error_patterns", after="tool_breakdown") {
    use mcp BigQuery
    
    + Run this BigQuery SQL to get error patterns for the '{{ .params.servername }}' MCP server:
      ```sql
      SELECT
        tenant_id,
        mcp,
        tool_name,
        error_message,
        COUNT(*)                        AS occurrences,
        MAX(CAST(timestamp AS STRING))  AS last_seen
      FROM `barndoor-production.gtm_analytics.tool_call_errors`
      WHERE mcp = '{{ .params.servername }}'
        AND event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
      GROUP BY tenant_id, mcp, tool_name, error_message
      ORDER BY occurrences DESC;
      ```
      
    - Extract and list the error patterns.
    
    schema $ErrorPattern[]
}

session("user_activity", after="error_patterns") {
    use mcp BigQuery
    
    + Run this BigQuery SQL to find the top user-level activity for the '{{ .params.servername }}' MCP server:
      ```sql
      SELECT
        u.tenant_id,
        m.tenant_name,
        u.user_id,
        u.total_exec_calls,
        CAST(u.last_active_date AS STRING) AS last_active_date,
        u.stage,
        u.recency
      FROM `barndoor-production.gtm_analytics.user_block` u
      JOIN `barndoor-production.gtm_analytics.mcp_block` m
        ON u.tenant_id = m.tenant_id
       AND m.mcp_name = '{{ .params.servername }}'
      WHERE u.total_exec_calls > 0
      GROUP BY u.tenant_id, m.tenant_name, u.user_id, u.total_exec_calls, u.last_active_date, u.stage, u.recency
      ORDER BY u.total_exec_calls DESC
      LIMIT 50;
      ```
      
    - Return the top users as defined by the query.
    
    schema $UserActivity[]
}

session("executive_summary", after="user_activity") {
    context "Tenant Data: {{ .context.tenant_breakdown | json }}\nDaily Trends: {{ .context.daily_trend | json }}\nTool Usage: {{ .context.tool_breakdown | json }}\nErrors: {{ .context.error_patterns | json }}\nUsers: {{ .context.user_activity | json }}"
    
    - You are a Product Manager and DevOps lead responsible for the success, adoption, and reliability of all enterprise MCP operations.
      Review the telemetry data gathered in the context above for the '{{ .params.servername }}' MCP server.
      Synthesize an executive summary that highlights overall health, adoption bottlenecks, error hotspots, and clear action items for the engineering and product teams.
      
    schema {
        overview: string       # High-level summary of the server's current health and adoption
        key_insights: string[] # Bullet points highlighting critical trends or concerning issues
        action_items: string[] # Recommended next steps for DevOps or PMs to improve reliability and success
    }
}