Cloud Asset Inventoryからサポート切れするAppEngine versionを見つける方法

以下の資料によりますと、いくつかのAppEngineの言語バージョンが近い将来にサポート切れを迎えます。

そのため、Cloud Asset Inventoryの情報からサポート切れ間近のAppEngine情報を抜き出してみます。

-- 以下のCTEはGoogle公式ドキュメントのURLをChatGPTに投げて作ってもらったもの
with support_ends as (
  SELECT 'Go' AS language, '1.21' AS version, 'Ubuntu 22.04' AS environment, 'go121' AS runtime_id, NULL AS support_end_date, NULL AS deprecated_end_date
  UNION ALL
  SELECT 'Go', '1.20', 'Ubuntu 22.04', 'go120', NULL, NULL
  UNION ALL
  SELECT 'Go', '1.19', 'Ubuntu 22.04', 'go119', DATE '2024-04-30', DATE '2025-04-30'
  UNION ALL
  SELECT 'Go', '1.18', 'Ubuntu 22.04', 'go118', DATE '2024-01-30', DATE '2025-01-31'
  UNION ALL
  SELECT 'Go', '1.16', 'Ubuntu 18.04', 'go116', DATE '2024-01-30', DATE '2025-01-31'
  UNION ALL
  SELECT 'Go', '1.15', 'Ubuntu 18.04', 'go115', DATE '2024-01-30', DATE '2025-01-31'
  UNION ALL
  SELECT 'Go', '1.14', 'Ubuntu 18.04', 'go114', DATE '2024-01-30', DATE '2025-01-31'
  UNION ALL
  SELECT 'Go', '1.13', 'Ubuntu 18.04', 'go113', DATE '2024-01-30', DATE '2025-01-31'
  UNION ALL
  SELECT 'Go', '1.12', 'Ubuntu 18.04', 'go112', DATE '2024-01-30', DATE '2025-01-31'
  UNION ALL
  SELECT 'Go', '1.11', 'Ubuntu 18.04', 'go111', DATE '2024-01-30', DATE '2026-01-31'
  UNION ALL
  SELECT 'Java', '17', 'Ubuntu 22.04', 'java17', DATE '2027-10-01', NULL
  UNION ALL
  SELECT 'Java', '11', 'Ubuntu 18.04', 'java11', DATE '2024-10-01', NULL
  UNION ALL
  SELECT 'Java', '8', 'App Engine 1st Gen', 'java8', DATE '2024-01-31', DATE '2026-01-31'
  UNION ALL
  SELECT 'Node.js', '20', 'Ubuntu 22.04', 'nodejs20', DATE '2026-04-30', DATE '2027-04-30'
  UNION ALL
  SELECT 'Node.js', '18', 'Ubuntu 22.04', 'nodejs18', DATE '2025-04-30', DATE '2026-04-30'
  UNION ALL
  SELECT 'Node.js', '16', 'Ubuntu 18.04', 'nodejs16', DATE '2024-01-30', DATE '2025-01-31'
  UNION ALL
  SELECT 'Node.js', '14', 'Ubuntu 18.04', 'nodejs14', DATE '2024-01-30', DATE '2025-01-31'
  UNION ALL
  SELECT 'Node.js', '12', 'Ubuntu 18.04', 'nodejs12', DATE '2024-01-30', DATE '2025-01-31'
  UNION ALL
  SELECT 'Node.js', '10', 'Ubuntu 18.04', 'nodejs10', DATE '2024-01-30', DATE '2025-01-31'
  UNION ALL
  SELECT 'Node.js', '8', 'Ubuntu 18.04', 'nodejs8', DATE '2018-06-27', DATE '2019-10-01'
  UNION ALL
  SELECT 'Python', '3.12', 'Ubuntu 22.04', 'python312', DATE '2028-10-02', DATE '2029-10-02'
  UNION ALL
  SELECT 'Python', '3.11', 'Ubuntu 22.04', 'python311', DATE '2027-10-24', DATE '2028-10-24'
  UNION ALL
  SELECT 'Python', '3.10', 'Ubuntu 22.04', 'python310', DATE '2026-10-04', DATE '2027-10-01'
  UNION ALL
  SELECT 'Python', '3.9', 'Ubuntu 18.04', 'python39', DATE '2025-10-05', DATE '2026-10-01'
  UNION ALL
  SELECT 'Python', '3.8', 'Ubuntu 18.04', 'python38', DATE '2024-10-14', DATE '2025-10-01'
  UNION ALL
  SELECT 'Python', '3.7', 'Ubuntu 18.04', 'python37', DATE '2024-01-30', DATE '2025-01-31'
  UNION ALL
  SELECT 'Python', '2.7', 'App Engine 1st Gen', 'python27', DATE '2024-01-31', DATE '2026-01-31'
  UNION ALL
  SELECT 'PHP', '8.2', 'Ubuntu 22.04', 'php82', DATE '2025-12-08', DATE '2026-12-08'
  UNION ALL
  SELECT 'PHP', '8.1', 'Ubuntu 18.04', 'php81', DATE '2024-11-25', DATE '2025-11-25'
  UNION ALL
  SELECT 'PHP', '7.4', 'Ubuntu 18.04', 'php74', DATE '2024-01-30', DATE '2025-01-31'
  UNION ALL
  SELECT 'PHP', '7.3', 'Ubuntu 18.04', 'php73', DATE '2024-01-30', DATE '2025-01-31'
  UNION ALL
  SELECT 'PHP', '7.2', 'Ubuntu 18.04', 'php72', DATE '2024-01-30', DATE '2025-01-31'
  UNION ALL
  SELECT 'PHP', '5.5', 'App Engine 1st Gen', 'php55', DATE '2024-01-30', DATE '2026-01-31'
  UNION ALL
  SELECT 'Ruby', '3.2', 'Ubuntu 22.04', 'ruby32', DATE '2026-03-31', DATE '2027-03-31'
  UNION ALL
  SELECT 'Ruby', '3.0', 'Ubuntu 18.04', 'ruby30', DATE '2024-03-31', DATE '2025-03-31'
  UNION ALL
  SELECT 'Ruby', '2.7', 'Ubuntu 18.04', 'ruby27', DATE '2024-01-30', DATE '2025-01-31'
  UNION ALL
  SELECT 'Ruby', '2.6', 'Ubuntu 18.04', 'ruby26', DATE '2024-01-30', DATE '2025-01-31'
  UNION ALL
  SELECT 'Ruby', '2.5', 'Ubuntu 18.04', 'ruby25', DATE '2024-01-30', DATE '2025-01-31'
)

select
  app_engine_versions.*,
  support_ends.support_end_date,
from (
  select
    regexp_extract(name, r'//appengine.googleapis.com/apps/(.+)/services/.+/versions/.+') as project_id,
    regexp_extract(name, r'//appengine.googleapis.com/apps/.+/services/(.+)/versions/.+') as service,
    json_value(resource.data, "$.runtime") as runtime,
    timestamp(json_value(resource.data, "$.createTime")) as create_time,
    *
  from <Cloud Asset InventoryのResourceデータ>
  where asset_type = 'appengine.googleapis.com/Version' and json_value(resource.data, "$.env") = 'standard'
  qualify row_number() over (partition by project_id, service order by create_time desc) = 1
) as app_engine_versions
join support_ends on app_engine_versions.runtime = support_ends.runtime_id
where support_end_date < current_date('Asia/Tokyo') + interval 365 day -- 1年以内にサポート切れ
order by project_id, service