Consultas en SQL de muestra

Este documento contiene consultas de muestra sobre entradas de registro que se almacenan en buckets de registros que se actualizan para usar el Análisis de registros. En estos buckets, puedes ejecutar consultas en SQL desde la página Análisis de registros en la consola de Google Cloud. Para obtener más muestras, consulta los repositorios de GitHub logging-analytics-samples y security-analytics.

En este documento no se describe SQL ni cómo enrutar y almacenar entradas de registro. Para obtener información sobre esos temas, consulta la sección ¿Qué sigue?

Sugerencia: No incluyas un campo timestamp en la cláusula WHERE de tu consulta. En su lugar, establece el intervalo de tiempo con el selector de intervalos de tiempo.

Antes de comenzar

• Para usar las consultas que se muestran en este documento en la página Análisis de registros, reemplaza TABLE por el nombre de la tabla que corresponde a la vista que quieres consultar. El nombre de la tabla tiene el formato project_ID.region.bucket_ID.view_ID. Puedes encontrar el nombre de la tabla de una vista en la página Análisis de registros; la consulta predeterminada para una vista de registro enumera el nombre de la tabla en la instrucción FROM. Para obtener información sobre cómo acceder a la consulta predeterminada, lee Cómo consultar una vista de registro.

• Para usar las consultas que se muestran en este documento en la página de BigQuery Studio, reemplaza TABLE por la ruta a la tabla en el conjunto de datos vinculado. Por ejemplo, para consultar la vista _AllLogs en el conjunto de datos vinculado mydataset que está en el proyecto myproject, establece este campo en myproject.mydataset._AllLogs:

  En el panel de navegación de la consola de Google Cloud, selecciona BigQuery:

  Ir a BigQuery

• Para abrir la página Análisis de registros, haz lo siguiente:

  1. En el panel de navegación de la consola de Google Cloud, selecciona Logging y, luego, Análisis de registros:

     Ir a Análisis de registros

  2. Opcional: Para identificar el esquema de tabla de la vista de registro, en la lista Vistas de registro, busca la vista y, luego, selecciona su nombre.

  Se mostrará el esquema de la tabla. Puedes usar el campo Filtrar para ubicar campos específicos. No puedes modificar el esquema.

Filtrar registros

Las consultas de SQL determinan qué filas de la tabla procesar y, luego, agrupan las filas y realizan operaciones de agregación. Cuando no se enumera ninguna operación de agrupación y agregación, el resultado de la consulta incluye las filas que seleccionó la operación de filtro. En los ejemplos de esta sección, se ilustra el filtrado.

Filtrar por hora

Para establecer el intervalo de tiempo de tu consulta, te recomendamos que uses el selector de intervalo de tiempo. Este selector se usa de forma automática cuando una consulta no especifica un campo timestamp en la cláusula WHERE. Por ejemplo, para ver los datos de la última semana, selecciona Últimos 7 días en el selector de intervalo de tiempo. También puedes usar el selector de intervalo de tiempo para especificar una hora de inicio y finalización, especificar una hora de visualización y cambiar las zonas horarias.

Si incluyes un campo timestamp en la cláusula WHERE, no se usa la configuración del selector de intervalo de tiempo. En el siguiente ejemplo, se filtran los datos con la función TIMESTAMP_SUB, que te permite especificar un intervalo de retrospectiva a partir de la hora actual:

WHERE
  timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)

Si quieres obtener más información para filtrar por tiempo, consulta Funciones de tiempo y Funciones de marca de tiempo.

Filtrar por recurso

Para filtrar por recurso, agrega una restricción resource.type.

Por ejemplo, la siguiente consulta lee la hora de datos más reciente, retiene las filas cuyo tipo de recurso coincide con gce_instance y, luego, ordena y muestra hasta 100 entradas:

SELECT
  timestamp, log_name, severity, json_payload, resource, labels
FROM
  `TABLE`
WHERE
  resource.type = "gce_instance"
ORDER BY timestamp ASC
LIMIT 100

Filtrar por gravedad

Puedes filtrar según una gravedad específica con una restricción como severity = 'ERROR'. Otra opción es usar la declaración IN y especificar un conjunto de valores válidos.

Por ejemplo, la siguiente consulta lee la hora de datos más reciente y, luego, retiene solo las filas que contienen un campo severity cuyo valor es 'INFO' o 'ERROR':

SELECT
  timestamp, log_name, severity, json_payload, resource, labels
FROM
  `TABLE`
WHERE
  severity IS NOT NULL AND
  severity IN ('INFO', 'ERROR')
ORDER BY timestamp ASC
LIMIT 100

La consulta anterior filtra por el valor del campo severity. Sin embargo, también puedes escribir consultas que filtren por el valor numérico de la gravedad del registro. Por ejemplo, si reemplazas las líneas severity por las siguientes líneas, la consulta mostrará todas las entradas de registro cuyo nivel de gravedad sea al menos NOTICE:

  severity_number IS NOT NULL AND
  severity_number > 200

Para obtener información sobre los valores enumerados, consulta LogSeverity.

Filtrar por nombre de registro

Para filtrar por un nombre de registro, puedes agregar una restricción en el valor de los campos log_name o log_id. El campo log_name incluye la ruta del recurso. Es decir, este campo tiene valores como projects/myproject/logs/mylog. El campo log_id solo almacena el nombre del registro, como mylog.

Por ejemplo, la siguiente consulta lee la hora de datos más reciente, retiene las filas en las que el valor del campo log_id es cloudaudit.googleapis.com/data_access y, luego, ordena y muestra los resultados:

SELECT
  timestamp, log_id, severity, json_payload, resource, labels
FROM
  `TABLE`
WHERE
  log_id = "cloudaudit.googleapis.com/data_access"
ORDER BY timestamp ASC
LIMIT 100

Filtrar por etiqueta de recurso

La mayoría de los descriptores de recurso supervisado definen las etiquetas que se usan para identificar el recurso específico. Por ejemplo, el descriptor de una instancia de Compute Engine incluye etiquetas para la zona, el ID del proyecto y el ID de la instancia. Cuando se escribe la entrada de registro, se asignan valores a cada campo. A continuación, se muestra un ejemplo de este tipo:

{
   type: "gce_instance"
   labels: {
      instance_id: "1234512345123451"
      project_id: "my-project"
      zone: "us-central1-f"
   }
}

Debido a que el tipo de datos del campo labels es JSON, incluir una restricción como resource.labels.zone = "us-centra1-f" en una consulta genera un error de sintaxis. Para obtener el valor de un campo con un tipo de datos de JSON, usa la función JSON_VALUE.

Por ejemplo, la siguiente consulta lee los datos más recientes y, luego, retiene las filas en las que el recurso es una instancia de Compute Engine ubicada en la zona us-central1-f:

SELECT
  timestamp, log_name, severity, JSON_VALUE(resource.labels.zone) AS zone, json_payload, resource, labels
FROM
  `TABLE`
WHERE
  resource.type = "gce_instance" AND
  JSON_VALUE(resource.labels.zone) = "us-central1-f"
ORDER BY timestamp ASC
LIMIT 100

Para obtener información sobre todas las funciones que pueden recuperar y transformar datos JSON, consulta Funciones de JSON.

Filtrar por solicitud HTTP

Si deseas filtrar la tabla para que solo incluya las filas que correspondan a una solicitud o respuesta HTTP, agrega una restricción http_request IS NOT NULL:

SELECT
  timestamp, log_name, severity, http_request, resource, labels
FROM
  `TABLE`
WHERE
  http_request IS NOT NULL
ORDER BY timestamp
LIMIT 100

La siguiente consulta solo incluye filas que corresponden a solicitudes GET o POST:

SELECT
  timestamp, log_name, severity, http_request, resource, labels
FROM
  `TABLE`
WHERE
  http_request IS NOT NULL AND
  http_request.request_method IN ('GET', 'POST')
ORDER BY timestamp ASC
LIMIT 100

Filtrar por estado HTTP

Para filtrar por estado HTTP, modifica la cláusula WHERE de modo que se requiera que se defina el campo http_request.status:

SELECT
  timestamp, log_name, http_request.status, http_request, resource, labels
FROM
  `TABLE`
WHERE
  http_request IS NOT NULL AND
  http_request.status IS NOT NULL
ORDER BY timestamp ASC
LIMIT 100

Para determinar el tipo de datos almacenados en un campo, visualiza el esquema o muestra el campo. Los resultados de la consulta anterior muestran que el campo http_request.status almacena valores enteros.

Filtra por un campo con un tipo JSON

Para extraer un valor de una columna cuyo tipo de datos es JSON, usa la función JSON_VALUE.

Considera las siguientes consultas:

SELECT
  json_payload
FROM
  `TABLE`
WHERE
  json_payload.status IS NOT NULL

y

SELECT
  json_payload
FROM
  `TABLE`
WHERE
  JSON_VALUE(json_payload.status) IS NOT NULL

Con las consultas anteriores, se prueba el valor de la columna json_payload. El contenido de esta columna está determinado por el contenido de una entrada de registro. Ambas consultas descartarán las filas que no contengan una columna etiquetada json_payload. La diferencia entre estas dos consultas es la línea final, que define qué se prueba con NULL. Ahora, considera una tabla que tiene dos filas. En una fila, la columna json_payload tiene el siguiente formato:

{
    status: {
        measureTime: "1661517845"
    }
}

En la otra fila, la columna json_payload tiene una estructura diferente:

{
    @type: "type.googleapis.com/google.cloud.scheduler.logging.AttemptFinished"
    jobName: "projects/my-project/locations/us-central1/jobs/test1"
    relativeUrl: "/food=cake"
    status: "NOT_FOUND"
    targetType: "APP_ENGINE_HTTP"
}

Las dos filas anteriores cumplen con la restricción json_payload.status IS NOT NULL. Es decir, el resultado de la consulta incluye ambas filas. Sin embargo, cuando la restricción es JSON_VALUE(json_payload.status) IS NOT NULL, solo se incluye la segunda fila en el resultado.

Filtrar por expresión regular

Para que se muestre la substring que coincide con una expresión regular, usa la función REGEXP_EXTRACT. El tipo de datos que se muestra de esta función es STRING o BYTES.

En la siguiente consulta, se muestran las entradas de registro más recientes recibidas, se retienen esas entradas con un campo json_payload.jobName y, luego, se muestra la parte del nombre que comienza con test:

SELECT
  timestamp, REGEXP_EXTRACT(JSON_VALUE(json_payload.jobName), r".*(test.*)$") AS name,
FROM
  `TABLE`
WHERE
  json_payload.jobName IS NOT NULL
ORDER BY timestamp DESC
LIMIT 20

Para obtener más ejemplos, consulta la documentación de REGEXP_EXTRACT. Para ver ejemplos de otras expresiones regulares que puedes usar, consulta Funciones, operadores y condicionales.

La consulta que se muestra en este ejemplo no es eficiente. Para una coincidencia de subcadena, como la que se ilustra, usa la función CONTAINS_SUBSTR.

Agrupa y agrega entradas de registro

En esta sección, se basa en los ejemplos anteriores y se ilustra cómo puedes agrupar y agregar filas de tablas. Si no especificas una agrupación, pero sí una agregación, se muestra un solo resultado porque SQL trata todas las filas que satisfacen la cláusula WHERE como un solo grupo.

Cada expresión SELECT debe incluirse o agregarse en los campos del grupo.

Agrupar por hora

Para agrupar los datos por tiempo, usa la función TIMESTAMP_TRUNC, que trunca una marca de tiempo hasta un nivel de detalle especificado, como MINUTE. Por ejemplo, una marca de tiempo de 15:30:11, que tiene el formato hours:minutes:seconds, se convierte en 15:30:00 cuando el nivel de detalle se establece en MINUTE.

Nota: Si especificas una marca de tiempo en tu consulta, esa marca de tiempo anulará el intervalo de tiempo seleccionado en el selector de intervalo de tiempo. Para usar el selector de intervalo de tiempo, quita las expresiones de marca de tiempo de tu consulta.

La siguiente consulta lee los datos recibidos en el intervalo que especifica el selector de intervalo de tiempo y, luego, retiene las filas en las que el valor del campo json_payload.status no es NULL. La consulta trunca la marca de tiempo en cada fila por hora y, luego, agrupa las filas según la marca de tiempo y el estado truncados:

SELECT
  TIMESTAMP_TRUNC(timestamp, HOUR) AS hour,
  JSON_VALUE(json_payload.status) AS status,
  COUNT(*) AS count
FROM
  `TABLE`
WHERE
  json_payload IS NOT NULL AND
  JSON_VALUE(json_payload.status) IS NOT NULL
GROUP BY hour,status
ORDER BY hour ASC

Para obtener muestras adicionales, consulta la documentación de TIMESTAMP_TRUNC. Para obtener información sobre otras funciones basadas en el tiempo, consulta Funciones de fecha y hora.

Agrupar por recurso

La siguiente consulta lee la hora de datos más reciente y, luego, agrupa las filas por tipo de recurso. Luego cuenta el número de filas de cada tipo y muestra una tabla con dos columnas. La primera columna muestra el tipo de recurso, mientras que la segunda columna es el número de filas para ese tipo de recurso:

SELECT
   resource.type, COUNT(*) AS count
FROM
  `TABLE`
GROUP BY resource.type
LIMIT 100

Agrupar por gravedad

La siguiente consulta lee la hora de datos más reciente y, luego, conserva las filas que tienen un campo de gravedad. Luego, la consulta agrupa las filas por gravedad y cuenta la cantidad de filas de cada grupo:

SELECT
  severity, COUNT(*) AS count
FROM
  `TABLE`
WHERE
  severity IS NOT NULL
GROUP BY severity
ORDER BY severity
LIMIT 100

Agrupar por log_id

El resultado de la siguiente consulta es una tabla con dos columnas. La primera columna enumera los nombres de registro, mientras que la segunda columna enumera la cantidad de entradas de registro que se escribieron en ese registro en la hora más reciente. La consulta ordena los resultados por el recuento de entradas:

SELECT
  log_id, COUNT(*) AS count
FROM
  `TABLE`
GROUP BY log_id
ORDER BY count DESC
LIMIT 100

Calcula la latencia promedio de una solicitud HTTP

En la siguiente consulta, se ilustra la agrupación por varias columnas y el cálculo de un valor promedio. La consulta agrupa las filas según la URL que contiene la solicitud HTTP y el valor del campo labels.checker_location. Después de agrupar las filas, la consulta calcula la latencia promedio de cada grupo:

SELECT
  JSON_VALUE(labels.checker_location) AS location,
  AVG(http_request.latency.seconds) AS secs, http_request.request_url
FROM
  `TABLE`
WHERE
  http_request IS NOT NULL AND
  http_request.request_method IN ('GET')
GROUP BY http_request.request_url, location
ORDER BY location
LIMIT 100

En la expresión anterior, se requiere JSON_VALUE para extraer el valor del campo labels.checker_location porque el tipo de datos para labels es JSON. Sin embargo, no usas esta función para extraer el valor del campo http_request.latency.seconds. El último campo tiene un tipo de datos de número entero.

Calcula el promedio de bytes enviados para una prueba de subred

En la siguiente consulta, se ilustra cómo mostrar la cantidad promedio de bytes enviados por ubicación.

La consulta lee la hora de datos más reciente y, luego, conserva solo las filas cuya columna de tipo de recurso es gce_subnetwork y cuya columna json_payload no es NULL. Luego, la consulta agrupa las filas según la ubicación del recurso. A diferencia del ejemplo anterior, en el que los datos se almacenan como un valor numérico, el valor del campo bytes_sent es una string y, por lo tanto, debes convertir el valor en un FLOAT64 antes de calcular el promedio:

SELECT JSON_VALUE(resource.labels.location) AS location,
   AVG(CAST(JSON_VALUE(json_payload.bytes_sent) AS FLOAT64)) AS bytes
FROM
  `TABLE`
WHERE
  resource.type = "gce_subnetwork" AND
  json_payload IS NOT NULL
GROUP BY location
LIMIT 100

El resultado de la consulta anterior es una tabla en la que cada fila enumera una ubicación y el promedio de bytes enviados para esa ubicación.

Para obtener información sobre todas las funciones que pueden recuperar y transformar datos JSON, consulta Funciones de JSON.

Para obtener información sobre CAST y otras funciones de conversión, consulta Funciones de conversión.

Cuenta las entradas de registro con un campo que coincide con un patrón

Para que se muestre la substring que coincide con una expresión regular, usa la función REGEXP_EXTRACT. El tipo de datos que se muestra de esta función es STRING o BYTES.

La siguiente consulta retiene las entradas de registro para las que el valor del campo json_payload.jobName no es NULL. Luego, agrupa las entradas por el sufijo de nombre que comienza con test. Por último, la consulta cuenta el número de entradas en cada grupo:

SELECT
  REGEXP_EXTRACT(JSON_VALUE(json_payload.jobName), r".*(test.*)$") AS name,
  COUNT(*) AS count
FROM
  `TABLE`
WHERE
  json_payload.jobName IS NOT NULL
GROUP BY name
ORDER BY count
LIMIT 20

Para obtener más ejemplos, consulta la documentación de REGEXP_EXTRACT. Para ver ejemplos de otras expresiones regulares que puedes usar, consulta Funciones, operadores y condicionales.

Búsqueda en varias columnas

En esta sección, se describen dos enfoques diferentes que puedes usar para buscar varias columnas de una tabla.

Búsqueda basada en tokens

Para buscar en una tabla las entradas que coinciden con un conjunto de términos de búsqueda, usa la función SEARCH. Esta función requiere dos parámetros: dónde buscar y búsqueda. Debido a que la función SEARCH tiene reglas específicas sobre cómo se buscan los datos, te recomendamos que leas la documentación de SEARCH.

La siguiente consulta retiene solo las filas que tienen un campo que coincide de manera exacta con “35.193.12.15”:

SELECT
  timestamp, log_id, proto_payload, severity, resource.type, resource, labels
FROM
  `TABLE` AS t
WHERE
  proto_payload IS NOT NULL AND
  log_id = "cloudaudit.googleapis.com/data_access" AND
  SEARCH(t,"`35.193.12.15`")
ORDER BY timestamp ASC
LIMIT 20

En la consulta anterior, las comillas simples se ajustan al valor que se buscará. Esto garantiza que la función SEARCH busque una coincidencia exacta entre el valor de un campo y el valor entre los acentos graves.

Cuando se omiten las comillas simples en la cadena de consulta, esta se divide en función de las reglas definidas en la documentación de SEARCH. Por ejemplo, cuando se ejecuta la siguiente instrucción, la cadena de consulta se divide en cuatro tokens: "35", "193", "12" y "15":

  SEARCH(t,"35.193.12.15")

La declaración SEARCH anterior coincide con una fila cuando un solo campo coincide con los cuatro tokens. El orden de los tokens no importa.

Puedes incluir varias sentencias SEARCH en una consulta. Por ejemplo, en la consulta anterior, podrías reemplazar el filtro en el ID del registro con una declaración como la siguiente:

  SEARCH(t,"`cloudaudit.googleapis.com/data_access`")

Con la instrucción anterior, se busca en toda la tabla, mientras que con la instrucción original, solo se busca en la columna log_id.

Para realizar varias búsquedas en una columna, separa las cadenas individuales con un espacio. Por ejemplo, la siguiente declaración coincide con las filas en las que un campo contiene “Hello World”, “happy” y “days”:

  SEARCH(t,"`Hello World` happy days")

Por último, puedes buscar columnas específicas de una tabla en lugar de buscar en una tabla completa. Por ejemplo, la siguiente declaración solo busca en las columnas llamadas text_payload y json_payload:

   SEARCH((text_payload, json_payload) ,"`35.222.132.245`")

Para obtener información sobre cómo se procesan los parámetros de la función SEARCH, consulta la página de referencia de BigQuery Funciones de búsqueda.

Búsqueda de subcadenas

Para realizar una prueba que no distingue entre mayúsculas y minúsculas a fin de determinar si un valor existe en una expresión, usa la función CONTAINS_SUBSTR. Esta función muestra TRUE cuando el valor existe y FALSE en caso contrario. El valor de búsqueda debe ser un literal STRING, pero no el NULL literal.

Por ejemplo, la siguiente consulta recupera todas las entradas de registro de auditoría de acceso a los datos con una dirección IP específica cuyas marcas de tiempo se encuentran en un intervalo de tiempo específico. Por último, la consulta ordena los resultados y, luego, muestra los 20 resultados más antiguos:

SELECT
  timestamp, log_id, proto_payload, severity, resource.type, resource, labels
FROM
  `TABLE` AS t
WHERE
  proto_payload IS NOT NULL AND
  log_id = "cloudaudit.googleapis.com/data_access" AND
  CONTAINS_SUBSTR(t,"35.193.12.15")
ORDER BY timestamp ASC
LIMIT 20

La consulta anterior realiza una prueba de subcadena. Por lo tanto, una fila que contiene "35.193.12.152" coincide con la declaración CONTAINS_SUBSTR.

Combinar datos de múltiples fuentes

Las instrucciones de consulta analizan una o más tablas o expresiones y muestran las filas de resultados calculados. Por ejemplo, puedes usar declaraciones de consulta para combinar los resultados de las declaraciones SELECT en diferentes tablas o conjuntos de datos de varias maneras y, luego, seleccionar las columnas de los datos combinados.

Combinar datos de dos tablas con uniones

Para combinar la información de dos tablas, usa uno de los operadores de join. El tipo de unión y la cláusula condicional que usas determinan cómo se combinan y descartan las filas.

La siguiente consulta te proporciona los campos json_payload de las filas de dos tablas diferentes escritas por el mismo intervalo de seguimiento. La consulta realiza un JOIN interno sobre dos tablas para las filas en las que los valores de las columnas span_id y trace de ambas tablas coinciden. A partir de este resultado, la consulta selecciona los campos timestamp, severity y json_payload que provienen de TABLE_1, el campo json_payload de TABLE_2 y los valores de los campos span_id y trace en los que se unieron las dos tablas, y muestra hasta 100 filas:

SELECT
  a.timestamp, a.severity, a.json_payload, b.json_payload, a.span_id, a.trace
FROM `TABLE_1` a
JOIN `TABLE_2` b
ON
  a.span_id = b.span_id AND
  a.trace = b.trace
LIMIT 100

Combina varias selecciones con uniones

Para combinar los resultados de dos o más declaraciones SELECT y descartar las filas duplicadas, usa el operador UNION. Para retener las filas duplicadas, usa el operador UNION ALL.

La siguiente consulta lee la hora de datos más reciente de TABLE_1, combina el resultado con la hora de datos más reciente de TABLE_2, ordena los datos combinados aumentando la marca de tiempo y, luego, muestra las 100 entradas más antiguas:

SELECT
  timestamp, log_name, severity, json_payload, resource, labels
FROM(
  SELECT * FROM `TABLE_1`
  UNION ALL
  SELECT * FROM `TABLE_2`
)
ORDER BY timestamp ASC
LIMIT 100

¿Qué sigue?

Para obtener información sobre cómo enrutar y almacenar entradas de registro, consulta los siguientes documentos:

• Crea un bucket de registros
• Actualiza un bucket para usar el Análisis de registros
• Vincula un bucket de registros a un conjunto de datos de BigQuery
• Configurar y administrar receptores

Para obtener documentación de referencia de SQL, consulta los siguientes documentos:

• Funciones, operadores y condicionales
• Funciones de búsqueda
• Sintaxis de las consultas