AWS Timestream SQL Query Patterns for MCP Server Uptime Analysis

TypeScript query client setup

import {
  TimestreamQueryClient,
  QueryCommand,
  type QueryResponse,
  type Row,
  type ColumnInfo,
} from '@aws-sdk/client-timestream-query';

const tsQuery = new TimestreamQueryClient({ region: process.env.AWS_REGION ?? 'us-east-1' });

// Helper: paginate through all query results
async function* queryAllRows(queryString: string): AsyncGenerator<Row[]> {
  let nextToken: string | undefined;
  let columnInfo: ColumnInfo[] | undefined;

  do {
    const response: QueryResponse = await tsQuery.send(new QueryCommand({
      QueryString: queryString,
      NextToken: nextToken,
    }));

    columnInfo = response.ColumnInfo ?? [];
    yield response.Rows ?? [];
    nextToken = response.NextToken;
  } while (nextToken);
}

// Helper: convert a Timestream row to a plain object using ColumnInfo names
function rowToObject(row: Row, columns: ColumnInfo[]): Record<string, string> {
  return Object.fromEntries(
    columns.map((col, i) => [col.Name!, row.Data?.[i]?.ScalarValue ?? ''])
  );
}

NextToken pagination is mandatory for queries that return more than 1,000 rows. The monitoring dashboard queries in this guide typically return <100 rows (one per server per time bucket), so a single-page response is normal. Use the paginated generator for queries against the full 90-day corpus, such as SLA PDF generation or export jobs.

Uptime percentage: 24-hour, 7-day, and 90-day

-- 24-hour uptime percentage per server slug (last 24 hours)
SELECT
  server_slug,
  ROUND(
    COUNT_IF(is_up = 'true') * 100.0 / NULLIF(COUNT(*), 0),
    2
  ) AS uptime_pct_24h,
  COUNT(*) AS total_probes,
  COUNT_IF(is_up = 'false') AS failed_probes
FROM "mcp-probe-metrics"."probe-results"
WHERE time BETWEEN ago(24h) AND now()
GROUP BY server_slug
ORDER BY uptime_pct_24h ASC;

-- 7-day uptime percentage
SELECT
  server_slug,
  ROUND(COUNT_IF(is_up = 'true') * 100.0 / NULLIF(COUNT(*), 0), 2) AS uptime_pct_7d
FROM "mcp-probe-metrics"."probe-results"
WHERE time BETWEEN ago(7d) AND now()
GROUP BY server_slug;

-- 90-day uptime (queries magnetic store — add ORDER BY for determinism)
SELECT
  server_slug,
  ROUND(COUNT_IF(is_up = 'true') * 100.0 / NULLIF(COUNT(*), 0), 2) AS uptime_pct_90d
FROM "mcp-probe-metrics"."probe-results"
WHERE time BETWEEN ago(90d) AND now()
GROUP BY server_slug
ORDER BY server_slug;

The NULLIF(COUNT(*), 0) guard prevents division by zero for new servers with no probe records in the window. The ago() function is evaluated at query execution time to the current timestamp minus the interval — it is not a constant and does not need to be replaced with a static timestamp.

Binned latency with bin() and APPROX_PERCENTILE

-- Hourly P50/P95/P99 response time for a specific server over the last 7 days
SELECT
  bin(time, 1h) AS hour,
  APPROX_PERCENTILE(response_ms, 0.50) AS p50_ms,
  APPROX_PERCENTILE(response_ms, 0.95) AS p95_ms,
  APPROX_PERCENTILE(response_ms, 0.99) AS p99_ms,
  MAX(response_ms) AS max_ms,
  COUNT(*) AS probe_count
FROM "mcp-probe-metrics"."probe-results"
WHERE
  server_slug = 'github-mcp'
  AND time BETWEEN ago(7d) AND now()
  AND is_up = 'true'   -- exclude failed probes from latency stats
GROUP BY bin(time, 1h)
ORDER BY hour ASC;

-- 5-minute granularity for the live dashboard (last 3 hours, memory store)
SELECT
  bin(time, 5m) AS bucket,
  AVG(response_ms) AS avg_ms,
  COUNT_IF(is_up = 'false') AS errors
FROM "mcp-probe-metrics"."probe-results"
WHERE
  server_slug = 'stripe-mcp'
  AND time BETWEEN ago(3h) AND now()
GROUP BY bin(time, 5m)
ORDER BY bucket ASC;

bin(time, interval) rounds each timestamp down to the start of its interval bucket. The interval can be any combination of ns (nanoseconds), us, ms, s, m, h, d. For live dashboard queries targeting the memory store, keep the bin size ≥ 1 minute — finer granularity multiplies the row count without adding useful signal for most monitoring dashboards.

APPROX_PERCENTILE uses a t-digest algorithm and is accurate to ±1% relative error. It is significantly faster and cheaper than exact percentile calculations, especially on large magnetic store scans. Use exact PERCENTILE_CONT only for compliance reports where the precise value matters.

Schema drift detection

AliveMCP stores a SHA-256 checksum of each server's tools/list response. When the checksum changes between consecutive probes, the server's tool schema has changed — tools were added, removed, or renamed. Timestream's LEAD() window function detects these changes efficiently:

-- Find schema changes in the last 7 days for all servers
-- (checksum changes = tool schema drift events)
SELECT
  server_slug,
  time AS drift_detected_at,
  schema_checksum AS new_checksum,
  prev_checksum
FROM (
  SELECT
    server_slug,
    time,
    schema_checksum,
    LAG(schema_checksum) OVER (
      PARTITION BY server_slug
      ORDER BY time ASC
    ) AS prev_checksum
  FROM "mcp-probe-metrics"."probe-results"
  WHERE
    time BETWEEN ago(7d) AND now()
    AND is_up = 'true'  -- only compare checksums from successful probes
)
WHERE
  prev_checksum IS NOT NULL
  AND schema_checksum != prev_checksum
ORDER BY server_slug, drift_detected_at ASC;

Note: LAG() is available in Timestream SQL. The outer query filters to rows where the checksum changed from the previous row — these are the schema drift events. prev_checksum IS NOT NULL excludes the first probe for each server, which has no previous value to compare against.

Downtime window extraction

Extracting the start and end time of each downtime window requires identifying consecutive runs of is_up = 'false' records. Timestream does not have a direct SESSIONIZE or gap-and-island function, but you can approximate it with a subquery that marks the start of each new down-run:

-- Identify downtime start/end windows using a sequence number approach
-- Step 1: label each row with is_down and a sequential row number per server
WITH ordered AS (
  SELECT
    server_slug,
    time,
    is_up,
    ROW_NUMBER() OVER (PARTITION BY server_slug ORDER BY time) AS rn
  FROM "mcp-probe-metrics"."probe-results"
  WHERE
    server_slug = 'github-mcp'
    AND time BETWEEN ago(7d) AND now()
),
-- Step 2: detect transitions from up→down
transitions AS (
  SELECT
    cur.server_slug,
    cur.time AS event_time,
    cur.is_up AS current_status,
    LAG(cur.is_up) OVER (PARTITION BY cur.server_slug ORDER BY cur.time) AS prev_status
  FROM ordered cur
),
-- Step 3: find start of each down window
down_starts AS (
  SELECT server_slug, event_time AS down_start
  FROM transitions
  WHERE current_status = 'false' AND (prev_status = 'true' OR prev_status IS NULL)
),
-- Step 4: find end of each down window (first up after each down start)
down_ends AS (
  SELECT server_slug, event_time AS down_end,
    LAG(event_time) OVER (PARTITION BY server_slug ORDER BY event_time) AS prior_down_start
  FROM transitions
  WHERE current_status = 'true' AND prev_status = 'false'
)
SELECT
  ds.server_slug,
  ds.down_start,
  de.down_end,
  date_diff('minute', ds.down_start, COALESCE(de.down_end, now())) AS duration_minutes
FROM down_starts ds
LEFT JOIN down_ends de
  ON ds.server_slug = de.server_slug
  AND de.down_end > ds.down_start
ORDER BY ds.down_start ASC;

The COALESCE(de.down_end, now()) handles currently-ongoing downtime: if the server is still down, the downtime window has no end time yet, so now() is used as a placeholder for the duration calculation.

Scheduled queries: materializing SLA aggregates

Queries that scan 90 days of magnetic store data for all monitored servers take seconds to minutes and cost per GB scanned. For SLA dashboards and weekly digest emails, materialize these aggregates into a Timestream derived table using Scheduled Queries.

import * as timestream from 'aws-cdk-lib/aws-timestream';
import * as iam from 'aws-cdk-lib/aws-iam';

// Scheduled query: run every hour, write 24h uptime summary to derived table
const scheduledQuery = new timestream.CfnScheduledQuery(this, 'UptimeSummaryQuery', {
  queryString: `
    SELECT
      bin(time, 1h) AS interval_start,
      server_slug,
      ROUND(COUNT_IF(is_up = 'true') * 100.0 / NULLIF(COUNT(*), 0), 2) AS uptime_pct_1h,
      APPROX_PERCENTILE(response_ms, 0.95) AS p95_response_ms,
      COUNT(*) AS probe_count
    FROM "mcp-probe-metrics"."probe-results"
    WHERE time BETWEEN ago(2h) AND now()
    GROUP BY bin(time, 1h), server_slug
  `,
  scheduleConfiguration: {
    scheduleExpression: 'rate(1 hour)',
  },
  targetConfiguration: {
    timestreamConfiguration: {
      databaseName: 'mcp-probe-metrics',
      tableName: 'uptime-hourly-summary',  // derived table for fast dashboard reads
      timeColumn: 'interval_start',
      dimensionMappings: [
        { name: 'server_slug', dimensionValueType: 'VARCHAR' },
      ],
      multiMeasureMappings: {
        targetMultiMeasureName: 'summary',
        multiMeasureAttributeMappings: [
          { sourceColumn: 'uptime_pct_1h', measureValueType: 'DOUBLE' },
          { sourceColumn: 'p95_response_ms', measureValueType: 'DOUBLE' },
          { sourceColumn: 'probe_count', measureValueType: 'BIGINT' },
        ],
      },
    },
  },
  notificationConfiguration: {
    snsConfiguration: {
      topicArn: alertTopic.topicArn,
    },
  },
  scheduledQueryExecutionRoleArn: scheduledQueryRole.roleArn,
  errorReportConfiguration: {
    s3Configuration: {
      bucketName: errorBucket.bucketName,
      objectKeyPrefix: 'scheduled-query-errors/',
      encryptionOption: 'SSE_S3',
    },
  },
});

Once the scheduled query runs, the uptime-hourly-summary derived table holds pre-aggregated hourly buckets. Dashboard queries against this table scan kilobytes instead of gigabytes, reducing both cost and latency by orders of magnitude. The scheduled query runs every hour and writes 2 hours of data each time — the 1-hour overlap ensures no gaps even if a run is slightly delayed.

IAM policy for the query path

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "TimestreamQueryRead",
      "Effect": "Allow",
      "Action": [
        "timestream:Select",
        "timestream:DescribeTable",
        "timestream:ListMeasures",
        "timestream:ListTables",
        "timestream:ListDatabases"
      ],
      "Resource": [
        "arn:aws:timestream:us-east-1:123456789012:database/mcp-probe-metrics/*",
        "arn:aws:timestream:us-east-1:123456789012:database/mcp-probe-metrics"
      ]
    },
    {
      "Sid": "TimestreamEndpointDiscovery",
      "Effect": "Allow",
      "Action": ["timestream:DescribeEndpoints"],
      "Resource": "*"
    }
  ]
}

Read-only consumers (dashboard API, status page renderer) should have this policy and nothing else. Probe workers should have the write policy from the WriteRecords guide, not the read policy. Keep write and read permissions on separate IAM roles — if the dashboard API is compromised, the attacker cannot poison historical probe data.

Failure modes