Ready-to-use SQL queries for common log analysis scenarios — alerting, traffic monitoring, latency analysis, and Tomcat web service observability.
Trigger an alert when the error rate exceeds 40% in the past 5 minutes
To alert on a spike in HTTP 500 errors, calculate the error rate per topic over 5-minute windows using max_by to find the window with the highest error count, then filter topics where that window accounts for more than 40% of total errors. The HAVING clause applies both conditions: error rate above 40% and a minimum of 500 total errors to exclude false positives on low-traffic topics.
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 100Collect traffic statistics and configure alerts
To monitor inbound traffic per minute and alert when it drops below a threshold, aggregate traffic by minute and normalize it to a per-minute rate. The divisor greatest(max(__time__) - min(__time__),1) handles sub-minute windows at query boundaries — greatest ensures the denominator is at least 1, preventing division by zero when all events fall within the same second.
* | SELECT SUM(inflow) / greatest(max(__time__) - min(__time__),1) as inflow_per_minute, date_trunc('minute',__time__) as minute group by minuteCalculate the average latency by data size
To understand how data size affects latency, bucket requests into five size ranges using CASE WHEN and calculate the average latency in each bucket. This reveals whether large payloads are the source of latency spikes.
* | 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 osReturn the percentages of different results
To see each department's share of the total count, use a subquery to calculate per-department counts and a window function to get the grand total. The expression sum(c) over() computes the sum across all rows without collapsing them, so you can divide each row's count by the total in a single pass.
* | select department, c*1.0/ sum(c) over () from(select count(1) as c, department from log group by department)Count the number of URLs that meet the query condition
To count login and registration requests per minute without complex branching, use COUNT_IF with a LIKE pattern for each URL type. COUNT_IF is more concise than the equivalent CASE WHEN expression and easier to extend when you need to track additional URL patterns.
* | 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 100General aggregate analysis
Query the global distribution of clients by PV
To see which countries your clients come from and how much traffic each generates, use the ip_to_country function to resolve client IP addresses to countries, then group by country and count page views (PVs). Display the results on a world map — see 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
500Query the category and PV trend of request methods
To track how different HTTP request methods trend over time, truncate timestamps to the minute using date_format(date_trunc('minute', ...)), then group by both time and request_method to get per-method PV counts. Display the results on a line chart with the x-axis set to t, the y-axis set to pv, and the aggregate column set to request_method. For more information, see 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
10000Query the distribution of requests by user agent and PV
To break down traffic by user agent — including request volume, traffic size, and status code distribution — group by http_user_agent and compute: total PVs, request and response traffic in MB (rounded to two decimal places), and the percentage of responses in each status code range (2xx, 3xx, 4xx, 5xx) using CASE WHEN expressions. Display the results in a table. For more information, see 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
100Query the daily consumption and trend forecast for the current month
To visualize actual daily spending alongside a forecast for the rest of the month, deduplicate billing records by RecordID, aggregate daily totals, and pass the result to sls_inner_ts_regression. This function takes the timestamp, the daily total, a label array, the forecast interval in seconds (86400 for daily), and the number of forecast points (60), then returns both actual and predicted values. Where res.real is NaN, the row is a forecast point — the CASE WHEN expression surfaces forecast values only for future dates. Display on a line chart with the x-axis set to time and two y-axes for actual and forecast consumption. For more information, see 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
1000Query the consumption percentage of each service for the current month
To see which services account for the largest share of your bill, aggregate spending by ProductName, rank services by total expense using the row_number window function, then collapse services ranked 7th and below (or with zero or negative charges) into an "Other" category. Display the results on a donut chart. For more information, see 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
1000Compare yesterday's expense against the same day last month
To compare yesterday's spending against the same day last month, use the compare function with a 604800-second (7-day) offset. COALESCE ensures that days with no charges return 0 rather than NULL. The output array diff contains three values: diff[1] is yesterday's expense, diff[2] is the same day last month, and diff[3] is the ratio. Use round to round the inner expense value to three decimal places, then format the outer diff values to two decimal places, and multiply diff[3] by 100 minus 100 to express the ratio as a percentage change. Display on a trend chart — see 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
)
)Tomcat web service analysis
Query the trend of Tomcat request status
To see how Tomcat request status codes change over time, use date_trunc to truncate timestamps to the minute and date_format to format the time as hours and minutes, then group by both time and status to count requests per status code per minute. Display on a flow chart with the x-axis set to time, the y-axis set to count, and the aggregate column set to status. For more information, see 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
1000Query the distribution of PVs and UVs for Tomcat access over time
To track both page views (PVs) and unique visitors (UVs) on the same timeline, use time_series to group events into 2-minute buckets and approx_distinct to count unique remote_addr values. approx_distinct uses HyperLogLog for efficient approximate deduplication — accurate enough for monitoring without the cost of exact deduplication. Display on a multi-axis line chart with the x-axis set to time and two y-axes for uv and pv. For more information, see 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
1000Query the number of Tomcat error requests and compare with the previous hour
To compare the current error count against one hour ago, use the compare function with a 3600-second offset. The function returns an array: c1 is the current error count, c2 is the count from 3,600 seconds ago, and c3 is the percentage change (c1/c2 * 100 - 100). Display on a donut chart using c1 as the display value and c3 as the comparison value. For more information, see 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
)
)Query the top 10 URIs in Tomcat requests
To find the most-requested URIs, group by request_uri, count PVs per URI, and return the top 10 in descending order. Display on a bar gauge with the x-axis set to page and the y-axis set to pv. For more information, see Configure a basic bar gauge.
* |
SELECT
request_uri as page,
COUNT(*) as pv
GROUP by
page
ORDER by
pv DESC
LIMIT
10Query the types and distribution of Tomcat clients
To understand what clients are hitting your Tomcat server, group by user_agent and count requests per type. Display on a donut chart with the type set to user_agent and the value column set to c. For more information, see Configure a donut chart.
* |
SELECT
user_agent,
COUNT(*) AS c
GROUP BY
user_agent
ORDER BY
c DESCCollect statistics on the outbound Tomcat traffic
To monitor outbound Tomcat traffic over time, use time_series to bucket events into 10-second intervals and sum body_bytes_sent per bucket. Display on a line chart with the x-axis set to time and the y-axis set to body-sent. For more information, see 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
1000Query the percentage of error Tomcat requests
To see what fraction of all requests are errors, compute the error count and total count in a subquery using CASE WHEN status >= 400, then divide and round to two decimal places in the outer query. Display on a dial. For more information, see 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
)