Cette page répertorie des exemples de requêtes SQL permettant d'analyser vos données d'éléments après les avoir exportées dans BigQuery. Pour en savoir plus, consultez la page Syntaxe des requêtes en SQL standard.
Interroger les colonnes disponibles directement
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 rechercher 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 "%@gmail.com"
Pour trouver une organisation, un dossier ou un projet qui permet la création à l'aide d'une adresse IP publique, exécutez la requête suivante. Cette requête est utile, car le fait d'autoriser les adresses IP publiques avec les instances Cloud SQL peut introduire 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_NUMBER";
Requête sur la chaîne JSON extraite
Pour trouver les règles de pare-feu ouvertes, exécutez la requête suivante. Apprenez-en plus 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";
Interroger les types de ressources joints
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 n'ont pas de VM associée.
La requête recherche d'abord tous les sous-réseaux. Ensuite, dans cette liste, il sélectionne les sous-réseaux dont le sous-réseau selfLinks
n'est pas présent.
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 sans VM associés à une requête plus facile 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 rechercher des clusters Dataproc vulnérables en raison de la faille 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 && match.groups.sub < 95){ return true; } if(match.groups.minor == 4 && match.groups.sub < 77){ return true; } if(match.groups.minor == 5 && match.groups.sub < 53){ return true; } } if (match.groups.major == 2 && match.groups.minor == 0 && 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";