Example of Using Bucket Log Analysis

The following example queries the top 10 most frequently accessed objects by request count in the lts-bestpractice bucket. You can change the bucket name as needed.

* | SELECT
    url_decode(object) AS object_path,
    COUNT(*) AS request_count
FROM log
WHERE
    bucket = 'lts-bestpractice'
    AND object IS NOT NULL
    AND object != ''
    AND object != '-'
GROUP BY object_path
ORDER BY request_count DESC
LIMIT 10

Example query result:

The following example identifies the top 10 objects by GET traffic volume and their client IP addresses in the lts-bestpractice bucket. You can change the bucket name as needed. To query PUT traffic, change the value of http_method to PUT.

* | SELECT
    url_decode(object) AS object_path,
    client_ip AS client_IP,
    SUM(content_length_out) AS total_traffic_bytes
FROM log
WHERE bucket = 'lts-bestpractice'
  AND http_method = 'GET' 
  AND object IS NOT NULL AND object != '' AND object != '-'
GROUP BY object_path, client_ip
ORDER BY total_traffic_bytes DESC
LIMIT 10

Example query result:

The following example identifies the top 10 objects by GET traffic volume for each hour in the lts-bestpractice bucket. You can change the bucket name as needed. To query PUT traffic, change the value of http_method to PUT.

* | SELECT
    date_format(from_unixtime(hourly_timestamp / 1000), '%Y-%m-%d %H:00') AS hourly,
    object_path, client_IP, bandwidth_byte
FROM (
    SELECT
        (__time__ - __time__ % 3600000) AS hourly_timestamp,
        url_decode(object) AS object_path,
        client_ip AS client_IP,
        ROUND(SUM(content_length_out), 2) AS bandwidth_byte,
        ROW_NUMBER() OVER (PARTITION BY (__time__ - __time__ % 3600000) ORDER BY SUM(content_length_out) DESC) AS rn
    FROM log
    WHERE bucket = 'lts-bestpractice'
      AND http_method = 'GET'
      AND object IS NOT NULL AND object != '' AND object != '-'
    GROUP BY hourly_timestamp, object_path, client_ip
) t
WHERE rn <= 10
ORDER BY hourly_timestamp, rn

Example query result:

The following example categorizes objects in the lts-bestpractice bucket by size (< 1 GB, 1 GB–2 GB, 2 GB–3 GB, and > 3 GB) and counts requests for each category. Adjust the bucket name as needed.

* | SELECT
    CASE
        WHEN object_size != '-' AND parse_long(object_size) < 1073741824 THEN '< 1GB'
        WHEN object_size != '-' AND parse_long(object_size) < 2147483648 THEN '1GB ~ 2GB'
        WHEN object_size != '-' AND parse_long(object_size) < 3221225472 THEN '2GB ~ 3GB'
        WHEN object_size != '-' AND parse_long(object_size) >= 3221225472 THEN '≥ 3GB'
        ELSE 'other/no_size'
    END AS size_range,
    COUNT(*) AS request_count
FROM log
WHERE
    bucket = 'lts-bestpractice'
    AND object IS NOT NULL
    AND object != ''
    AND object != '-'
GROUP BY size_range
ORDER BY request_count DESC

Example query result:

The following example identifies the top 10 most frequently accessed IP addresses by request count in the lts-bestpractice bucket. You can change the bucket name as needed.

* | SELECT
    client_ip AS client_IP,
    operation AS operation_type,
    COUNT(*) AS operation_count
FROM
    log
WHERE
    bucket = 'lts-bestpractice'
    AND client_ip IS NOT NULL
    AND client_ip != ''
    AND client_ip != '-'
GROUP BY
    client_ip,
    operation
ORDER BY
    operation_count DESC
LIMIT 10

Example query result:

The following example identifies the top 10 objects by traffic volume in the lts-bestpractice bucket. You can change the bucket name as needed.

* | SELECT
    client_ip AS client_IP,
    SUM(content_length_in) AS inbound_bytes,
    SUM(content_length_out) AS outbound_bytes,
    SUM(content_length_in) + SUM(content_length_out) AS total_traffic_bytes,
    ROUND((SUM(content_length_in) + SUM(content_length_out)), 2) AS bandwidth_byte
FROM log
WHERE bucket = 'lts-bestpractice'
  AND client_ip IS NOT NULL AND client_ip != '' AND client_ip != '-'
GROUP BY client_ip
ORDER BY total_traffic_bytes DESC
LIMIT 10

Example query result:

The following example identifies the top 10 most frequently used access keys by request count for the lts-bestpractice bucket. You can change the bucket name as needed.

* | SELECT
    access_key AS access_key,
    COUNT(*) AS access_count
FROM log
WHERE
    bucket = 'lts-bestpractice'
    AND access_key IS NOT NULL
    AND access_key != ''
    AND access_key != '-'
GROUP BY access_key
ORDER BY access_count DESC
LIMIT 10

Example query result:

The following example identifies the top 10 access keys by traffic volume in the lts-bestpractice bucket. You can change the bucket name and the start and end times of the log query as needed.

* | SELECT access_key AS access_key, SUM(content_length_in + content_length_out) AS traffic
FROM log
WHERE bucket = 'lts-bestpractice'
  AND access_key IS NOT NULL AND access_key != '' AND access_key != '-'
  AND date_parse(time, '%d/%b/%Y:%H:%i:%s') >= date_parse('2026-09-01 00:00:00', '%Y-%m-%d %H:%i:%s')
  AND date_parse(time, '%d/%b/%Y:%H:%i:%s') < date_parse('2026-09-30 00:00:00', '%Y-%m-%d %H:%i:%s')
GROUP BY access_key
ORDER BY traffic DESC
LIMIT 10

Example query result:

The following example identifies the top objects, object prefixes, and client IP addresses for a specific error code in the lts-bestpractice bucket, ranked by error count. For example, for error code 499, it identifies the objects, directories, and client IP addresses that trigger the most 499 errors. You can change the bucket name, error code, and the start and end times of the log query as needed.

* | SELECT * FROM (
    SELECT 'object' AS type, object_path, client_IP, error_count FROM (
        SELECT object_path, client_IP, error_count, row_number() OVER (PARTITION BY object_path ORDER BY error_count DESC) AS rn FROM (
            SELECT REPLACE(object, '%2F', '/') AS object_path, client_ip AS client_IP, COUNT(*) AS error_count
            FROM log
            WHERE bucket = 'lts-bestpractice'
              AND (http_status = 499 OR error_code = '499')
              AND object IS NOT NULL AND object != '' AND object != '-'
              AND date_parse(time, '%d/%b/%Y:%H:%i:%s') >= date_parse('2026-09-01 00:00:00', '%Y-%m-%d %H:%i:%s')
              AND date_parse(time, '%d/%b/%Y:%H:%i:%s') < date_parse('2026-09-30 00:00:00', '%Y-%m-%d %H:%i:%s')
            GROUP BY REPLACE(object, '%2F', '/'), client_ip
        ) a
    ) b WHERE rn = 1 ORDER BY error_count DESC LIMIT 10
) t1
UNION ALL
SELECT * FROM (
    SELECT 'prefix' AS type, object_prefix, client_IP, error_count FROM (
        SELECT object_prefix, client_IP, error_count, row_number() OVER (PARTITION BY object_prefix ORDER BY error_count DESC) AS rn FROM (
            SELECT regexp_extract(REPLACE(object, '%2F', '/'), '^([^/]+/[^/]+/[^/]+)/', 1) AS object_prefix,
                   client_ip AS client_IP, COUNT(*) AS error_count
            FROM log
            WHERE bucket = 'lts-bestpractice'
              AND (http_status = 499 OR error_code = '499')
              AND object IS NOT NULL AND object != '' AND object != '-'
              AND date_parse(time, '%d/%b/%Y:%H:%i:%s') >= date_parse('2026-09-01 00:00:00', '%Y-%m-%d %H:%i:%s')
              AND date_parse(time, '%d/%b/%Y:%H:%i:%s') < date_parse('2026-09-30 00:00:00', '%Y-%m-%d %H:%i:%s')
            GROUP BY regexp_extract(REPLACE(object, '%2F', '/'), '^([^/]+/[^/]+/[^/]+)/', 1), client_ip
        ) a
    ) b WHERE rn = 1 ORDER BY error_count DESC LIMIT 10
) t2

Example query result:

The following example queries access details for a specified access key (AK) in the lts-bestpractice bucket within a given time period, including access operations, client IP addresses, and request status codes. You can change the start and end times for the log query as needed.

* | SELECT operation AS operation, client_ip AS source_IP, http_status AS status_code, COUNT(*) AS count
FROM log
WHERE access_key = 'Your_AK'
  AND date_parse(time, '%d/%b/%Y:%H:%i:%s') >= date_parse('2026-09-01 00:00:00', '%Y-%m-%d %H:%i:%s')
  AND date_parse(time, '%d/%b/%Y:%H:%i:%s') < date_parse('2026-09-30 00:00:00', '%Y-%m-%d %H:%i:%s')
GROUP BY operation, client_ip, http_status
ORDER BY count DESC

Example query result:

The following example queries the average latency, p99/p95 latency, maximum latency, and minimum latency for the lts-bestpractice bucket. You can adjust the bucket name and the start and end times for the log query as needed.

* | SELECT bucket AS bucket_name,
       AVG(server_cost_time) AS average_latency_ms,
       approx_percentile(server_cost_time, 0.99) AS p99_latency_ms,
       approx_percentile(server_cost_time, 0.95) AS p95_latency_ms,
       MAX(server_cost_time) AS maximum_latency_ms,
       MIN(server_cost_time) AS minimum_latency_ms,
       COUNT(*) AS sample_count
FROM log
WHERE bucket = 'lts-bestpractice'
  AND server_cost_time IS NOT NULL AND server_cost_time >= 0
  AND date_parse(time, '%d/%b/%Y:%H:%i:%s') >= date_parse('2026-09-01 00:00:00', '%Y-%m-%d %H:%i:%s')
  AND date_parse(time, '%d/%b/%Y:%H:%i:%s') < date_parse('2026-09-30 00:00:00', '%Y-%m-%d %H:%i:%s')
GROUP BY bucket

Example query result:

The following example identifies slow upload and download requests that take more than 10 minutes for the lts-bestpractice bucket. You can change the threshold (for example, 10 minutes or 600,000 ms) for slow requests as needed, as well as the start and end times for the log query.

* | SELECT date_parse(time, '%d/%b/%Y:%H:%i:%s') AS request_time,
       bucket AS bucket_name,
       REPLACE(object, '%2F', '/') AS object,
       http_method AS request_method,
       client_ip AS source_IP,
       response_time AS total_duration_ms,
       server_cost_time AS server_duration_ms,
       content_length_out AS outbound_bytes,
       content_length_in AS inbound_bytes,
       http_status AS status_code,
       error_code AS error_code
FROM log
WHERE bucket = 'lts-bestpractice'
  AND (response_time > 600000 OR server_cost_time > 600000)
  AND date_parse(time, '%d/%b/%Y:%H:%i:%s') >= date_parse('2026-09-01 00:00:00', '%Y-%m-%d %H:%i:%s')
  AND date_parse(time, '%d/%b/%Y:%H:%i:%s') < date_parse('2026-09-30 00:00:00', '%Y-%m-%d %H:%i:%s')
ORDER BY total_duration_ms DESC

Example query result:

The following example identifies the top 10 objects and directories by traffic volume in the lts-bestpractice bucket. You can change the bucket name and the start and end times for the log query as needed.

* | SELECT * FROM (
    SELECT 'object' AS type, REPLACE(object, '%2F', '/') AS name,
           SUM(content_length_in + content_length_out) AS total_traffic_bytes,
           SUM(content_length_in) AS inbound_traffic_bytes,
           SUM(content_length_out) AS outbound_traffic_bytes
    FROM log
    WHERE bucket = 'lts-bestpractice'
      AND object IS NOT NULL AND object != '' AND object != '-'
      AND date_parse(time, '%d/%b/%Y:%H:%i:%s') >= date_parse('2026-09-01 00:00:00', '%Y-%m-%d %H:%i:%s')
      AND date_parse(time, '%d/%b/%Y:%H:%i:%s') < date_parse('2026-09-30 00:00:00', '%Y-%m-%d %H:%i:%s')
    GROUP BY REPLACE(object, '%2F', '/')
    ORDER BY total_traffic_bytes DESC
    LIMIT 10
) t1
UNION ALL
SELECT * FROM (
    SELECT 'directory' AS type,
           COALESCE(regexp_extract(REPLACE(object, '%2F', '/'), '^(.*)/[^/]*$', 1), '/') AS name,
           SUM(content_length_in + content_length_out) AS total_traffic_bytes,
           SUM(content_length_in) AS inbound_traffic_bytes,
           SUM(content_length_out) AS outbound_traffic_bytes
    FROM log
    WHERE bucket = 'lts-bestpractice'
      AND object IS NOT NULL AND object != '' AND object != '-'
      AND date_parse(time, '%d/%b/%Y:%H:%i:%s') >= date_parse('2026-09-01 00:00:00', '%Y-%m-%d %H:%i:%s')
      AND date_parse(time, '%d/%b/%Y:%H:%i:%s') < date_parse('2026-09-30 00:00:00', '%Y-%m-%d %H:%i:%s')
    GROUP BY COALESCE(regexp_extract(REPLACE(object, '%2F', '/'), '^(.*)/[^/]*$', 1), '/')
    ORDER BY total_traffic_bytes DESC
    LIMIT 10
) t2

Example query result:

The following example queries the distribution of access methods for objects with the folder1 prefix in the lts-bestpractice bucket. You can change the bucket name, object prefix, and start and end times as needed.

* | SELECT method, request_count,
       ROUND(request_count * 100.0 / SUM(request_count) OVER (), 2) AS percentage
FROM (
    SELECT CASE
               WHEN operation LIKE 'GET.BUCKET.%' THEN 'LIST'
               WHEN http_method = 'GET'     THEN 'GET'
               WHEN http_method = 'PUT'     THEN 'PUT'
               WHEN http_method = 'POST'    THEN 'POST'
               WHEN http_method = 'DELETE'  THEN 'DELETE'
               WHEN http_method = 'HEAD'    THEN 'HEAD'
               WHEN http_method = 'OPTIONS' THEN 'OPTIONS'
               ELSE 'other'
           END AS method,
           COUNT(*) AS request_count
    FROM log
    WHERE bucket = 'lts-bestpractice'
      AND date_parse(time, '%d/%b/%Y:%H:%i:%s') >= date_parse('2026-09-01 00:00:00', '%Y-%m-%d %H:%i:%s')
      AND date_parse(time, '%d/%b/%Y:%H:%i:%s') <  date_parse('2026-09-30 00:00:00', '%Y-%m-%d %H:%i:%s')
      AND REPLACE(object, '%2F', '/') LIKE 'folder1%'
    GROUP BY CASE
               WHEN operation LIKE 'GET.BUCKET.%' THEN 'LIST'
               WHEN http_method = 'GET'     THEN 'GET'
               WHEN http_method = 'PUT'     THEN 'PUT'
               WHEN http_method = 'POST'    THEN 'POST'
               WHEN http_method = 'DELETE'  THEN 'DELETE'
               WHEN http_method = 'HEAD'    THEN 'HEAD'
               WHEN http_method = 'OPTIONS' THEN 'OPTIONS'
               ELSE 'other'
           END
) t
ORDER BY request_count DESC

Example query result:

Path-style requests place the bucket name in the URL path (https://obs.regionID.myhuaweicloud.com/${bucketname}/query) rather than in the access domain. To meet security and compliance requirements, Huawei Cloud prohibits accessing OBS buckets using the path-style method. The following example helps identify recent path-style access requests to the lts-bestpractice bucket, based on which you can optimize your application code. You can change the bucket name as needed.

* | SELECT date_parse(time, '%d/%b/%Y:%H:%i:%s') AS request_time,
       bucket AS bucket_name,
       client_ip AS source_IP,
       object AS object,
       user_agent AS user_agent,
       requester_id AS user_id,
       http_method, http_status, error_code
FROM log
WHERE bucket = 'lts-bestpractice'
  AND SPLIT_PART(host, '.', 1) <> bucket
  AND date_parse(time, '%d/%b/%Y:%H:%i:%s') >= date_parse('2026-09-01 00:00:00', '%Y-%m-%d %H:%i:%s')
  AND date_parse(time, '%d/%b/%Y:%H:%i:%s') <  date_parse('2026-09-30 00:00:00', '%Y-%m-%d %H:%i:%s')
ORDER BY request_time
LIMIT 10