BigQuery-Beispiel-SQL-Abfragen

Auf dieser Seite sind Beispiel-SQL-Abfragen aufgeführt, mit denen Sie die Daten Ihrer Assets nach dem Export nach BigQuery analysieren können. Weitere Informationen finden Sie in der SQL-Standard-SQL-Abfragesyntax.

Hinweis: Einige experimentelle Felder können in BigQuery auf den ersten Blick leer sein. Legen Sie erst dann eine Abhängigkeit von den betroffenen Spalten fest, wenn die Felder ausgefüllt sind. Im Discovery-Dokument der Ressource können Sie sehen, welche Felder öffentlich sind.

Verfügbare Spalten direkt abfragen

Führen Sie die folgende Abfrage aus, um die Mengen der einzelnen Asset-Typen 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

Wiederkehrende Felder abfragen

Führen Sie die folgende Abfrage aus, um IAM-Richtlinien (Identity and Access Management) zu suchen, die Zugriff auf Gmail-Konten gewähren. BigQuery verwendet UNNEST, um wiederkehrende Felder in einer Tabelle zu vereinfachen, die direkt abgefragt werden 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 "[e-mail]"

Führen Sie die folgende Abfrage aus, um eine Organisation, einen Ordner oder ein Projekt zu finden, die bzw. das das Erstellen über eine öffentliche IP-Adresse zulässt. Diese Abfrage ist nützlich, da das Zulassen öffentlicher IP-Adressen mit Cloud SQL-Instanzen zu Sicherheitslücken führen kann, wenn SSL oder ein Proxy nicht konfiguriert ist:

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 Dienstperimeter von VPC Service Controls 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_ID";

Extrahierten JSON-String abfragen

Führen Sie die folgende Abfrage aus, um offene Firewallregeln zu suchen: Weitere Informationen zu JSON-Funktionen in 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"

Durch das Verteilen von Tabellen pro Ressourcentyp sind offene Firewallregeln mit einer einfacheren und schnelleren Abfrage verfügbar.

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

Verknüpfte Ressourcentypen abfragen

Führen Sie die folgende Abfrage aus, um Tabellen verschiedener Ressourcentypen zu verknüpfen. Das folgende Beispiel zeigt, wie Sie alle Subnetzwerke finden, denen keine VM angehängt ist. Zuerst sucht die Abfrage alle Subnetzwerke. In dieser Liste werden dann die Subnetzwerke ausgewählt, deren selfLinks nicht vorhanden sind.

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

Durch die Trennung von Tabellen nach Ressourcentyp können Sie mit einer einfacheren und schnelleren Abfrage alle Subnetzwerke finden, an die keine VM angehängt ist.

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