Consultas SQL prontas para uso em cenários comuns de análise de log: alertas, monitoramento de tráfego, análise de latência e observabilidade de web services do Tomcat.
Disparar um alerta quando a taxa de erro exceder 40% nos últimos 5 minutos
Para gerar um alerta sobre um pico de erros HTTP 500, calcule a taxa de erro por tópico em janelas de 5 minutos usando max_by para encontrar a janela com a maior contagem de erros. Em seguida, filtre os tópicos em que essa janela representa mais de 40% do total de erros. A cláusula HAVING aplica ambas as condições: taxa de erro acima de 40% e um mínimo de 500 erros no total, o que exclui 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 100Colete estatísticas de tráfego e configure alertas
Para monitorar o tráfego de entrada por minuto e gerar um alerta quando ele cair abaixo de um limiar, agregue o tráfego por minuto e normalize-o para uma taxa por minuto. O divisor greatest(max(__time__) - min(__time__),1) lida com janelas de menos de um minuto nos limites da consulta. A função greatest garante que o denominador seja pelo menos 1, o que evita a 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 dos dados
Para entender como o tamanho dos dados afeta a latência, agrupe as solicitações em cinco faixas de tamanho usando CASE WHEN e calcule a latência média em cada grupo. Isso revela se as cargas úteis grandes são a origem dos 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 ver 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() calcula a soma em 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 as solicitações de login e registro por minuto sem ramificações complexas, use COUNT_IF com um padrão LIKE para cada tipo de URL. O COUNT_IF é mais conciso que a expressão equivalente CASE WHEN e mais fácil de estender quando você precisa rastrear padrões de URL adicionais.
* | 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 de agregação geral
Consultar a distribuição global de clientes por PV
Para ver de quais países seus clientes se conectam e quanto tráfego cada um gera, use a função ip_to_country para resolver os endereços IP dos 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-múndi, conforme descrito em Configure a world map.
* |
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 solicitação
Para acompanhar a tendência de diferentes métodos de solicitação HTTP ao longo do tempo, trunque os carimbos de data e hora para o minuto usando date_format(date_trunc('minute', ...)). Depois, agrupe por tempo e request_method para obter as contagens de PV por método. Exiba os resultados em um gráfico de linhas, defina o eixo x como t, o eixo y como pv e a coluna de agregação como request_method. Para mais informações, consulte Line chart.
* |
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 solicitações por user agent e PV
Para detalhar o tráfego por user agent, incluindo volume de solicitações, tamanho do tráfego e distribuição de códigos de status, agrupe por http_user_agent. Em seguida, calcule o total de PVs, o tráfego de solicitaçã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 Basic table.
* |
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 junto com uma previsão para o restante do mês, remova a duplicação dos registros de faturamento por RecordID e agregue os totais diários. Em seguida, passe o resultado para sls_inner_ts_regression. Essa função recebe o carimbo de data e hora, o total diário, uma matriz 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 os valores reais e previstos. Onde res.real é NaN, a linha é um ponto de previsão. A expressão CASE WHEN exibe os valores previstos apenas para datas futuras. Exiba os dados em um gráfico de linhas, defina o eixo x como time e os dois eixos y para o consumo real e previsto. Para mais informações, consulte Line chart.
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 ver quais serviços representam a maior parte da sua fatura, agregue os gastos por ProductName. Classifique os serviços por despesa total usando a função de janela row_number e agrupe os serviços classificados do 7º lugar em diante (ou com cobranças zero ou negativas) em uma categoria "Outros". Exiba os resultados em um gráfico de rosca. Para mais informações, consulte Configure a donut chart.
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 passado
Para comparar os gastos de ontem com o mesmo dia do mês passado, use a função compare com um deslocamento de 604.800 segundos (7 dias). A função COALESCE garante que os dias sem cobranças retornem 0 em vez de NULL. A matriz de saída diff contém três valores: diff[1] é a despesa de ontem, diff[2] é o mesmo dia do mês passado e diff[3] é a proporção. Use round para arredondar o valor interno da despesa para três casas decimais e formate os valores externos de diff para duas casas decimais. Além disso, multiplique diff[3] por 100 menos 100 para expressar a proporção como uma variação percentual. Exiba os dados em um gráfico de tendências, conforme descrito em Trend chart.
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 web service do Tomcat
Consultar a tendência de status das solicitações do Tomcat
Para ver como os códigos de status das solicitações do Tomcat mudam ao longo do tempo, use date_trunc para truncar os carimbos de data e hora para o minuto e date_format para formatar o tempo em horas e minutos. Em seguida, agrupe por tempo e status para contar as solicitações por código de status a cada minuto. Exiba os resultados em um gráfico de fluxo, defina o eixo x como time, o eixo y como count e a coluna de agregação como status. Para mais informações, consulte Flow chart.
* |
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 as visualizações de página (PVs) e os visitantes únicos (UVs) na mesma linha do tempo, use time_series para agrupar os eventos em intervalos de 2 minutos. Use também approx_distinct para contar os valores únicos de remote_addr. A função approx_distinct usa HyperLogLog para uma deduplicação aproximada e eficiente, com precisão suficiente para monitoramento sem o custo de uma deduplicação exata. Exiba os resultados em um gráfico de linhas de múltiplos eixos, defina o eixo x como time e os dois eixos y para uv e pv. Para mais informações, consulte Add a line chart with multiple y-axes.
* |
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 solicitaçõ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 uma matriz: 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 os resultados em um gráfico de rosca usando c1 como o valor de exibição e c3 como o valor de comparação. Para mais informações, consulte Configure a donut chart.
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 solicitações do Tomcat
Para encontrar as URIs mais solicitadas, agrupe por request_uri, conte os PVs por URI e retorne as 10 principais em ordem decrescente. Exiba os resultados em um medidor de barras, defina o eixo x como page e o eixo y como pv. Para mais informações, consulte Bar gauge.
* |
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 entender quais clientes estão acessando seu servidor Tomcat, agrupe por user_agent e conte as solicitações por tipo. Exiba os resultados em um gráfico de rosca, defina o tipo como user_agent e a coluna de valor como c. Para mais informações, consulte Configure a donut chart.
* |
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 os eventos em intervalos de 10 segundos e somar body_bytes_sent por intervalo. Exiba os resultados em um gráfico de linhas, defina o eixo x como time e o eixo y como body-sent. Para mais informações, consulte Configure the x-axis and the y-axis of a line chart.
* |
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 solicitações com erro no Tomcat
Para ver qual fração de todas as solicitações são erros, calcule a contagem de erros e a contagem total em uma subconsulta usando CASE WHEN status >= 400 e, em seguida, divida e arredonde para duas casas decimais na consulta externa. Exiba os resultados em um gráfico de velocímetro. Para mais informações, consulte Configure a dial.
* |
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
)