Se usó la API de Cloud Translation para traducir esta página.

Consultas en SQL de muestra

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

En este documento, no se describe SQL ni cómo enrutar y almacenar entradas de registro. Para para obtener información sobre esos temas, consulta la sección Próximos pasos.

Antes de comenzar

Para usar las consultas que se muestran en este documento en la página Log Analytics, reemplaza TABLE_NAME_OF_LOG_VIEW por el nombre de la tabla de la vista de registro que deseas consultar. Este nombre tiene el siguiente formato: project_ID.region.bucket_ID.view_ID

Para identificar este nombre, ve a la lista Vistas de registro en la en la página Análisis de registros, ubica la vista de registro y, luego, selecciona Consulta. El panel Consulta se propaga con una consulta predeterminada, incluye el nombre de la tabla para la vista del registro que se consulta. Para obtener información sobre cómo acceder a la consulta predeterminada, consulta Cómo consultar una vista de registro.
Para usar las consultas que se muestran en este documento en el Página de BigQuery Studio, reemplazar TABLE_NAME_OF_LOG_VIEW con la ruta de acceso a la tabla en el conjunto de datos vinculado. Por ejemplo, para consultar la vista _AllLogs en el conjunto de datos vinculado mydataset que se encuentra en el proyecto myproject, establece este campo en myproject.mydataset._AllLogs:

En la consola de Google Cloud, ve a la página de BigQuery:
Ve a BigQuery Studio.

También puedes usar la barra de búsqueda para encontrar esta página.
Para abrir la página Análisis de registros, haz lo siguiente:
1. En la consola de Google Cloud, ve a la página Análisis de registros:
  Ir a Análisis de registros
  
  Si usas la barra de búsqueda para encontrar esta página, selecciona el resultado cuyo subtítulo es Logging.
2. Opcional: Para identificar el esquema de la vista de registro, en la lista Vistas de registro, busca la vista y, luego, selecciona nombre de la vista.
Se muestra el esquema. Puedes usar el campo Filtro para ubicar campos específicos. No puedes modificar el esquema.

Filter logs

Las consultas en SQL determinan qué entradas de la vista de registro se deben procesar y, luego, agrupan estas entradas y realizar operaciones de agregación. Cuando no se enumeran operaciones de agrupación ni agregación, el resultado de la consulta incluye las filas seleccionadas por la operación de filtro. En los ejemplos de esta sección, se muestra el filtrado.

Filtrar por hora

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

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

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

Para obtener más información sobre cómo 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 más reciente de datos, luego retiene aquellas 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_NAME_OF_LOG_VIEW`
WHERE
  resource.type = "gce_instance"
ORDER BY timestamp ASC
LIMIT 100

Filtrar por gravedad

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

Por ejemplo, la siguiente consulta lee la hora más reciente de los datos 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_NAME_OF_LOG_VIEW`
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, la consulta muestra todas las entradas de registro cuyo nivel de gravedad es de 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 del log_name o el campo log_id. El campo log_name incluye el recurso ruta de acceso. 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 los datos de la hora más reciente y, luego, retiene las filas en las que el valor del campo log_id es cloudaudit.googleapis.com/data_access y, luego, ordena y muestra resultados:

SELECT
  timestamp, log_id, severity, json_payload, resource, labels
FROM
  `TABLE_NAME_OF_LOG_VIEW`
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 recurso específico. Por ejemplo, el descriptor de una instancia de Compute Engine incluye las etiquetas de la zona, el ID del proyecto y el ID de la instancia. Cuando se escribe una entrada de registro y se asignan valores a cada campo. Lo siguiente es ejemplo:

{
   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, incluye una restricción. como resource.labels.zone = "us-centra1-f", en una consulta genera una 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 aquellas filas en las que el recurso es una instancia de Compute Engine ubicado en la zona us-central1-f:

SELECT
  timestamp, log_name, severity, JSON_VALUE(resource.labels.zone) AS zone, json_payload, resource, labels
FROM
  `TABLE_NAME_OF_LOG_VIEW`
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 JSON consulta Funciones JSON.

Filtrar por solicitud HTTP

Filtrar la vista de registro para que solo incluya entradas de registro que correspondan a una solicitar o responder, agrega una restricción http_request IS NOT NULL:

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

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

SELECT
  timestamp, log_name, severity, http_request, resource, labels
FROM
  `TABLE_NAME_OF_LOG_VIEW`
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 solicite la Se debe definir el campo http_request.status:

SELECT
  timestamp, log_name, http_request.status, http_request, resource, labels
FROM
  `TABLE_NAME_OF_LOG_VIEW`
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, consulta el esquema o la visualización. en el campo. Los resultados de la consulta anterior muestran que el El campo http_request.status almacena valores de número entero.

Cómo filtrar por un campo con un tipo JSON

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

Ten en cuenta las siguientes consultas:

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

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

Las consultas anteriores prueban el valor del campo json_payload en el entrada de registro. Ambas consultas Descarta las entradas de registro que no contengan un campo con la etiqueta json_payload. La diferencia entre estas dos consultas es la línea final, que define lo que se prueba en NULL. Ahora, considera una vista de registro que tenga dos entradas de registro. Para una entrada de registro, el campo json_payload tiene el siguiente formato:

{
    status: {
        measureTime: "1661517845"
    }
}

Para la otra entrada de registro, el campo 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 entradas de registro anteriores cumplen con la restricción json_payload.status IS NOT NULL Es decir, el resultado de la primera consulta incluye ambas entradas de registro. Sin embargo, cuando la restricción es JSON_VALUE(json_payload.status) IS NOT NULL, solo se incluye la segunda entrada de registro en el resultado de la consulta.

Filtrar por expresión regular

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

La siguiente consulta muestra las entradas de registro más recientes recibidas, conserva esas entradas con un campo json_payload.jobName y, luego, muestra el parte del nombre que comienza con test:

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

Para ver más ejemplos, consulta el Documentación de REGEXP_EXTRACT Para ver ejemplos de otras expresiones regulares que 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 muestra en la ilustración, usa la función CONTAINS_SUBSTR.

Agrupa y agrega entradas de registro

Esta sección se basa en los ejemplos anteriores y explica cómo puedes agrupar y agregar entradas de registro. Si no especificas una agrupación, pero sí especificas una agregación, se imprime un solo resultado porque SQL trata todos filas que satisfacen la cláusula WHERE como un solo grupo.

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

Agrupar por tiempo

Para agrupar datos por tiempo, usa la función TIMESTAMP_TRUNC. que trunca una marca de tiempo a un nivel de detalle específico, como MINUTE. Para 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

La siguiente consulta lee los datos recibidos en el intervalo especificado por el selector de intervalo de tiempo y, luego, retiene las filas en las que el valor del campo json_payload.status no sea NULL. La consulta trunca la marca de tiempo de cada fila por hora y, luego, agrupa las filas por 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_NAME_OF_LOG_VIEW`
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 más reciente de los datos y, luego, agrupa los las entradas de registro según el tipo de recurso. Luego, cuenta el número de filas para cada el tipo de recurso y muestra una tabla con dos columnas. La primera columna enumera las el tipo de recurso, mientras que la segunda columna es la cantidad de filas tipo de recurso:

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

Agrupar por gravedad

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

SELECT
  severity, COUNT(*) AS count
FROM
  `TABLE_NAME_OF_LOG_VIEW`
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 los registros y la segunda, la cantidad de entradas de registro que se escribieron en el registro. El consulta ordena los resultados según el recuento de entradas:

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

Cómo calcular la latencia promedio de una solicitud HTTP

La siguiente consulta ilustra la agrupación por varias columnas y el cálculo un valor promedio. La consulta agrupa filas según la URL que se encuentra en las por solicitud y por el valor del campo labels.checker_location. Después del cuando agrupas 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_NAME_OF_LOG_VIEW`
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 se usa esta función para extraer el valor del http_request.latency.seconds. El último campo tiene un tipo de datos que entero.

Calcula el promedio de bytes enviados para una prueba de subred

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

La consulta lee la hora más reciente de los datos y, luego, retiene solo esas filas cuya columna de tipo de recurso es gce_subnetwork y cuyo json_payload no sea NULL. Luego, la consulta agrupa las filas por la ubicación de recurso. A diferencia del ejemplo anterior, en el que los datos se almacenan como valores value, el valor del campo bytes_sent es una cadena y, por lo tanto, convierte el valor en 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_NAME_OF_LOG_VIEW`
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 indica 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 JSON consulta Funciones 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 coincida con un patrón

Para mostrar 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 conserva 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 la cantidad de entradas en cada grupo:

SELECT
  REGEXP_EXTRACT(JSON_VALUE(json_payload.jobName), r".*(test.*)$") AS name,
  COUNT(*) AS count
FROM
  `TABLE_NAME_OF_LOG_VIEW`
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 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 en varias columnas de una tabla.

Búsqueda basada en tokens

Para buscar entradas que coincidan con un conjunto de términos de búsqueda en una vista de registro, usa la función SEARCH. Esta función requiere dos parámetros: dónde buscar y la consulta de búsqueda. Como 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 coincida exactamente con “35.193.12.15”:

SELECT
  timestamp, log_id, proto_payload, severity, resource.type, resource, labels
FROM
  `TABLE_NAME_OF_LOG_VIEW` 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, los acentos graves unen el valor que se debe buscar. Esta garantiza que la función SEARCH busque una coincidencia exacta entre un valor de campo y el valor entre las comillas simples.

Cuando se omiten las virgulillas en la cadena de consulta, esta se divide según 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 sentencia SEARCH anterior coincide con una fila cuando un solo campo coincide con los cuatro tokens. No importa el orden de los tokens.

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

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

La instrucción anterior busca en todos los campos de las entradas de registro en la vista de registros. mientras que la instrucción original busca solo en el campo log_id de la las entradas de registro.

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

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

Por último, puedes buscar campos específicos en lugar de buscar un en toda la tabla. Por ejemplo, la siguiente sentencia solo busca en las columnas 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 lo siguiente: consulta la página de referencia de BigQuery Funciones de búsqueda.

Búsqueda de subcadenas

Para realizar una prueba que no distingue mayúsculas de minúsculas para determinar si existe un valor 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 de 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 muestra los 20 más antiguos:

SELECT
  timestamp, log_id, proto_payload, severity, resource.type, resource, labels
FROM
  `TABLE_NAME_OF_LOG_VIEW` 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 substring. Por lo tanto, una fila que contiene “35.193.12.152” coincide con la declaración CONTAINS_SUBSTR.

Combinar datos de varias fuentes

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

Combina datos de dos tablas con uniones

Para combinar información de dos tablas, usa la función join. operadores. 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 en dos tablas diferentes escritas por el mismo intervalo de seguimiento. La consulta realiza una interno JOIN 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, Luego, la consulta selecciona los campos timestamp, severity y json_payload. que provenían de TABLE_NAME_OF_LOG_VIEW_1, el campo json_payload de ABLE_NAME_OF_LOG_VIEW_2 y los valores de span_id y trace en los que se unieron las dos tablas, y devuelve hasta 100 filas:

SELECT
  a.timestamp, a.severity, a.json_payload, b.json_payload, a.span_id, a.trace
FROM `TABLE_NAME_OF_LOG_VIEW_1` a
JOIN `ABLE_NAME_OF_LOG_VIEW_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 sentencias SELECT y descartar las filas duplicadas, usa el operador UNION. Para conservar el duplicado filas, usa el operador UNION ALL.

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

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

Limitaciones

Las consultas usadas en la página Análisis de registros son compatibles con las funciones de GoogleSQL con algunas excepciones.

Los siguientes comandos SQL no son compatibles con las consultas SQL emitidas con el Página Análisis de registros:

Comandos DDL y DML
Funciones definidas por el usuario de JavaScript
Funciones de BigQuery ML
Variables de SQL

Lo siguiente solo se admite cuando consultas un conjunto de datos vinculado usando las páginas BigQuery Studio y Looker Studio Herramienta de línea de comandos de bq:

Funciones de JavaScript definidas por el usuario
Funciones de BigQuery ML
Variables de SQL

¿Qué sigue?

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

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