Consultas en SQL de muestra de BigQuery

En esta página, se enumeran ejemplos de consultas en SQL para analizar los datos de tus elementos después de exportarlos a BigQuery. Consulta la sintaxis de consulta de SQL estándar para obtener más información.

Nota: Algunos campos experimentales pueden aparecer vacíos al principio en BigQuery. No establezcas una dependencia en las columnas afectadas hasta que se propaguen los campos. Para ver qué campos son públicos, consulta el documento de descubrimiento del recurso.

Consulta directamente en las columnas disponibles

Para encontrar la cantidad de cada tipo de recurso, ejecuta la siguiente consulta:

SELECT asset_type, COUNT(*) AS asset_count
FROM `PROJECT_ID.DATASET_ID.TABLE_NAME`
GROUP BY asset_type
ORDER BY asset_count DESC

Consulta en campos repetidos

Para encontrar las políticas de administración de identidades y accesos (IAM) que otorgan acceso a las cuentas de Gmail, ejecuta la siguiente consulta: BigQuery usa UNNEST para compactar campos repetidos en una tabla que puedes consultar directamente:

SELECT name, asset_type, bindings.role
FROM `PROJECT_ID.DATASET_ID.TABLE_NAME`
JOIN UNNEST(iam_policy.bindings) AS bindings
JOIN UNNEST(bindings.members) AS principals
WHERE principals like "[e-mail]"

Para buscar una organización, carpeta o proyecto que permita crear con una IP pública, ejecuta la siguiente consulta. Esta consulta es útil porque permitir direcciones IP públicas con instancias de Cloud SQL puede generar vulnerabilidades, a menos que se configure SSL o un proxy:

SELECT name
FROM `PROJECT_ID.DATASET_ID.TABLE_NAME`
JOIN UNNEST(org_policy) AS op
WHERE
  op.constraint = "constraints/sql.restrictPublicIp"
  AND (op.boolean_policy IS NULL OR op.boolean_policy.enforced = FALSE);

Para encontrar una organización, una carpeta o un proyecto en el mismo perímetro de servicio de los Controles del servicio de VPC de un proyecto, ejecuta la siguiente consulta:

SELECT service_perimeter.title, service_perimeter.status.resources
FROM `PROJECT_ID.DATASET_ID.TABLE_NAME`
CROSS JOIN UNNEST(service_perimeter.status.resources) as resource
WHERE resource = "projects/PROJECT_ID";

Consulta en la string JSON extraída

Para encontrar reglas de firewall abiertas, ejecuta la siguiente consulta. Obtén más información sobre las funciones JSON que se usan en BigQuery.

CREATE TEMP FUNCTION json2array(json STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
  return JSON.parse(json).map(x=>JSON.stringify(x));
""";

SELECT firewall.name, firewall.resource.parent, JSON_EXTRACT(firewall.resource.data, '$.sourceRanges') AS sourceRanges
FROM `PROJECT_ID.DATASET_ID.TABLE_NAME` AS firewall
JOIN UNNEST(json2array(JSON_EXTRACT(firewall.resource.data, '$.sourceRanges'))) AS source_ranges
WHERE asset_type="compute.googleapis.com/Firewall" AND JSON_EXTRACT(firewall.resource.data, '$.sourceRanges') IS NOT NULL AND JSON_EXTRACT_SCALAR(source_ranges, '$') = "0.0.0.0/0"

Mediante la separación de tablas por tipo de recurso, podemos encontrar reglas de firewall abiertas con una consulta más fácil y rápida.

SELECT firewall.name, firewall.resource.parent, sourceRanges
FROM `PROJECT_ID.DATASET_ID.STRUCTURED_INSTANCE_TABLE_NAME` AS firewall
JOIN UNNEST(firewall.resource.data.sourceRanges) AS sourceRanges
WHERE sourceRanges = "0.0.0.0/0";

Consulta en los tipos de recursos unidos

Para unir tablas de diferentes tipos de recursos, ejecuta la siguiente consulta: En el siguiente ejemplo, se muestra cómo encontrar todas las subredes que no tienen una VM conectada. Primero, la consulta encuentra todas las subredes. Luego, en esa lista, selecciona las subredes cuyo selfLinks no está presente.

CREATE TEMP FUNCTION json2array(json STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
  return JSON.parse(json).map(x=>JSON.stringify(x));
""";

SELECT name, JSON_EXTRACT(subnetwork.resource.data, '$.selfLink') AS selflink
FROM `PROJECT_ID.DATASET_ID.TABLE_NAME` AS subnetwork
WHERE asset_type = "compute.googleapis.com/Subnetwork" AND (JSON_EXTRACT(subnetwork.resource.data, '$.selfLink') NOT IN
(SELECT DISTINCT JSON_EXTRACT(network_interfaces, '$.subnetwork')
FROM `PROJECT_ID.DATASET_ID.TABLE_NAME` as instance
JOIN UNNEST(json2array(JSON_EXTRACT(instance.resource.data, '$.networkInterfaces'))) AS network_interfaces
WHERE asset_type ="compute.googleapis.com/Instance"
AND JSON_EXTRACT(instance.resource.data, '$.networkInterfaces') IS NOT NULL
)) IS NULL

Mediante la separación de tablas por tipo de recurso, podemos encontrar todas las subredes que no tienen una VM adjunta con una consulta más fácil y rápida.

SELECT name, subnetwork.resource.data.selfLink
FROM `PROJECT_ID.DATASET_ID.STRUCTURED_SUBNETWORK_TABLE_NAME` AS subnetwork
WHERE
  (
    subnetwork.resource.data.selfLink
      NOT IN (
        SELECT DISTINCT networkInterface.subnetwork
        FROM `PROJECT_ID.DATASET_ID.STRUCTURED_INSTANCE_TABLE_NAME` as instance
        JOIN
          UNNEST(instance.resource.data.networkInterfaces) AS networkInterface
        WHERE
          networkInterface IS NOT NULL
      )
  ) IS NULL;

Realiza consultas para encontrar clústeres vulnerables de Dataproc debido a CVE-2021-44228.

CREATE TEMP FUNCTION vulnerable_version(imageVersion STRING)
RETURNS BOOL
LANGUAGE js AS r"""
    const version_regexp = /(?<major>\d+)(?:\.)(?<minor>\d+)(?:\.)?(?<sub>\d+)?/g;
    let match = version_regexp.exec(imageVersion);
    if(match.groups.major < 1){
        return true;
    }
    if (match.groups.major == 1){
        if (match.groups.minor < 3){
            return true;
        }
        if(match.groups.minor == 3 &amp;&amp; match.groups.sub < 95){
            return true;
        }
        if(match.groups.minor == 4 &amp;&amp; match.groups.sub < 77){
            return true;
        }
        if(match.groups.minor == 5 &amp;&amp; match.groups.sub < 53){
            return true;
        }
    }
    if (match.groups.major == 2 &amp;&amp; match.groups.minor == 0 &amp;&amp; match.groups.sub < 27){
        return true;
    }
    return false;
""";

SELECT
  c.name,
  c.resource.data.projectId AS project_id,
  c.resource.data.clusterName AS cluster_name,
  c.resource.data.config.softwareConfig.imageVersion AS image_version,
  c.resource.data.status.state AS cluster_state,
  vulnerable_version(c.resource.data.config.softwareConfig.imageVersion) AS is_vulnerable
FROM
  `PROJECT_ID.DATASET_ID.TABLE_NAME_PREFIX_dataproc_googleapis_com_Cluster` c
  INNER JOIN `PROJECT_ID.DATASET_ID.TABLE_NAME_PREFIX_cloudresourcemanager_googleapis_com_Project` p
    ON p.resource.data.projectId = c.resource.data.projectId
WHERE
  c.resource.data.config.softwareConfig.imageVersion IS NOT NULL
  AND c.resource.data.status.state = "RUNNING"
  AND p.resource.data.lifecycleState = "ACTIVE";