En esta página, se enumeran ejemplos de consultas de SQL para analizar los datos de tus elementos después de exportarlos a BigQuery. Consulta la sintaxis de consultas de SQL estándar para obtener más información.
Consulta directamente en columnas disponibles
Para encontrar la cantidad de cada tipo de elemento, ejecuta la siguiente consulta:
SELECT asset_type, COUNT(*) AS asset_count FROM `PROJECT_ID.DATASET_ID.TABLE_NAME` GROUP BY asset_type ORDER BY asset_count DESC
Consulta sobre campos repetidos
Para encontrar las políticas de administración de identidades y accesos (IAM) que otorgan acceso a las cuentas de Gmail, ejecuta la siguiente consulta. BigQuery usa UNNEST
para acoplar campos repetidos en una tabla que puedes consultar directamente:
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"
Para encontrar una organización, una carpeta o un proyecto que permita crear con una IP pública, ejecuta la siguiente consulta. Esta consulta es útil porque permitir IP públicas con instancias de Cloud SQL puede generar vulnerabilidades, a menos que se configure SSL o un proxy:
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);
Para encontrar una organización, una carpeta o un proyecto dentro del mismo perímetro de servicio de los Controles del servicio de VPC de un proyecto, ejecuta la siguiente consulta:
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";
Consulta sobre la string JSON extraída
Para encontrar reglas de firewall abiertas, ejecute la siguiente consulta. Obtén más información sobre funciones JSON usadas en 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"
Cuando separa las tablas por tipo de recurso, podemos encontrar reglas de firewall abiertas con una consulta más fácil y rápida.
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";
Consulta sobre tipos de recursos unidos
Para unir tablas de diferentes tipos de recursos, ejecuta la siguiente consulta. En el siguiente ejemplo, se muestra cómo encontrar todas las subredes que no tienen una VM conectada.
Primero, la consulta encuentra todas las subredes. Luego, en esa lista, se seleccionan esas subredes cuyo selfLinks
no está presente.
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
Cuando separa las tablas por tipo de recurso, podemos encontrar todas las subredes que no tienen una VM conectada con una consulta más fácil y rápida.
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;
Realiza una consulta para encontrar clústeres de Dataproc vulnerables debido a 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";