Exemples de requêtes SQL BigQuery

Cette page répertorie des exemples de requêtes SQL permettant d'analyser les données de vos éléments après les avoir exportées vers BigQuery. Pour en savoir plus, consultez la page Syntaxe des requêtes en SQL standard.

Remarque :Au départ, certains champs expérimentaux peuvent apparaître vides dans BigQuery. Ne définissez pas de dépendance sur les colonnes concernées tant que les champs ne sont pas renseignés. Pour savoir quels champs sont publics, consultez le document de découverte de la ressource.

Requête directement sur les colonnes disponibles

Pour connaître la quantité de chaque type d'élément, exécutez la requête suivante :

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

Requête sur des champs répétés

Pour trouver les stratégies IAM (Identity and Access Management) qui accordent l'accès aux comptes Gmail, exécutez la requête suivante. BigQuery utilise UNNEST pour aplatir les champs répétés dans une table que vous pouvez interroger directement :

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]"

Pour trouver une organisation, un dossier ou un projet permettant la création à l'aide d'une adresse IP publique, exécutez la requête suivante. Cette requête est utile, car autoriser des adresses IP publiques avec des instances Cloud SQL peut entraîner des failles, sauf si SSL ou un proxy est configuré:

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);

Pour rechercher une organisation, un dossier ou un projet dans le même périmètre de service VPC Service Controls d'un projet, exécutez la requête suivante:

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";

Requête sur une chaîne JSON extraite

Pour trouver les règles de pare-feu ouvertes, exécutez la requête suivante. Obtenez davantage d'informations sur les fonctions JSON utilisées dans 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"

En séparant les tables par type de ressource, nous pouvons trouver les règles de pare-feu ouvertes à l'aide d'une requête plus simple et plus rapide.

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";

Requête sur les types de ressources associées

Pour joindre des tables de différents types de ressources, exécutez la requête suivante. L'exemple suivant montre comment rechercher tous les sous-réseaux qui ne sont associés à aucune VM. La requête trouve tout d'abord tous les sous-réseaux. Ensuite, elle sélectionne dans cette liste les sous-réseaux pour lesquels selfLinks n'est pas présent.

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

En séparant les tables par type de ressource, nous pouvons trouver tous les sous-réseaux n'ayant pas de VM associée, grâce à une requête plus simple et plus rapide.

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;

Requête pour détecter les clusters Dataproc vulnérables à cause de 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";