SQL Query Example

Updated on 2025-02-12 GMT+08:00

View PDF

This section describes how to query ELB raw logs in LTS.

Log in to the LTS console.
Choose Log Management in the navigation pane, and click a log group or stream to access the log details page.
View logs on the Log Search tab page. The system obtains ELB raw logs.
Click in the upper right corner. On the page that is displayed, select Cloud Structuring Parsing.
Select Structuring Template, and configure log structuring based on the ELB template. For other logs, you can select other structuring templates.
On the Visualization tab page, enter a SQL statement in the search box.

Display of Query Results

Table

The following statement queries the host, the number of logs of each request_uri, the request body size in MB, and the ratios of 2xx, 3xx, 4xx, and 5xx responses. Then the results are displayed in a table, and sorted in descending order by the number of logs.

SELECT "router_request_uri" as "request_uri", "host", COUNT(*) as pv,
round(sum(body_bytes_sent) / 1024.0 , 5) as "body_bytes_sent(MB)",
round(sum(case when status >= 200 and status < 300 then 1 else 0 end ) * 100.0 / COUNT(1), 6) as "2xx ratio(%)",
round(sum(case when status >= 300 and status < 400 then 1 else 0 end ) * 100.0 / count(1), 6) as "3xx ratio(%)",
round(sum(case when status >= 400 and status < 500 then 1 else 0 end ) * 100.0 / count(1), 6) as "4xx ratio(%)",
round(sum(case when status >= 500 and status < 600 then 1 else 0 end ) * 100.0 / count(1), 6) as "5xx ratio(%)"
GROUP BY "host", "router_request_uri"
ORDER BY pv DESC
LIMIT 100

Bar chart

The following statement queries the number of requests for each request URL and displays the results in a bar chart, with request_uri set as the X axis and pv as the Y axis.

SELECT "router_request_uri" as "request_uri", "host", COUNT(*) as pv,
round(sum(body_bytes_sent) / 1024.0 , 5) as "body_bytes_sent(MB)",
round(sum(case when status >= 200 and status < 300 then 1 else 0 end ) * 100.0 / COUNT(1), 6) as "2xx ratio(%)",
round(sum(case when status >= 300 and status < 400 then 1 else 0 end ) * 100.0 / count(1), 6) as "3xx ratio(%)",
round(sum(case when status >= 400 and status < 500 then 1 else 0 end ) * 100.0 / count(1), 6) as "4xx ratio(%)",
round(sum(case when status >= 500 and status < 600 then 1 else 0 end ) * 100.0 / count(1), 6) as "5xx ratio(%)"
GROUP BY "host", "router_request_uri"
ORDER BY pv DESC
LIMIT 100

Line chart

The following statement displays a line chart based on the query result, with _time_ set as the X axis and QPS as the Y axis. The chart shows the queries per second (QPS) change at an interval of 5s in the query period.

select TIME_FORMAT(TIME_CEIL(TIME_PARSE(SUBSTRING(time_iso8601, 2, 25) ,'yyyy-MM-dd''T''HH:mm:ssZZ'),'PT5S'),'yyyy-MM-dd HH:mm:ss','+08:00') AS _time_ , COUNT(*) as QPS from log group by _time_

Pie chart

The following statement queries the percentage of different values of status in the query period, and displays the results in a bar chart, with Category set to status and Value set to rm.

SELECT status, COUNT(1) AS rm GROUP BY status

Number chart

The following statement queries the total number of normal requests in the last hour and displays the results in a number chart.

SELECT count(*) AS normalRequest WHERE status = 200

Parent topic: SQL Analysis Syntax

Previous topic: SQL JOIN Syntax

Next topic: Log Visualization

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel