Auf dieser Seite sind Beispiel-SQL-Abfragen aufgeführt, um Ihre Asset-Daten zu analysieren, nachdem Sie sie in BigQuery exportiert haben. Weitere Informationen finden Sie unter Standard-SQL-Abfragesyntax.
Direkt zu den verfügbaren Spalten abfragen
Führen Sie die folgende Abfrage aus, um die Menge jedes Asset-Typs zu ermitteln:
SELECT asset_type, COUNT(*) AS asset_count FROM `PROJECT_ID.DATASET_ID.TABLE_NAME` GROUP BY asset_type ORDER BY asset_count DESC
Abfrage für wiederkehrende Felder
Führen Sie die folgende Abfrage aus, um IAM-Richtlinien (Identity and Access Management) zu finden, die Zugriff auf Gmail-Konten gewähren. BigQuery verwendet UNNEST
, um wiederkehrende Felder in einer Tabelle zu vereinfachen, die Sie direkt abfragen können:
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"
Führen Sie die folgende Abfrage aus, um eine Organisation, einen Ordner oder ein Projekt zu finden, das das Erstellen mit einer öffentlichen IP-Adresse ermöglicht. Diese Abfrage ist nützlich, da das Zulassen öffentlicher IP-Adressen mit Cloud SQL-Instanzen Sicherheitslücken aufweisen kann, es sei denn, SSL oder ein Proxy ist konfiguriert:
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);
Führen Sie die folgende Abfrage aus, um eine Organisation, einen Ordner oder ein Projekt im selben VPC Service Controls-Dienstperimeter eines Projekts zu finden:
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";
Abfrage für extrahierten JSON-String
Führen Sie die folgende Abfrage aus, um offene Firewallregeln zu finden. Mehr über in BigQuery verwendete JSON-Funktionen erfahren
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"
Durch Trennen von Tabellen nach Ressourcentyp können wir offene Firewallregeln mit einer einfacheren und schnelleren Abfrage finden.
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";
Abfrage verbundener Ressourcentypen
Führen Sie die folgende Abfrage aus, um Tabellen verschiedener Ressourcentypen zu verknüpfen. Im folgenden Beispiel wird gezeigt, wie Sie alle Subnetzwerke finden, denen keine VM angehängt ist.
Zuerst sucht die Abfrage nach allen Subnetzwerken. Anschließend werden in dieser Liste die Subnetzwerke ausgewählt, deren selfLinks
nicht vorhanden ist.
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
Durch die Trennung von Tabellen nach Ressourcentyp finden wir alle Subnetzwerke, denen keine VM zugeordnet ist, mit einer einfacheren und schnelleren Abfrage.
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;
Abfrage zum Suchen anfälliger Dataproc-Cluster aufgrund von 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";