MCP Agents and Client Families

An agent is an active MCP API key. Client family comes from self-reported MCP clientInfo.name snapshotted on runs; it is not proof of the upstream LLM provider or model.

Setup

Mix.install([
  {:postgrex, "~> 0.22.0"},
  {:kino, "~> 0.19.0"},
  {:kino_vega_lite, "~> 0.1.13"}
])

Code.require_file("/opt/emisar/product_analytics.exs")
alias EmisarProductAnalytics, as: Analytics
db = Analytics.connect!()

Agent inventory

agent_inventory =
  Analytics.query(db, """
  SELECT
    count(*) FILTER (WHERE revoked_at IS NULL AND deleted_at IS NULL AND expires_at > now() AND created_by_membership_id IS NOT NULL AND (auto_generated_at IS NULL OR last_used_at IS NOT NULL)) AS active_agents,
    count(*) FILTER (WHERE revoked_at IS NULL AND deleted_at IS NULL AND expires_at > now() AND created_by_membership_id IS NOT NULL AND (auto_generated_at IS NULL OR last_used_at IS NOT NULL) AND last_used_at IS NOT NULL) AS connected_agents,
    count(*) FILTER (WHERE revoked_at IS NULL AND deleted_at IS NULL AND expires_at > now() AND created_by_membership_id IS NOT NULL AND auto_generated_at IS NULL AND last_used_at IS NULL) AS never_used_agents,
    count(DISTINCT account_id) FILTER (WHERE revoked_at IS NULL AND deleted_at IS NULL AND expires_at > now() AND created_by_membership_id IS NOT NULL AND (auto_generated_at IS NULL OR last_used_at IS NOT NULL)) AS accounts_with_agents
  FROM api_keys
  WHERE kind = 'mcp'
  """)
  |> hd()

Analytics.kpis([
  {"Active agents", agent_inventory["active_agents"]},
  {"Connected agents", agent_inventory["connected_agents"]},
  {"Never used", agent_inventory["never_used_agents"]},
  {"Accounts with agents", agent_inventory["accounts_with_agents"]}
])

Agents per account

agents_by_account =
  Analytics.query(db, """
  SELECT a.name AS account,
    count(k.id) FILTER (WHERE k.revoked_at IS NULL AND k.deleted_at IS NULL AND k.expires_at > now() AND k.created_by_membership_id IS NOT NULL AND (k.auto_generated_at IS NULL OR k.last_used_at IS NOT NULL)) AS active_agents,
    count(k.id) FILTER (WHERE k.revoked_at IS NULL AND k.deleted_at IS NULL AND k.expires_at > now() AND k.created_by_membership_id IS NOT NULL AND (k.auto_generated_at IS NULL OR k.last_used_at IS NOT NULL) AND k.last_used_at IS NOT NULL) AS connected_agents,
    max(k.last_used_at) FILTER (WHERE k.revoked_at IS NULL AND k.deleted_at IS NULL AND k.expires_at > now() AND k.created_by_membership_id IS NOT NULL AND (k.auto_generated_at IS NULL OR k.last_used_at IS NOT NULL)) AS last_agent_use
  FROM accounts a
  LEFT JOIN api_keys k ON k.account_id = a.id AND k.kind = 'mcp'
  WHERE a.deleted_at IS NULL
  GROUP BY a.id, a.name
  ORDER BY active_agents DESC, a.name
  LIMIT 500
  """)

Analytics.table(agents_by_account)

MCP client families, 30 days

client_families =
  Analytics.query(db, """
  WITH named AS (
    SELECT CASE
      WHEN lower(coalesce(client_info->>'name', '')) LIKE '%claude%' THEN 'Claude clients'
      WHEN lower(coalesce(client_info->>'name', '')) LIKE '%codex%' THEN 'Codex clients'
      WHEN lower(coalesce(client_info->>'name', '')) LIKE '%gemini%' THEN 'Gemini clients'
      WHEN coalesce(client_info->>'name', '') = '' THEN 'Unreported'
      ELSE client_info->>'name'
    END AS client_family
    FROM action_runs
    WHERE source = 'mcp' AND inserted_at >= now() - interval '30 days'
  )
  SELECT client_family, count(*) AS runs
  FROM named
  GROUP BY client_family
  ORDER BY runs DESC
  LIMIT 30
  """)

Analytics.bar(client_families, "client_family", "runs", title: "Self-reported MCP client families", y_title: "MCP runs")

Daily MCP use

daily_mcp =
  Analytics.query(db, """
  WITH days AS (
    SELECT generate_series(current_date - 89, current_date, interval '1 day')::date AS day
  )
  SELECT to_char(days.day, 'YYYY-MM-DD') AS day,
    count(ar.id) AS mcp_runs,
    count(DISTINCT ar.account_id) AS active_accounts
  FROM days
  LEFT JOIN action_runs ar ON ar.inserted_at::date = days.day AND ar.source = 'mcp'
  GROUP BY days.day
  ORDER BY days.day
  """)

Analytics.line(daily_mcp, "day", "mcp_runs", title: "Daily MCP runs", y_title: "Runs")

Agent detail

agents =
  Analytics.query(db, """
  SELECT a.name AS account, k.name AS agent, k.inserted_at, k.last_used_at, k.expires_at,
    k.last_client_info->>'name' AS last_client_name,
    k.last_client_info->>'version' AS last_client_version
  FROM api_keys k
  JOIN accounts a ON a.id = k.account_id AND a.deleted_at IS NULL
  WHERE k.kind = 'mcp'
    AND k.deleted_at IS NULL
    AND k.revoked_at IS NULL
    AND k.expires_at > now()
    AND k.created_by_membership_id IS NOT NULL
    AND (k.auto_generated_at IS NULL OR k.last_used_at IS NOT NULL)
  ORDER BY k.last_used_at DESC NULLS LAST
  LIMIT 500
  """)

Analytics.table(agents)