anduin / clickhouse.json

{ "params": { "rounding": "60", "seconds": "3200" }, "queries": [ { "title": "Request rate", "query": "SELECT\n toStartOfInterval(ts, INTERVAL {rounding:UInt32} SECOND)::Int64 AS t,\n count() AS requests\nFROM logs.caddy_requests\nWHERE ts >= now() - {seconds:UInt32}\nGROUP BY t\nORDER BY t\nWITH FILL STEP {rounding:UInt32}\n" }, { "title": "Unique Request IP", "query": "SELECT\n toStartOfInterval(ts, INTERVAL {rounding:UInt32} SECOND)::Int64 AS t,\n uniqExact(remote_ip) AS unique_ips\nFROM logs.caddy_requests\nWHERE ts >= now() - {seconds:UInt32}\nGROUP BY t\nORDER BY t\nWITH FILL STEP {rounding:UInt32}\n" }, { "title": "Average request bytes", "query": "SELECT\n toStartOfInterval(ts, INTERVAL {rounding:UInt32} SECOND)::Int64 AS t,\n avg(bytes_sent) AS avg_bytes_sent\nFROM logs.caddy_requests\nWHERE ts >= now() - {seconds:UInt32}\nGROUP BY t\nORDER BY t\nWITH FILL STEP {rounding:UInt32}\n" }, { "title": "Average response time costs", "query": "SELECT\n toStartOfInterval(ts, INTERVAL {rounding:UInt32} SECOND)::Int64 AS t,\n avg(duration_ms) AS avg_response_time_ms\nFROM logs.caddy_requests\nWHERE ts >= now() - {seconds:UInt32}\nGROUP BY t\nORDER BY t\nWITH FILL STEP {rounding:UInt32}\n" }, { "title": "Gitea Crawler Percentage", "query": "SELECT\n toStartOfInterval(ts, INTERVAL {rounding:UInt32} SECOND)::Int64 AS t,\n countIf(\n host = 'git.aiursoft.cn' \n AND uri LIKE '/PublicVault%' \n AND uri LIKE '%commit%'\n ) / count() AS crawler_ratio\nFROM logs.caddy_requests\nWHERE ts >= now() - {seconds:UInt32}\nGROUP BY t\nORDER BY t\nWITH FILL STEP {rounding:UInt32}\n" }, { "title": "HTTP Code distribution", "query": "SELECT\n toStartOfInterval(ts, INTERVAL {rounding:UInt32} SECOND)::Int64 AS t,\n countIf(status >= 100 AND status < 200) / count() AS ratio_1xx,\n countIf(status >= 200 AND status < 300) / count() AS ratio_2xx,\n countIf(status >= 300 AND status < 400) / count() AS ratio_3xx,\n countIf(status >= 400 AND status < 500) / count() AS ratio_4xx,\n countIf(status >= 500 AND status < 600) / count() AS ratio_5xx\nFROM logs.caddy_requests\nWHERE ts >= now() - {seconds:UInt32}\nGROUP BY t\nORDER BY t\nWITH FILL STEP {rounding:UInt32}\n" }, { "title": "Requests by host distribution", "query": "SELECT\n toStartOfInterval(ts, INTERVAL {rounding:UInt32} SECOND)::Int64 AS t,\n sumIf(1, host = 'git.aiursoft.cn') AS Gitea,\n sumIf(1, host = 'nextcloud.aiursoft.cn') AS Nextcloud,\n sumIf(1, host = 'stathub.aiursoft.cn') AS Stathub,\n sumIf(1, host = 'hub.aiursoft.cn') AS DockerHub,\n sumIf(1, host = 'gitlab.aiursoft.cn') AS GitLab,\n sumIf(1, host = 'auth.aiursoft.cn') AS Auth,\n sumIf(1, host = 'wiki.aiursoft.cn') AS Wiki,\n sumIf(1, host = 'remotely.aiursoft.cn') AS Remotely,\n -- Others：统计 host 不在上述列表中的所有请求\n sumIf(\n 1,\n host NOT IN (\n 'git.aiursoft.cn',\n 'nextcloud.aiursoft.cn',\n 'stathub.aiursoft.cn',\n 'hub.aiursoft.cn',\n 'gitlab.aiursoft.cn',\n 'auth.aiursoft.cn',\n 'wiki.aiursoft.cn',\n 'remotely.aiursoft.cn'\n )\n ) AS Others\nFROM logs.caddy_requests\nWHERE ts >= now() - {seconds:UInt32}\nGROUP BY t\nORDER BY t\nWITH FILL STEP {rounding:UInt32}\n" }, { "title": "Methods distribution", "query": "SELECT\n toStartOfInterval(ts, INTERVAL {rounding:UInt32} SECOND)::Int64 AS t,\n sumIf(1, method = 'GET') AS GET,\n sumIf(1, method = 'POST') AS POST,\n sumIf(1, method = 'OPTIONS') AS OPTIONS,\n sumIf(1, method = 'PROPFIND') AS PROPFIND,\n sumIf(\n 1,\n method NOT IN ('GET','POST','OPTIONS', 'PROPFIND')\n ) AS Others\nFROM logs.caddy_requests\nWHERE ts >= now() - {seconds:UInt32}\nGROUP BY t\nORDER BY t\nWITH FILL STEP {rounding:UInt32}\n" } ] }