Neste documento, você encontra exemplos de consultas em entradas de registro armazenadas em buckets de registros atualizados para usar a Análise de dados de registros.
Nesses buckets, é possível executar consultas SQL na página Análise de registros no console do Google Cloud. Para mais amostras, consulte os repositórios do GitHub
logging-analytics-samples
e
security-analytics
(links em inglês).
Neste documento, não descrevemos o SQL nem como rotear e armazenar entradas de registro. Para saber mais sobre esses tópicos, consulte a seção Próximas etapas.
Antes de começar
Para usar as consultas mostradas neste documento na página Análise de dados de registros, substitua TABLE pelo nome da tabela que corresponde à visualização que você quer consultar. O nome da tabela tem o formato
project_ID.region.bucket_ID.view_ID
. Você encontra o nome da tabela de uma visualização na página Análise de dados de registros. A consulta padrão de uma visualização de registro lista o nome da tabela na instruçãoFROM
. Para informações sobre como acessar a consulta padrão, consulte Consultar uma visualização de registro.Para usar as consultas mostradas neste documento na página do BigQuery Studio, substitua TABLE pelo caminho para a tabela no conjunto de dados vinculado. Por exemplo, para consultar a visualização
_AllLogs
no conjunto de dados vinculadomydataset
que está no projetomyproject
, defina esse campo comomyproject.mydataset._AllLogs
:No painel de navegação do console do Google Cloud, selecione BigQuery:
Para abrir a página Análise de dados de registros, faça o seguinte:
-
No painel de navegação do console do Google Cloud, selecione Logging e, em seguida, Análise de Registros:
Opcional: para identificar o esquema da tabela da visualização de registro, encontre a visualização na lista Visualizações de registros e selecione o nome dela.
O esquema da tabela é exibido. Use o campo Filtro para localizar campos específicos. Não é possível modificar o esquema.
-
Filtrar registros
As consultas SQL determinam quais linhas da tabela serão processadas. Em seguida, elas agrupam as linhas e executam operações de agregação. Quando nenhuma operação de agrupamento e agregação estiver listada, o resultado da consulta incluirá as linhas selecionadas pela operação de filtro. Os exemplos nesta seção ilustram a filtragem.
Filtrar por tempo
Para definir o intervalo de tempo da consulta, recomendamos que você use o seletor de intervalo de tempo. Esse seletor é usado automaticamente quando uma consulta
não especifica um campo timestamp
na cláusula WHERE
.
Por exemplo, para consultar os dados da última semana, selecione Últimos sete dias no seletor de período. Você também pode usar o seletor de intervalo de tempo para especificar um horário de início e de término, especificar um horário para visualizar e alterar os fusos horários.
Se você incluir um campo timestamp
na cláusula WHERE
, a configuração do seletor de intervalo de tempo não será usada. O exemplo a seguir filtra os dados
usando a função TIMESTAMP_SUB
, que permite especificar um intervalo de retrospectiva
do horário atual:
WHERE
timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
Para mais informações sobre como filtrar por tempo, consulte Funções de tempo e Funções de carimbo de data/hora.
Filtrar por recurso
Para filtrar por recurso, adicione uma restrição resource.type
.
Por exemplo, a consulta a seguir lê a hora mais recente de dados, mantém as linhas cujo tipo de recurso corresponde a gce_instance
e, em seguida, classifica e exibe até 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 gravidade
É possível filtrar por uma gravidade específica com uma restrição como
severity = 'ERROR'
. Outra opção é usar a instrução IN
e especificar um conjunto de valores válidos.
Por exemplo, a consulta a seguir lê a hora mais recente de dados e mantém apenas as linhas que contêm um campo severity
, cujo valor é 'INFO'
ou '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
A consulta anterior filtra pelo valor do campo severity
. No entanto, também é possível escrever consultas que filtram pelo valor numérico da gravidade do registro.
Por exemplo, se você substituir as linhas severity
pelas seguintes, a consulta retornará todas as entradas de registro com nível de gravidade pelo menos NOTICE
:
severity_number IS NOT NULL AND
severity_number > 200
Para informações sobre os valores enumerados, consulte
LogSeverity
.
Filtrar por nome do registro
Para filtrar por um nome de registro, adicione uma restrição ao valor do campo log_name
ou log_id
. O campo log_name
inclui o caminho do recurso. Ou seja, esse campo tem valores como projects/myproject/logs/mylog
.
O campo log_id
armazena apenas o nome do registro, como mylog
.
Por exemplo, a consulta a seguir lê a hora mais recente de dados, mantém as linhas em que o valor no campo log_id
é cloudaudit.googleapis.com/data_access
e classifica e exibe os 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 rótulo de recurso
A maioria dos descritores de recursos monitorados define rótulos usados para identificar o recurso específico. Por exemplo, o descritor de uma instância do Compute Engine inclui rótulos para a zona, o ID do projeto e o ID da instância. Quando a entrada de registro é gravada, os valores são atribuídos a cada campo. Confira este exemplo:
{
type: "gce_instance"
labels: {
instance_id: "1234512345123451"
project_id: "my-project"
zone: "us-central1-f"
}
}
Como o tipo de dados do campo labels
é JSON, incluir uma restrição como resource.labels.zone = "us-centra1-f"
em uma consulta resulta em um erro de sintaxe. Para receber o valor de um campo com um tipo de dados JSON, use a função
JSON_VALUE
.
Por exemplo, a consulta a seguir lê os dados mais recentes e retém as linhas em que o recurso é uma instância do Compute Engine localizada na 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 informações sobre todas as funções que podem recuperar e transformar dados JSON, consulte Funções JSON.
Filtrar por solicitação HTTP
Para filtrar a tabela para incluir apenas linhas que correspondam a uma solicitação ou resposta HTTP, adicione uma restrição 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
A consulta a seguir inclui apenas linhas que correspondem a solicitações GET
ou 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 status HTTP
Para filtrar por status HTTP, modifique a cláusula WHERE
para exigir que o campo http_request.status
seja definido:
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 o tipo de dados armazenados em um campo, confira o esquema ou o campo. Os resultados da consulta anterior mostram que o campo http_request.status
armazena valores inteiros.
Filtrar por um campo com um tipo JSON
Para extrair um valor de uma coluna com tipo de dados JSON, use a função
JSON_VALUE
.
Considere as seguintes consultas:
SELECT
json_payload
FROM
`TABLE`
WHERE
json_payload.status IS NOT NULL
e
SELECT
json_payload
FROM
`TABLE`
WHERE
JSON_VALUE(json_payload.status) IS NOT NULL
As consultas anteriores testam o valor da coluna json_payload
. O conteúdo
dessa coluna é determinado pelo conteúdo de uma entrada de registro. Ambas as consultas descartam linhas que não contenham uma coluna rotulada json_payload
.
A diferença entre essas duas consultas é a linha final, que define
o que é testado em relação a NULL
. Agora, considere uma tabela que tem duas linhas. Em uma
linha, a coluna json_payload
tem o seguinte formato:
{
status: {
measureTime: "1661517845"
}
}
Na outra linha, a coluna json_payload
tem uma estrutura 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"
}
As duas linhas anteriores atendem à restrição json_payload.status IS NOT NULL
.
Ou seja, o resultado da consulta inclui ambas as linhas.
No entanto, quando a restrição é JSON_VALUE(json_payload.status) IS NOT NULL
,
somente a segunda linha é incluída no resultado.
Filtrar por expressão regular
Para retornar a substring que corresponde a uma expressão regular, use a função
REGEXP_EXTRACT
. O tipo de retorno dessa função é
STRING
ou BYTES
.
A consulta a seguir exibe as entradas de registro mais recentes recebidas, retém essas entradas com um campo json_payload.jobName
e exibe a parte do nome que começa com 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 mais exemplos, consulte a
documentação do REGEXP_EXTRACT
.
Para ver exemplos de outras expressões regulares que você pode usar, consulte Funções, operadores e condicionais.
A consulta mostrada neste exemplo não é eficiente. Para uma correspondência de substring, como a ilustrada, use a função CONTAINS_SUBSTR
.
Agrupar e agregar entradas de registro
Esta seção se baseia nos exemplos anteriores e ilustra como agrupar e agregar linhas da tabela. Se você não especificar um agrupamento, mas especificar uma agregação, um único resultado será exibido, porque o SQL trata todas as linhas que atendem à cláusula WHERE
como um único grupo.
Todas as expressões SELECT
precisam ser incluídas nos campos do grupo ou agregadas.
Agrupar por tempo
Para agrupar dados por tempo, use a função TIMESTAMP_TRUNC
,
que trunca um carimbo de data/hora para uma granularidade especificada, como MINUTE
. Por
exemplo, um carimbo de data/hora de 15:30:11
, que é formatado como
hours:minutes:seconds
, se torna 15:30:00
quando a granularidade é definida como
MINUTE
.
A consulta a seguir lê os dados recebidos no intervalo especificado pelo
seletor de período e mantém
as linhas em que o valor do campo json_payload.status
não é NULL.
A consulta trunca o carimbo de data/hora em cada linha por hora e, em seguida, agrupa as linhas pelo carimbo de data/hora e status 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 mais amostras, consulte a
documentação do TIMESTAMP_TRUNC
.
Para mais informações sobre outras funções baseadas em tempo, consulte
Funções de data e hora.
Agrupar por recurso
A consulta a seguir lê a hora mais recente de dados e agrupa as linhas pelo tipo de recurso. Em seguida, conta o número de linhas de cada tipo e retorna uma tabela com duas colunas. A primeira coluna lista o tipo de recurso, enquanto a segunda coluna é o número de linhas para esse tipo de recurso:
SELECT
resource.type, COUNT(*) AS count
FROM
`TABLE`
GROUP BY resource.type
LIMIT 100
Agrupar por gravidade
A consulta a seguir lê a hora mais recente de dados e mantém as linhas que têm um campo de gravidade. A consulta agrupa as linhas por gravidade e conta o número de linhas 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
O resultado da consulta a seguir é uma tabela com duas colunas. A primeira coluna lista os nomes de registro, enquanto a segunda coluna lista o número de entradas de registro que foram gravadas nesse registro na hora mais recente. A consulta classifica os resultados pela contagem de entradas:
SELECT
log_id, COUNT(*) AS count
FROM
`TABLE`
GROUP BY log_id
ORDER BY count DESC
LIMIT 100
Calcular a latência média para solicitação HTTP
A consulta a seguir ilustra o agrupamento por várias colunas e o cálculo de um valor médio. A consulta agrupa linhas pelo URL contido na solicitação HTTP e pelo valor do campo labels.checker_location
. Depois de agrupar as linhas, a consulta calcula a latência média 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
Na expressão anterior, JSON_VALUE
é necessário para extrair o valor
do campo labels.checker_location
porque o tipo de dados para
labels
é JSON.
No entanto, essa função não é usada para extrair o valor do campo http_request.latency.seconds
. O último campo tem um tipo de dados de
número inteiro.
Calcular a média de bytes enviados para um teste de sub-rede
A consulta a seguir ilustra como exibir o número médio de bytes enviados por local.
A consulta lê a hora mais recente de dados e mantém apenas as linhas
cuja coluna de tipo de recurso é gce_subnetwork
e cuja coluna json_payload
não é NULL. Em seguida, a consulta agrupa as linhas pelo local do
recurso. Ao contrário do exemplo anterior, em que os dados são armazenados como um valor numérico, o valor do campo bytes_sent
é uma string. Portanto, é preciso converter o valor em FLOAT64
antes de calcular a média:
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
O resultado da consulta anterior é uma tabela em que cada linha lista um local e a média de bytes enviados para esse local.
Para informações sobre todas as funções que podem recuperar e transformar dados JSON, consulte Funções JSON.
Para informações sobre CAST
e outras funções de conversão, consulte Funções de conversão.
Contar as entradas de registro com um campo que corresponda a um padrão
Para retornar a substring que corresponde a uma expressão regular, use a função
REGEXP_EXTRACT
. O tipo de retorno dessa função é
STRING
ou BYTES
.
A consulta a seguir mantém as entradas de registro para as quais o valor do campo json_payload.jobName
não é NULL.
Em seguida, ele agrupa as entradas pelo sufixo de nome que começa
com test
. Por fim, a consulta conta o número de entradas em 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 mais exemplos, consulte a
documentação do REGEXP_EXTRACT
.
Para ver exemplos de outras expressões regulares que você pode usar, consulte Funções, operadores e condicionais.
Pesquisa em várias colunas
Nesta seção, descrevemos duas abordagens diferentes que podem ser usadas para pesquisar várias colunas de uma tabela.
Pesquisa baseada em token
Para pesquisar em uma tabela entradas que correspondam a um conjunto de termos de pesquisa, use a função SEARCH
. Essa função requer dois parâmetros: onde pesquisar e a consulta de pesquisa.
Como a função SEARCH
tem regras específicas sobre como os dados são pesquisados, recomendamos que você leia a documentação do SEARCH
.
A consulta a seguir mantém apenas as linhas com um campo que corresponde exatamente a "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
Na consulta anterior, a crase envolve o valor a ser pesquisado. Isso
garante que a função SEARCH
procure uma correspondência exata entre
um valor de campo e o valor entre os acentos graves.
Quando os acentos graves são omitidos na string de consulta, esta é dividida com base nas regras definidas na documentação de SEARCH
.
Por exemplo, quando a seguinte instrução é executada, a string de consulta é dividida em quatro tokens: "35", "193", "12" e "15":
SEARCH(t,"35.193.12.15")
A instrução SEARCH
anterior corresponde a uma linha quando um único campo
corresponde aos quatro tokens. A ordem dos tokens não importa.
É possível incluir várias instruções SEARCH
em uma consulta. Por exemplo, na
consulta anterior, é possível substituir o filtro no ID do registro por uma
declaração como a seguinte:
SEARCH(t,"`cloudaudit.googleapis.com/data_access`")
A instrução anterior pesquisa a tabela inteira, enquanto a instrução original pesquisa apenas a coluna log_id
.
Para realizar várias pesquisas em uma coluna, separe as strings individuais com um espaço. Por exemplo, a instrução a seguir corresponde a linhas em que um campo contém "Hello World", "happy" e "days":
SEARCH(t,"`Hello World` happy days")
Por fim, você pode pesquisar colunas específicas de uma tabela em vez de pesquisar uma tabela inteira. Por exemplo, a instrução a seguir pesquisa apenas as colunas chamadas text_payload
e json_payload
:
SEARCH((text_payload, json_payload) ,"`35.222.132.245`")
Para informações sobre como os parâmetros da função SEARCH
são processados, consulte a página de referência do BigQuery Funções de pesquisa.
Pesquisa de substring
Para executar um teste que não diferencia maiúsculas de minúsculas e determinar se existe um valor em uma
expressão, use a função CONTAINS_SUBSTR
.
Essa função retornará TRUE
quando o valor existir. Caso contrário, retornará FALSE
. O valor da pesquisa precisa ser um literal STRING
, mas não o literal NULL
.
Por exemplo, a consulta a seguir busca todas as entradas de registro de auditoria de acesso a dados com um endereço IP específico cujos carimbos de data/hora estejam em um intervalo de tempo específico. Por fim, a consulta classifica os resultados e exibe os 20 resultados mais antigos:
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
A consulta anterior executa um teste de substring. Portanto, uma linha que contenha
"35.193.12.152" corresponde à instrução CONTAINS_SUBSTR
.
Combine dados de diferentes fontes
As instruções de consulta verificam uma ou mais tabelas ou expressões e retornam as linhas de resultados calculados. Por exemplo, é possível usar instruções de consulta para mesclar os resultados de instruções SELECT
em diferentes tabelas ou conjuntos de dados de várias maneiras e, em seguida, selecionar as colunas dos dados combinados.
Combinar dados de duas tabelas com mesclagens
Para combinar informações de duas tabelas, use um dos operadores join. O tipo de junção e a cláusula condicional que você usa determinam como as linhas são combinadas e descartadas.
A consulta a seguir fornece os campos json_payload
de linhas em duas tabelas diferentes escritas pelo mesmo período de trace. A consulta executa um
JOIN
interno em duas tabelas para linhas em que os valores das
colunas span_id
e trace
em ambas as tabelas correspondem. A partir desse resultado, a consulta seleciona os campos timestamp
, severity
e json_payload
que vieram de TABLE_1, o campo json_payload
de TABLE_2 e os valores dos campos span_id
e trace
em que as duas tabelas foram unidas e retorna até 100 linhas:
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
Combinar várias seleções com uniões
Para combinar os resultados de duas ou mais instruções SELECT
e descartar linhas duplicadas, use o operador UNION
. Para reter linhas duplicadas, use o operador UNION ALL
.
A consulta a seguir lê a hora mais recente de dados de TABLE_1, mescla o resultado com a hora mais recente de dados de TABLE_2, classifica os dados mesclados aumentando o carimbo de data/hora e, em seguida, exibe as 100 entradas mais antigas:
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
A seguir
Para informações sobre como rotear e armazenar entradas de registro, consulte os seguintes documentos:
- Criar um bucket de registros
- Faça upgrade de um bucket para usar a Análise de dados de registros
- Vincular um bucket de registros a um conjunto de dados do BigQuery
- Configurar e gerenciar coletores
Para conferir a documentação de referência do SQL, consulte os seguintes documentos: