SQL Query Example

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

1. Log in to the LTS console, and click a log stream on the Log Management page to go to the log details page.
2. View logs on the Raw Logs tab page. The system obtains ELB raw logs.
3. Click in the upper right corner. On the page that is displayed, select Cloud Structuring Parsing.
4. Select Structuring Template, and configure log structuring based on the ELB template. For other logs, you can select other structuring templates.
5. On the Visualization tab page, enter an SQL statement in the search box.

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