Nach BigQuery-Beispielabfragen exportieren

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