Consultas SQL de amostra do BigQuery

Nesta página, listamos exemplos de consultas SQL para analisar os dados dos seus recursos depois de exportá-los para o BigQuery. Consulte Sintaxe de consulta do SQL padrão para mais informações.

Observação :alguns campos experimentais podem parecer vazios no BigQuery. Não defina uma dependência nas colunas afetadas até que os campos sejam preenchidos. Para ver quais campos são públicos, consulte o documento de descoberta do recurso.

Consultar diretamente em colunas disponíveis

Para encontrar a quantidade de cada tipo de recurso, execute a seguinte 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 em campos repetidos

Para encontrar as políticas do Identity and Access Management (IAM) que concedem acesso a contas do Gmail, execute a seguinte consulta. O BigQuery usa UNNEST para nivelar campos repetidos em uma tabela que pode ser consultada diretamente:

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 encontrar uma organização, pasta ou projeto que permita a criação usando um IP público, execute a consulta a seguir. Essa consulta é útil porque permitir endereços IP públicos com instâncias do Cloud SQL pode introduzir vulnerabilidades, a menos que o SSL ou um proxy estejam configurado:

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 uma organização, pasta ou projeto no mesmo perímetro de serviço do VPC Service Controls de um projeto, execute a seguinte 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";

Consultar a string JSON extraída

Para encontrar regras de firewall abertas, execute a seguinte consulta. Saiba mais sobre as funções JSON usadas no 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"

Ao separar tabelas por tipo de recurso, podemos encontrar regras de firewall abertas com uma consulta mais fácil e 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";

Consultar tipos de recurso unidos

Para mesclar tabelas de diferentes tipos de recursos, execute a seguinte consulta. O exemplo a seguir mostra como encontrar todas as sub-redes que não têm VM anexada. Primeiro, a consulta encontra todas as sub-redes. Em seguida, ela seleciona as sub-redes em que selfLinks não 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

Ao separar as tabelas por tipo de recurso, é possível encontrar todas as sub-redes que não têm VMs anexadas com uma consulta mais fácil e 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;

Faça uma consulta para encontrar clusters vulneráveis do Dataproc devido 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";