Analyze DCDN logs

Procedure

This example assumes that DCDN logs are stored in the China (Shanghai) region where DCDN offline log delivery is enabled.

1. Log on to the DLA console.
2. Select China (Shanghai) from the region drop-down list in the upper-left corner.
3. In the left-side navigation pane, choose Serverless Presto > Execute.
4. On the Execute page, execute an SQL statement to query and analyze DCDN log data. Examples:
   • Top 10 URL path access errors

     // Query the HTTP status codes that are greater than 400. Obtain access errors that correspond to each URL path based on the URL paths contained in the requests, excluding aggregated URL paths. Then, sort the errors in descending order of the error quantity to obtain top 10 errors.
     SELECT urlPath,count(*) as c from alicdn_offline_log.alicdn_offline_log_dcdn WHERE httpcode > 400 GROUP BY urlPath ORDER BY  c DESC LIMIT 10;

   • Access source percentage

     // Use the domain name filter condition to filter out the logs for which the access source is empty. Aggregate the number of requests from each access source. Then, calculate the percentage of requests for a single domain to all requests. Retain two decimal places for the percentage.
     select
       refer_domain as "Access source",
       c as "Requests",
       round(c * 100.0 /(sum(c) over()), 2) as "Percentage (%)"
     from
       (
         select
           domain as refer_domain,
           count(*) as c
         from
           alicdn_offline_log.alicdn_offline_log_dcdn
         group by
           refer_domain
         order by
           c desc
         limit
           100
       )
       where refer_domain ! = "";

   • Top domains based on access times

     // Use GROUP BY to group access times by domain name, use the COUNT (*) function to calculate the number of times of accesses to each domain, use ORDER BY to sort the domains based on the access times, and use DESC to sort the domains in descending order. In addition, use Limit 100 to return the top 100 domains, use a pie chart for graphical presentation, use return_code for classification, and set the value column to c.
     select
       Domain,
       count(*) as c
     FROM
       alicdn_offline_log.alicdn_offline_log_dcdn
     group by
       Domain
     order by
       c desc
     limit
       100

   • Distribution of offline logs of different data types

     // Use the SUM function to calculate the total value of respsize, and use the calculated value as sum_res. Use GROUP BY to group offline logs of different data types by content_type, use ORDER BY to sort offline logs of different data types by sum_res. Then, obtain top 10 data types, use a pie chart for graphical presentation, use content_type for classification, and set the value column to sum_res.
     select
       contenttype,
       sum(respsize) as sum_res
     FROM alicdn_offline_log.alicdn_offline_log_dcdn
     group by
       contenttype
     order by
       sum_res desc
     limit
       10;