CloudQuery

SELECT
 product,
 cycle,
 latest,
 lts,
 support_until
FROM endoflife_products
WHERE support_until < DATE(NOW() + INTERVAL 30 DAY)
 AND support_until >= NOW()
ORDER BY product;

WITH
 now() AS current_time,
 current_time + toIntervalMonth(3) AS three_months_ahead

SELECT
 eks.name AS cluster_name,
 eks.version AS kubernetes_version,
 eol.product,
 eol.cycle,
 eol.eol,
 eol.eol_since,
 eol.support_until,
 multiIf(
     eol.eol = true, 'red',  -- Already unsupported
     eol.support_until <= three_months_ahead, 'yellow',  -- Soon unsupported
     'green'
 ) AS status
FROM aws_eks_clusters AS eks
LEFT JOIN endoflife_products AS eol
 ON eks.version = eol.cycle
WHERE eol.product = 'kubernetes'
  AND (eol.eol = true OR eol.support_until <= three_months_ahead);

aws eks list-nodegroups --cluster-name <cluster-name>

aws eks update-nodegroup-version --cluster-name <cluster-name> --nodegroup-name <node-group-name>

sh aws eks update-cluster-version --name --kubernetes-version

aws eks describe-cluster --name --query "cluster.version"

-- Set the current time and calculate the threshold date (30 days from now)
WITH
    now() AS now_time,
    now() + toIntervalDay(30) AS threshold,
    eol_data AS (
        SELECT
            lower(cycle) AS cycle,
            lower(product) AS product,
            eol_since,
        FROM endoflife_products
        WHERE lower(product) = 'amazon-rds-mysql'
    )
-- Retrieve RDS MySQL instances reaching EOL within the next 30 days
SELECT
    r.account_id,
    r.engine,
    r.major_engine_version,
    e.eol_since AS "RDS end of standard support date",
    dateDiff('day', now_time, e.eol_since) AS days_until_standard_support_end,
    multiIf(
        e.eol_since < now_time, 'Extended support active',
        e.eol_since <= threshold, 'Upgrade soon',
        'Standard support active'
    ) AS support_status
FROM aws_rds_engine_versions AS r
INNER JOIN eol_data AS e
    ON lower(r.engine) = replace(e.product, 'amazon-rds-', '')
    AND lower(r.major_engine_version) = e.cycle
WHERE e.eol_since <= threshold;

-- Set the current time and calculate six months ahead
WITH
    now() AS current_time,
    current_time + toIntervalMonth(6) AS six_months_ahead

-- Retrieve AWS Lambda functions with their version details and join them with Python EOL support data
SELECT
    f.arn AS function_arn,
    fv.function_name,
    fv.version,
    fv.runtime,
    eol.support_until,
    eol.eol,
    eol.eol_since
FROM aws_lambda_functions AS f
JOIN aws_lambda_function_versions AS fv ON f.arn = fv.function_arn
JOIN endoflife_products AS eol ON fv.runtime = eol.cycle
WHERE eol.product LIKE '%python%'
  AND eol.support_until < six_months_ahead
  AND eol.support_until >= current_time;

WITH
    -- Retrieve EOL details for Python, Node, and Go runtimes
    eol_data AS (
        SELECT
            lower(cycle) AS cycle,
            support_until,
            eol,
            eol_since,
            lower(product) AS product
        FROM endoflife_products
        WHERE lower(product) LIKE '%python%'
           OR lower(product) LIKE '%node%'
           OR lower(product) LIKE '%go%'
    ),

    -- Combine function data from AWS, Azure, GCP (v1 and v2)
    all_functions AS (
        -- AWS Lambda Functions: extract details from the configuration JSON
        SELECT
            f.arn AS function_id,
            JSONExtractString(f.configuration, 'FunctionName') AS function_name,
            lower(JSONExtractString(f.configuration, 'Version')) AS function_version,
            lower(JSONExtractString(f.configuration, 'Runtime')) AS runtime,
            'AWS' AS cloud_provider
        FROM aws_lambda_functions AS f
        WHERE lower(JSONExtractString(f.configuration, 'Runtime')) LIKE '%python%'
           OR lower(JSONExtractString(f.configuration, 'Runtime')) LIKE '%node%'
           OR lower(JSONExtractString(f.configuration, 'Runtime')) LIKE '%go%'

        UNION ALL

        -- Azure Functions: extract runtime from properties JSON and remove the "|" character;
        -- function_version is set to a constant 'latest'
        SELECT
            id AS function_id,
            name AS function_name,
            'latest' AS function_version,
            replaceAll(lower(regexpExtract(properties, '"linuxFxVersion":s*"([^"]+)"', 1)), '|', '') AS runtime,
            'Azure' AS cloud_provider
        FROM azure_appservice_web_apps
        WHERE lower(regexpExtract(properties, '"linuxFxVersion":s*"([^"]+)"', 1)) LIKE '%python%'
           OR lower(regexpExtract(properties, '"linuxFxVersion":s*"([^"]+)"', 1)) LIKE '%node%'
           OR lower(regexpExtract(properties, '"linuxFxVersion":s*"([^"]+)"', 1)) LIKE '%go%'

        UNION ALL

        -- GCP Cloud Functions (v1)
        SELECT
            name AS function_id,
            name AS function_name,
            CAST(version_id AS String) AS function_version,
            lower(runtime) AS runtime,
            'GCP' AS cloud_provider
        FROM gcp_functions_functions
        WHERE lower(runtime) LIKE '%python%'
           OR lower(runtime) LIKE '%node%'
           OR lower(runtime) LIKE '%go%'

        UNION ALL

        -- GCP Cloud Functions (v2) using build_config for runtime; function_version set to 'latest'
        SELECT
            name AS function_id,
            name AS function_name,
            'latest' AS function_version,
            lower(JSONExtractString(build_config, 'runtime')) AS runtime,
            'GCPv2' AS cloud_provider
        FROM gcp_functionsv2_functions
        WHERE lower(JSONExtractString(build_config, 'runtime')) LIKE '%python%'
           OR lower(JSONExtractString(build_config, 'runtime')) LIKE '%node%'
           OR lower(JSONExtractString(build_config, 'runtime')) LIKE '%go%'
    )

SELECT
    af.cloud_provider,
    multiIf(
        ed.eol = true OR ed.support_until < now(), 'red',
        ed.support_until < now() + toIntervalMonth(9), 'yellow',
        'green'
    ) AS status,
    af.runtime,
    CASE
      WHEN lower(af.function_version) = '$latest' THEN 'latest'
      ELSE af.function_version
    END AS function_version,
    af.function_id,
    af.function_name
FROM all_functions AS af
LEFT JOIN eol_data AS ed
    ON af.runtime = ed.cycle
ORDER BY status, cloud_provider;