Consultas SQL prontas para uso em cenários comuns de análise de logs: alertas, monitoramento de tráfego, análise de latência e observabilidade de serviços web Tomcat.
Acionar um alerta quando a taxa de erro ultrapassar 40% nos últimos 5 minutos
Para gerar alertas sobre picos de erros HTTP 500, calcule a taxa de erro por tópico em janelas de 5 minutos. Use max_by para identificar a janela com maior contagem de erros e filtre os tópicos em que essa janela represente mais de 40% do total. A cláusula HAVING aplica ambas as condições: taxa de erro superior a 40% e mínimo de 500 erros totais, evitando falsos positivos em tópicos com pouco tráfego.
status:500 | select __topic__, max_by(error_count,window_time)/1.0/sum(error_count) as error_ratio, sum(error_count) as total_error from (
select __topic__, count(*) as error_count , __time__ - __time__ % 300 as window_time from log group by __topic__, window_time
)
group by __topic__ having max_by(error_count,window_time)/1.0/sum(error_count) > 0.4 and sum(error_count) > 500 order by total_error desc limit 100Coletar estatísticas de tráfego e configurar alertas
Para monitorar o tráfego de entrada por minuto e gerar alertas quando ele cair abaixo de um limiar, agregue o tráfego por minuto e normalize-o para uma taxa minutal. O divisor greatest(max(__time__) - min(__time__),1) trata janelas inferiores a um minuto nos limites da consulta. A função greatest garante que o denominador seja pelo menos 1, evitando divisão por zero quando todos os eventos ocorrem no mesmo segundo.
* | SELECT SUM(inflow) / greatest(max(__time__) - min(__time__),1) as inflow_per_minute, date_trunc('minute',__time__) as minute group by minuteCalcular a latência média por tamanho de dados
Para entender como o tamanho dos dados afeta a latência, agrupe as requisições em cinco faixas de tamanho usando CASE WHEN e calcule a latência média em cada grupo. Essa abordagem revela se payloads grandes originam os picos de latência.
* | select avg(latency) as latency , case when originSize < 5000 then 's1' when originSize < 20000 then 's2' when originSize < 500000 then 's3' when originSize < 100000000 then 's4' else 's5' end as os group by osRetornar as porcentagens de diferentes resultados
Para visualizar a participação de cada departamento na contagem total, use uma subconsulta para calcular as contagens por departamento e uma função de janela para obter o total geral. A expressão sum(c) over() soma todas as linhas sem colapsá-las, permitindo dividir a contagem de cada linha pelo total em uma única passagem.
* | select department, c*1.0/ sum(c) over () from(select count(1) as c, department from log group by department)Contar o número de URLs que atendem à condição de consulta
Para contar requisições de login e registro por minuto sem ramificações complexas, use COUNT_IF com um padrão LIKE para cada tipo de URL. A função COUNT_IF é mais concisa que a expressão equivalente com CASE WHEN e facilita a adição de novos padrões de URL.
* | select count_if(uri like '%login') as login_num, count_if(uri like '%register') as register_num, date_format(date_trunc('minute', __time__), '%m-%d %H:%i') as time group by time order by time limit 100Análise agregada geral
Consultar a distribuição global de clientes por PV
Para identificar os países de origem dos clientes e o volume de tráfego gerado por cada um, use a função ip_to_country para resolver endereços IP de clientes em países. Em seguida, agrupe por país e conte as visualizações de página (PVs). Exiba os resultados em um mapa mundial; consulte Configurar um mapa mundial.
* |
select
ip_to_country(client_ip) as ip_country,
count(*) as pv
group by
ip_country
order by
pv desc
limit
500Consultar a categoria e a tendência de PV dos métodos de requisição
Para acompanhar a evolução temporal dos diferentes métodos de requisição HTTP, trunque os timestamps para o minuto usando date_format(date_trunc('minute', ...)). Agrupe por tempo e request_method para obter as contagens de PV por método. Exiba os resultados em um gráfico de linhas com o eixo x definido como t, o eixo y como pv e a coluna de agregação como request_method. Para mais informações, consulte Gráfico de linhas.
* |
select
date_format(date_trunc('minute', __time__), '%m-%d %H:%i') as t,
request_method,
count(*) as pv
group by
t,
request_method
order by
t asc
limit
10000Consultar a distribuição de requisições por user agent e PV
Para detalhar o tráfego por user agent (volume de requisições, tamanho do tráfego e distribuição de códigos de status), agrupe por http_user_agent e calcule: total de PVs, tráfego de requisição e resposta em MB (arredondado para duas casas decimais) e a porcentagem de respostas em cada faixa de código de status (2xx, 3xx, 4xx, 5xx) usando expressões CASE WHEN. Exiba os resultados em uma tabela. Para mais informações, consulte Tabela básica.
* |
select
http_user_agent as "User agent",
count(*) as pv,
round(sum(request_length) / 1024.0 / 1024, 2) as "Request traffic (MB)",
round(sum(body_bytes_sent) / 1024.0 / 1024, 2) as "Response traffic (MB)",
round(
sum(
case
when status >= 200
and status < 300 then 1
else 0
end
) * 100.0 / count(1),
6
) as "Percentage of status code 2xx (%)",
round(
sum(
case
when status >= 300
and status < 400 then 1
else 0
end
) * 100.0 / count(1),
6
) as "Percentage of status code 3xx (%)",
round(
sum(
case
when status >= 400
and status < 500 then 1
else 0
end
) * 100.0 / count(1),
6
) as "Percentage of status code 4xx (%)",
round(
sum(
case
when status >= 500
and status < 600 then 1
else 0
end
) * 100.0 / count(1),
6
) as "Percentage of status code 5xx (%)"
group by
"User agent"
order by
pv desc
limit
100Consultar o consumo diário e a previsão de tendência para o mês atual
Para visualizar o gasto diário real e a previsão para o restante do mês, desduplique os registros de faturamento por RecordID, agregue os totais diários e passe o resultado para sls_inner_ts_regression. Essa função recebe o timestamp, o total diário, um array de rótulos, o intervalo de previsão em segundos (86400 para diário) e o número de pontos de previsão (60), retornando valores reais e previstos. Quando res.real for NaN, a linha representa um ponto de previsão; a expressão CASE WHEN exibe valores previstos apenas para datas futuras. Exiba em um gráfico de linhas com o eixo x definido como time e dois eixos y para consumo real e previsto. Para mais informações, consulte Gráfico de linhas.
source :bill |
select
date_format(res.stamp, '%Y-%m-%d') as time,
res.real as "Actual consumption",case
when is_nan(res.real) then res.pred
else null
end as "Forecast consumption",
res.instances
from(
select
sls_inner_ts_regression(
cast(day as bigint),
total,
array ['total'],
86400,
60
) as res
from
(
select
*
from
(
select
*,
max(day) over() as lastday
from
(
select
to_unixtime(date_trunc('day', __time__)) as day,
sum(PretaxAmount) as total
from
(
select
RecordID,
arbitrary(__time__) as __time__,
arbitrary(ProductCode) as ProductCode,
arbitrary(item) as item,
arbitrary(PretaxAmount) as PretaxAmount
from
log
group by
RecordID
)
group by
day
order by
day
)
)
where
day < lastday
)
)
limit
1000Consultar a porcentagem de consumo de cada serviço no mês atual
Para identificar quais serviços representam a maior parte da fatura, agregue os gastos por ProductName e classifique-os por despesa total usando a função de janela row_number. Em seguida, agrupe os serviços classificados do 7º lugar em diante (ou com cobranças zero ou negativas) na categoria "Other". Exiba os resultados em um gráfico de rosca. Para mais informações, consulte Configurar um gráfico de rosca.
source :bill |
select
case
when rnk > 6
or pretaxamount <= 0 then 'Other'
else ProductName
end as ProductName,
sum(PretaxAmount) as PretaxAmount
from(
select
*,
row_number() over(
order by
pretaxamount desc
) as rnk
from(
select
ProductName,
sum(PretaxAmount) as PretaxAmount
from
log
group by
ProductName
)
)
group by
ProductName
order by
PretaxAmount desc
limit
1000Comparar a despesa de ontem com o mesmo dia do mês anterior
Para comparar o gasto de ontem com o mesmo dia do mês passado, use a função compare com um deslocamento de 604800 segundos (7 dias). A função COALESCE garante que dias sem cobranças retornem 0 em vez de NULL. O array de saída diff contém três valores: diff[1] é a despesa de ontem, diff[2] é o valor do mesmo dia no mês anterior e diff[3] é a razão. Use round para arredondar o valor interno da despesa para três casas decimais. Formate os valores externos de diff para duas casas decimais e multiplique diff[3] por 100 menos 100 para expressar a razão como variação percentual. Exiba em um gráfico de tendências; consulte Gráfico de tendências.
source :bill |
select
round(diff [1], 2),
round(diff [2], 2),
round(diff [3] * 100 -100, 2)
from(
select
compare("Expense of the previous day", 604800) as diff
from(
select
round(coalesce(sum(PretaxAmount), 0), 3) as "Expense of the previous day"
from
log
)
)Análise de serviço web Tomcat
Consultar a tendência de status de requisições do Tomcat
Para analisar a variação temporal dos códigos de status de requisições do Tomcat, use date_trunc para truncar os timestamps para o minuto e date_format para formatar o tempo em horas e minutos. Agrupe por tempo e status para contar as requisições por código de status a cada minuto. Exiba em um gráfico de fluxo com o eixo x definido como time, o eixo y como count e a coluna de agregação como status. Para mais informações, consulte Gráfico de fluxo.
* |
select
date_format(date_trunc('minute', __time__), '%H:%i') as time,
COUNT(1) as c,
status
GROUP by
time,
status
ORDER by
time
LIMIT
1000Consultar a distribuição de PVs e UVs para acesso ao Tomcat ao longo do tempo
Para acompanhar visualizações de página (PVs) e visitantes únicos (UVs) na mesma linha do tempo, use time_series para agrupar eventos em intervalos de 2 minutos e approx_distinct para contar valores únicos de remote_addr. A função approx_distinct usa HyperLogLog para deduplicação aproximada eficiente, precisa o suficiente para monitoramento sem o custo da deduplicação exata. Exiba em um gráfico de linhas com múltiplos eixos, definindo o eixo x como time e dois eixos y para uv e pv. Para mais informações, consulte Adicionar um gráfico de linhas com múltiplos eixos y.
* |
select
time_series(__time__, '2m', '%H:%i', '0') as time,
COUNT(1) as pv,
approx_distinct(remote_addr) as uv
GROUP by
time
ORDER by
time
LIMIT
1000Consultar o número de requisições com erro no Tomcat e comparar com a hora anterior
Para comparar a contagem atual de erros com a de uma hora atrás, use a função compare com um deslocamento de 3600 segundos. A função retorna um array: c1 é a contagem atual de erros, c2 é a contagem de 3.600 segundos atrás e c3 é a variação percentual (c1/c2 * 100 - 100). Exiba em um gráfico de rosca usando c1 como valor de exibição e c3 como valor de comparação. Para mais informações, consulte Configurar um gráfico de rosca.
status >= 400 |
SELECT
diff [1] AS c1,
diff [2] AS c2,
round(diff [1] * 100.0 / diff [2] - 100.0, 2) AS c3
FROM
(
select
compare(c, 3600) AS diff
from
(
select
count(1) as c
from
log
)
)Consultar as 10 principais URIs nas requisições do Tomcat
Para encontrar as URIs mais requisitadas, agrupe por request_uri, conte os PVs por URI e retorne as 10 principais em ordem decrescente. Exiba em um gráfico de barras horizontais com o eixo x definido como page e o eixo y como pv. Para mais informações, consulte Configurar um gráfico de barras horizontais básico.
* |
SELECT
request_uri as page,
COUNT(*) as pv
GROUP by
page
ORDER by
pv DESC
LIMIT
10Consultar os tipos e a distribuição de clientes do Tomcat
Para identificar quais clientes acessam seu servidor Tomcat, agrupe por user_agent e conte as requisições por tipo. Exiba em um gráfico de rosca com o tipo definido como user_agent e a coluna de valor como c. Para mais informações, consulte Configurar um gráfico de rosca.
* |
SELECT
user_agent,
COUNT(*) AS c
GROUP BY
user_agent
ORDER BY
c DESCColetar estatísticas sobre o tráfego de saída do Tomcat
Para monitorar o tráfego de saída do Tomcat ao longo do tempo, use time_series para agrupar eventos em intervalos de 10 segundos e some body_bytes_sent por intervalo. Exiba em um gráfico de linhas com o eixo x definido como time e o eixo y como body-sent. Para mais informações, consulte Configurar o eixo x e o eixo y de um gráfico de linhas.
* |
select
time_series(__time__, '10s', '%H:%i:%S', '0') as time,
sum(body_bytes_sent) as body_sent
GROUP by
time
ORDER by
time
LIMIT
1000Consultar a porcentagem de requisições com erro no Tomcat
Para verificar a fração de requisições com erro, calcule a contagem de erros e a contagem total em uma subconsulta usando CASE WHEN status >= 400. Divida e arredonde para duas casas decimais na consulta externa. Exiba em um medidor radial. Para mais informações, consulte Configurar um medidor radial.
* |
select
round((errorCount * 100.0 / totalCount), 2) as errorRatio
from
(
select
sum(
case
when status >= 400 then 1
else 0
end
) as errorCount,
count(1) as totalCount
from
log
)