【玩转 EdgeOne】 使用EdgeOne实时日志+cls 自动生成网站访客信息

* | SELECT   
    SUM(EdgeResponseBytes)/300.00*8 AS "实时带宽",   
    HISTOGRAM(CAST(__TIMESTAMP__ AS TIMESTAMP), INTERVAL 5 MINUTE) AS "时间"   
GROUP BY "时间"   
ORDER BY "时间" DESC   
LIMIT 10000

--LIMIT 10000，获得最近的10000个5分钟间隔的数据点,300.00然后乘以8。
--将字节转换为比特（8比特=1字节），并确保得到的结果是以Kbps（每秒千比特）为单位的
--"实时"的定义：那么你最多只能得到5分钟前的"实时"数据更新一次

* | SELECT    
    EdgeServerID,    
    MAX(EdgeResponseBytes * 1.0) AS "峰值宽带"    
GROUP BY    
    EdgeServerID    
ORDER BY    
    "峰值宽带"  
    -- 找出每个EdgeServerID的EdgeResponseBytes的最大值，这可以被视为"峰值宽带"

* | SELECT   
    SUM(EdgeResponseBytes * 1.0) AS "总流量"
      -- 计算EdgeResponseBytes的总和，这可以被视为"总流量"。
      

* | SELECT   
  ROUND(  
    SUM(CASE WHEN EdgeCacheStatus LIKE '%hit%' THEN 1.00 ELSE 0.00 END) /   
    CAST(COUNT(*) AS DOUBLE) * 100,  
    1  
  ) AS "缓存命中率"  
WHERE   
  (RequestUrl LIKE '/A%' OR RequestUrl LIKE '/B%' OR RequestUrl LIKE '/%.js OR RequestUrl LIKE '/%.css')  
  AND EdgeResponseStatusCode < 400
  -- 为了计算特定请求URL模式（以/A开始，以/B开始，或任何.js文件，还有任何.css文件）的缓存命中率
  -- 同时只考虑那些返回的HTTP状态码小于400的请求

* | SELECT   
    COUNT(*) AS cnt,  
    "RequestProtocol",  
    HISTOGRAM(__TIMESTAMP__, INTERVAL ${__interval}) AS analytic_time  
WHERE   
    "RequestProtocol" IN (  
        SELECT "RequestProtocol"   
        GROUP BY "RequestProtocol"   
        ORDER BY COUNT(*) DESC   
        LIMIT 5  
    )  
GROUP BY   
    analytic_time, "RequestProtocol"  

* | SELECT   
    CASE   
     WHEN REGEXP_LIKE(RequestSSLProtocol, 'TLS1.0') THEN 'TLS1.0' 
     WHEN REGEXP_LIKE(RequestSSLProtocol, 'TLS1.1') THEN 'TLS1.1'
     WHEN REGEXP_LIKE(RequestSSLProtocol, 'TLS1.2') THEN 'TLS1.2'
     WHEN REGEXP_LIKE(RequestSSLProtocol, 'TLS1.3') THEN 'TLS1.3'
        ELSE '其他'  
    END AS "TLS握手情况",  
    SUM(EdgeResponseBytes) AS "总次数"  
GROUP BY "TLS握手情况"  
ORDER BY "总次数" DESC  
LIMIT 20
 -- 获取前指定TLS请求协议

* | SELECT   
    COUNT(*) AS cnt,  
    "RequestProtocol",  
    HISTOGRAM(__TIMESTAMP__, INTERVAL ${__interval}) AS analytic_time  
WHERE   
    "RequestProtocol" IN (  
        SELECT "RequestProtocol"   
        GROUP BY "RequestProtocol"   
        ORDER BY COUNT(*) DESC   
        LIMIT 5  
    )  
GROUP BY   
    analytic_time, "RequestProtocol"  
     -- 获取前指定TLS请求协议

* | SELECT   
    CASE   
     WHEN REGEXP_LIKE(RequestProtocol, 'HTTP/1.0') THEN 'HTTP/1.0' 
     WHEN REGEXP_LIKE(RequestProtocol, 'HTTP/1.1') THEN 'HTTP/1.0'
     WHEN REGEXP_LIKE(RequestProtocol, 'HTTP/2.0') THEN 'HTTP/1.0'
     WHEN REGEXP_LIKE(RequestProtocol, 'HTTP/3.0') THEN 'HTTP/1.0'
        ELSE '其他'  
    END AS "HTTP协议",  
    SUM(EdgeResponseBytes) AS "总次数"  
GROUP BY "HTTP协议"  
ORDER BY "总次数" DESC  
LIMIT 20
 -- 获取前指定TLS请求协议

* | SELECT   
  REGEXP_REPLACE("RequestReferer", '\?.*$', '') AS "RequestReferer",   
  COUNT(*) AS requests   
WHERE   
  "RequestReferer" IS NOT NULL   
GROUP BY   
  "RequestReferer"   
ORDER BY   
  requests DESC   
  -- 获取Referer 并且忽略?以后参数

* | SELECT     
    CASE     
        WHEN strpos(lower(RequestUA), 'edge') > 0 THEN 'Microsoft Edge'    
        WHEN strpos(lower(RequestUA), 'firefox') > 0 THEN 'Firefox'    
        WHEN strpos(lower(RequestUA), 'ie') > 0 THEN 'Internet Explorer'    
        WHEN strpos(lower(RequestUA), 'opera') > 0 THEN 'Opera'    
        WHEN strpos(lower(RequestUA), 'brave') > 0 THEN 'Brave'    
        WHEN strpos(lower(RequestUA), 'vivaldi') > 0 THEN 'Vivaldi'    
        WHEN strpos(lower(RequestUA), 'uc') > 0 THEN 'UC Browser' 
         WHEN strpos(lower(RequestUA), 'via') > 0 THEN 'via Browser' 
        WHEN strpos(lower(RequestUA), 'mqqbrowser') > 0 THEN 'QQ Browser'    
        WHEN strpos(lower(RequestUA), 'maxthon') > 0 THEN 'Maxthon'    
        WHEN strpos(lower(RequestUA), '360SE') > 0 THEN '360 Browser'    
        WHEN strpos(lower(RequestUA), 'torb') > 0 THEN 'Tor Browser'    
        WHEN strpos(lower(RequestUA), 'waterfox') > 0 THEN 'Waterfox'    
        WHEN strpos(lower(RequestUA), 'palemoon') > 0 THEN 'Pale Moon'    
        WHEN strpos(lower(RequestUA), 'seamonkey') > 0 THEN 'SeaMonkey'    
        WHEN strpos(lower(RequestUA), 'k-melon') > 0 THEN 'K-Melon'    
        WHEN strpos(lower(RequestUA), 'lunascape') > 0 THEN 'Lunascape'    
        WHEN strpos(lower(RequestUA), 'comodo dragon') > 0 THEN 'Comodo Dragon'    
        WHEN strpos(lower(RequestUA), 'srware iron') > 0 THEN 'SRWare Iron'    
        WHEN strpos(lower(RequestUA), 'blackberry') > 0 THEN 'BlackBerry Browser'    
        WHEN strpos(lower(RequestUA), 'baidu') > 0 THEN 'Baidu Browser'    
        WHEN strpos(lower(RequestUA), 'sogou') > 0 THEN 'Sogou Explorer'    
        WHEN strpos(lower(RequestUA), 'tencent') > 0 THEN 'Tencent Traveler'    
        WHEN strpos(lower(RequestUA), 'mibrowser') > 0 THEN 'Mi Browser'    
        WHEN strpos(lower(RequestUA), 'huawei') > 0 THEN 'Huawei Browser'     
        WHEN strpos(lower(RequestUA), 'micromessenger') > 0 THEN 'WeChat Browser'    
        WHEN strpos(lower(RequestUA), 'wxwork') > 0 THEN 'WeCom Browser'    
        WHEN strpos(lower(RequestUA), 'vivo') > 0 THEN 'Vivo Browser'   
        WHEN strpos(lower(RequestUA), 'oppo') > 0 THEN 'OPPO Browser'   
         WHEN strpos(lower(RequestUA), 'chromium') > 0 THEN 'Chromium'  
        WHEN strpos(lower(RequestUA), 'chrome') > 0 THEN 'Chrome'   
        ELSE '未知'    
    END AS Browser,    
    COUNT(*) AS Visits    
GROUP BY     
    Browser    
ORDER BY     
    Visits DESC
    -- 列出浏览器UA关键词

* | SELECT   
    CASE   
        WHEN REGEXP_LIKE(RequestUA, 'Windows NT 10.1') THEN 'Windows 11'  
        WHEN REGEXP_LIKE(RequestUA, 'Windows NT 10.0') THEN 'Windows 10'  
        WHEN REGEXP_LIKE(RequestUA, 'Windows NT 6.3') THEN 'Windows 8.1'  
        WHEN REGEXP_LIKE(RequestUA, 'Windows NT 6.2') THEN 'Windows 8'  
        WHEN REGEXP_LIKE(RequestUA, 'Windows NT 6.1') THEN 'Windows 7'  
        WHEN REGEXP_LIKE(RequestUA, 'Windows NT 5.1') THEN 'Windows XP'
        WHEN REGEXP_LIKE(RequestUA, 'idm') THEN 'IDM'
        WHEN REGEXP_LIKE(RequestUA,'iPhone|iPad|iPod') THEN  'IOS'
        WHEN strpos(lower(RequestUA), 'chromiumos') > 0 THEN 'ChromiumOS'  
        WHEN strpos(lower(RequestUA), 'netbsd') > 0 THEN 'NetBSD'  
        WHEN REGEXP_LIKE(RequestUA,'Mac') THEN  'MAC OS' 
        WHEN REGEXP_LIKE(RequestUA,' Ubuntu') THEN  ' Ubuntu' 
        WHEN REGEXP_LIKE(RequestUA,' python') THEN  ' python'
        WHEN REGEXP_LIKE(RequestUA,'bot') THEN  'bot' 
        WHEN REGEXP_LIKE(RequestUA,'curl') THEN  'curl' 
        WHEN REGEXP_LIKE(RequestUA,'CFNetwork') THEN  'CFNetwork' 
        WHEN REGEXP_LIKE(RequestUA,'BitComet') THEN  'BitComet' 
        WHEN REGEXP_LIKE(RequestUA,'RSS') THEN  'RSS' 
        WHEN REGEXP_LIKE(RequestUA,'Clash') THEN  'Clash' 
        WHEN REGEXP_LIKE(RequestUA, 'Android 10; HarmonyOS')  THEN 'Harmony OS 2' 
        WHEN REGEXP_LIKE(RequestUA, 'Android 11; HarmonyOS')  THEN 'Harmony OS 3'  
        WHEN REGEXP_LIKE(RequestUA, 'Android 12; HarmonyOS')  THEN 'Harmony OS 4'  
        WHEN REGEXP_LIKE(RequestUA, 'Android 15') THEN 'Android 15'
        WHEN REGEXP_LIKE(RequestUA, 'Android 14') THEN 'Android 14'
        WHEN REGEXP_LIKE(RequestUA, 'Android 13') THEN 'Android 13'
        WHEN REGEXP_LIKE(RequestUA, 'Android 12') THEN 'Android 12'
        WHEN REGEXP_LIKE(RequestUA, 'Android 11') THEN 'Android 11'
        WHEN REGEXP_LIKE(RequestUA, 'Android 10') THEN 'Android 10'
        WHEN REGEXP_LIKE(RequestUA, 'Android 9') THEN 'Android 9'
        WHEN REGEXP_LIKE(RequestUA, 'Android 8') THEN 'Android 8'
        WHEN REGEXP_LIKE(RequestUA, 'Android 7') THEN 'Android 7'
        WHEN REGEXP_LIKE(RequestUA, 'Android 6') THEN 'Android 6'
        WHEN REGEXP_LIKE(RequestUA, 'Android 5') THEN 'Android 5'
        WHEN REGEXP_LIKE(RequestUA, 'Android 4') THEN 'Android 4'
        WHEN REGEXP_LIKE(RequestUA, 'BlackBerry') THEN 'BlackBerry'  
        WHEN REGEXP_LIKE(RequestUA, 'Symbian') THEN 'Symbian'  
        WHEN REGEXP_LIKE(RequestUA, 'webOS') THEN 'webOS'  
        WHEN REGEXP_LIKE(RequestUA, 'Meego') THEN 'Meego'  
        WHEN REGEXP_LIKE(RequestUA, 'Bada') THEN 'Bada'  
        WHEN REGEXP_LIKE(RequestUA, 'Tizen') THEN 'Tizen'  
        WHEN REGEXP_LIKE(RequestUA, 'Sailfish OS') THEN 'Sailfish OS'  
        WHEN REGEXP_LIKE(RequestUA, 'tv') THEN 'tv'  
        WHEN REGEXP_LIKE(RequestUA, 'Apache') THEN 'Apache' 
        WHEN REGEXP_LIKE(RequestUA, 'aria2') THEN 'aria2' 
        WHEN REGEXP_LIKE(RequestUA, 'ZhihuHybrid') THEN 'Zhihu'
        WHEN REGEXP_LIKE(RequestUA, 'Wget') THEN 'Wget'
        WHEN REGEXP_LIKE(RequestUA, 'qBittorrent') THEN 'qBittorrent'
        WHEN REGEXP_LIKE(RequestUA, 'Dalvik') THEN 'Dalvik'    
        WHEN REGEXP_LIKE(RequestUA, 'Linux') THEN 'Linux'    
        ELSE '未知'  
    END AS OperatingSystem,  
    COUNT(*) AS Visits  
GROUP BY   
    OperatingSystem  
ORDER BY   
    Visits DESC
    -- 变量报错 先这样吧 获取用户具体操作系统或环境

* | SELECT   
    ip_to_province("ClientIP") AS "省份",   
    SUM(EdgeResponseBytes) AS "总流量"   
GROUP BY "省份"   
HAVING "省份" <> '-1'   
ORDER BY "总流量" DESC   
LIMIT 20
-- 获取前20名流量大省消耗了多少流NULL为ipv6 

* | SELECT   
    ip_to_city("ClientIP") AS "城市",   
    SUM(EdgeResponseBytes) AS "总流量"   
GROUP BY "城市"   
HAVING "城市" <> '-1'   
ORDER BY "总流量" DESC   
LIMIT 20
-- 获取前20名流量城市消耗了多少流NULL为ipv6 

* |  SELECT   
    CASE   
        WHEN REGEXP_LIKE(RequestUA, 'Googlebot') THEN 'Google'  
        WHEN REGEXP_LIKE(RequestUA, 'Bingbot') THEN 'Bing'  
        WHEN REGEXP_LIKE(RequestUA, 'Yahoo') THEN 'Yahoo'  
        WHEN REGEXP_LIKE(RequestUA, 'Baiduspider') THEN 'Baidu'  
        WHEN REGEXP_LIKE(RequestUA, 'SogouSpider') THEN 'Sogou'  
        WHEN REGEXP_LIKE(RequestUA, '360Spider') THEN '360'  
        WHEN REGEXP_LIKE(RequestUA, 'Bytespider') THEN 'Toutiao'  
    END AS "搜索引擎",  
    COUNT(*) AS "请求次数"  
WHERE REGEXP_LIKE(RequestUA, 'Googlebot') OR  
      REGEXP_LIKE(RequestUA, 'Bingbot') OR  
      REGEXP_LIKE(RequestUA, 'Yahoo') OR  
      REGEXP_LIKE(RequestUA, 'Baiduspider') OR  
      REGEXP_LIKE(RequestUA, 'SogouSpider') OR  
      REGEXP_LIKE(RequestUA, '360Spider') OR  
      REGEXP_LIKE(RequestUA, 'Bytespider')  
GROUP BY "搜索引擎"  
ORDER BY "请求次数" DESC  
LIMIT 20
-- 搜索引擎来路的请求 ,也有可能是蜘蛛爬取，虽然说我学过SEO但是我不太关心SEO，，，

* | SELECT   
    CASE   
        WHEN RequestUA LIKE '%://%' THEN REGEXP_EXTRACT(RequestUA, 'https?://([^/]+)', 1)   
        ELSE '直接访问'   
    END AS "来路域名",  
    COUNT(*) AS "访问次数"  
GROUP BY "来路域名"  
ORDER BY "访问次数" DESC  
LIMIT 20
--  检测RequestUA字段的值是否包含'://'，这是一个简单的判断URL是否存在的方法。如果包含'://'
-- 它将使用REGEXP_EXTRACT函数提取域名。
--如果不包含'://'，它将返回'直接访问'。这样，所有不包含链接的RequestUA记录都将被归类为'直接访问'。

https://img.cuteapi.com/cls/json/Edgeone_senior.json