BigQuery 샘플 쿼리로 내보내기

이 페이지는 BigQuery로 내보낸 후 애셋 데이터를 분석하는 SQL 쿼리 예시 목록을 보여줍니다. 자세한 내용은 표준 SQL 쿼리 구문을 참조하세요.

사용 가능한 열에서 직접 쿼리

각 애셋 유형의 수량을 찾으려면 다음 쿼리를 실행합니다.

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

반복 필드 쿼리

구성원으로 Gmail 계정이 포함된 ID 및 액세스 관리(IAM) 정책을 찾으려면 다음 쿼리를 실행합니다. BigQuery는 UNNEST를 사용하여 반복 필드를 직접 쿼리할 수 있는 테이블로 평면화합니다.

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 members
WHERE members like "%@gmail.com"

공개 IP를 사용하여 생성할 수 있는 조직, 폴더, 프로젝트를 찾으려면 다음 쿼리를 실행합니다. 이 쿼리는 SSL 또는 프록시가 구성되어 있지 않다면 Cloud SQL 인스턴스가 있는 공개 IP를 허용하는 것이 취약점을 유발할 수 있기 때문에 유용합니다.

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

프로젝트의 동일한 VPC 서비스 제어 서비스 경계 내에서 조직, 폴더 또는 프로젝트를 찾으려면 다음 쿼리를 실행합니다.

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

추출된 JSON 문자열 쿼리

공개 방화벽 규칙을 찾으려면 다음 쿼리를 실행합니다. BigQuery에서 사용되는 JSON 함수에 대해 자세히 알아보기

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"

리소스 유형별로 테이블을 분리하면 더 쉽고 빠른 쿼리로 방화벽 규칙 열기를 찾을 수 있습니다.

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

조인된 리소스 유형 쿼리

여러 리소스 유형의 테이블을 조인하려면 다음 쿼리를 실행합니다. 다음 예시에서는 VM이 연결되지 않은 모든 서브네트워크를 찾는 방법을 보여줍니다. 먼저 쿼리가 모든 서브네트워크를 찾습니다. 그런 다음 이 목록에서 selfLinks가 없는 서브네트워크를 선택합니다.

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

리소스 유형별로 테이블을 분리하면 더 빠르고 쉬운 쿼리로 연결된 VM이 없는 모든 서브네트워크를 찾을 수 있습니다.

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;