{"id":5538,"date":"2023-11-02T19:42:48","date_gmt":"2023-11-02T11:42:48","guid":{"rendered":"https:\/\/ixyzero.com\/blog\/?p=5538"},"modified":"2023-11-02T19:42:48","modified_gmt":"2023-11-02T11:42:48","slug":"%e4%b8%80%e4%ba%9bsql%e4%bb%a3%e7%a0%81%e7%89%87%e6%ae%b5%e6%95%b4%e7%90%86","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/5538.html","title":{"rendered":"\u4e00\u4e9bSQL\u4ee3\u7801\u7247\u6bb5\u6574\u7406"},"content":{"rendered":"\n<p>=Start=<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">\u7f18\u7531\uff1a<\/h4>\n\n\n\n<p>\u7b80\u5355\u6574\u7406\u4e00\u4e0b\u4e2a\u4eba\u89c9\u5f97\u6709\u7528\/\u901a\u7528\u7684SQL\u4ee3\u7801\u7247\u6bb5\uff0c\u65b9\u4fbf\u540e\u9762\u6709\u9700\u8981\u7684\u65f6\u5019\u53c2\u8003\u548c\u4f7f\u7528\u3002<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">\u6b63\u6587\uff1a<\/h4>\n\n\n\n<h5 class=\"wp-block-heading\">\u53c2\u8003\u89e3\u7b54\uff1a<\/h5>\n\n\n\n<p>Nginx\u65e5\u5fd7\u4e2d\u7684UA\u5b57\u6bb5\u7684\u5207\u5206\u548c\u4fe1\u606f\u63d0\u53d6<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- \u4eceHTTP\u8bf7\u6c42\u7684user_agent\u5b57\u6bb5\u4e2d\u63d0\u53d6\u4e00\u4e9b\u4fe1\u606f\nSELECT\n`op_datetime`\n,`device`\n,`device_id`\n,split(replace(user_agent, ')', '('),'\\\\(')&#91;1] as ua\n,case\nwhen user_agent rlike 'Windows NT|Macintosh' THEN 'PC'\nwhen user_agent rlike 'iPhone|iPad|Android' THEN 'mobile'\nelse 'others'\nEND AS platform\n,case\nwhen locate('Windows NT',user_agent) &gt; 0 THEN 'win'\nwhen locate('Macintosh',user_agent) &gt; 0 THEN 'mac'\nwhen locate('iPhone',user_agent) &gt; 0 THEN 'iphone'\nwhen locate('iPad',user_agent) &gt; 0 THEN 'ipad'\nwhen locate('Android',user_agent) &gt; 0 THEN 'android'\nwhen locate('Linux x86_64',user_agent) &gt; 0 THEN 'linux'\nwhen locate('Java\/',user_agent) &gt; 0 THEN 'java'\nelse 'others'\nEND AS os_type\n,`user_agent`<\/code><\/pre>\n\n\n\n<p>GitLab\u4e2d\u548c\u4ee3\u7801\u4ed3\u5e93\u4e0b\u8f7d\u6709\u5173\u7684\u65e5\u5fd7\u548c\u67e5\u8be2\u6761\u4ef6<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- \u5728GitLab\u76843\u4e2a\u65e5\u5fd7\uff08production_json.log\u3001api_json.log\u3001gitlab-shell.log\uff09\u4e2d\u67e5\u770b\u4e0b\u8f7d\u884c\u4e3a\u8bb0\u5f55\nSELECT\nfrom_unixtime(cast(`timestamp`\/1000 as BIGINT)) as time_str\n,username\n,remote_ip\n,action as op\n,project_path\n,status\n,ua\n,p_dt\n,p_hour\nFROM\norigin_sec_log.gitlab_rails_production_json\nWHERE\np_dt between '20231102' and '20231102'\nand (project_path like '%reponame%'\n)\nAND action = 'git_upload_pack'\n\nunion all\n\nSELECT\nfrom_unixtime(cast(`timestamp`\/1000 as BIGINT)) as time_str\n,username\n,remote_ip\n,'git-upload-pack' as op\n,project_path\n,status\n,ua\n,p_dt\n,p_hour\nFROM\norigin_sec_log.gitlab_rails_api_json\nWHERE\np_dt between '20231102' and '20231102'\nand params like '%\"git-upload-pack\"%'\nand username IS NOT NULL\nand (project_path like '%reponame%'\n)\n\nunion all\n\nSELECT\nfrom_unixtime(cast(`timestamp`\/1000 as BIGINT)) as time_str\n,username\n,null as remote_ip\n,command as op\n,gl_project_path as project_path\n,status\n,null as ua\n,p_dt\n,p_hour\nFROM\norigin_sec_log.gitlab_shell_log\nWHERE\np_dt between '20231102' and '20231102'\nAND command = 'git-upload-pack'\nand (gl_project_path like '%reponame%'\n)\n;<\/code><\/pre>\n\n\n\n<p>\u5206\u6790\u6216\u6e05\u6d17Nginx\u65e5\u5fd7\u65f6\u4f1a\u7528\u5230\u7684\u4e00\u4e9b\u5b57\u6bb5\u4ee5\u53ca\u9700\u8981\u5173\u6ce8\u7684\u4e00\u4e9b\u7c7b\u578b\u7684\u6570\u636e<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE IF NOT EXISTS origin_sec_log.nginx_access_log (\n`unix_timestamp` bigint,\n`datetime_str` string,\n`user_name` string,\n`ipv4` string,\n`ipv6` string,\n`scheme` string,\n`domain` string,\n`uri` string,\n`req_method` string,\n`req_args` string,\n`req_body` string,\n`resp_content` string,\n`status` int,\n`http_user_agent` string,\n`device_id` string,\n`http_referer` string,\n`http_x_forwarded_for` string,\n`http_content_type` string,\n`upstream_response_length` bigint,\n`bytes_sent` bigint\n) \nPARTITIONED BY (\n`p_dt` string\n)\n\nreq_body rlike '(\u94f6\u884c\u5361|\u624b\u673a\u53f7|\u7535\u8bdd|\u5730\u5740|\u8ba2\u5355|\u8eab\u4efd\u8bc1|\u5b9e\u540d|addr|street|company|phone|mobile|token|accesskey|secretkey|passport|key|userid|uid|idcard|idn|order|card|merchant|shop|logistics|asset|cert)'\n\nresp_content rlike '(\u94f6\u884c\u5361|\u624b\u673a\u53f7|\u7535\u8bdd|\u5730\u5740|\u8ba2\u5355|\u8eab\u4efd\u8bc1|\u5b9e\u540d|addr|street|company|phone|mobile|token|accesskey|secretkey|passport|key|userid|uid|idcard|idn|order|card|merchant|shop|logistics|asset|cert)'\n\n\u624b\u673a\u53f7\u3001\u8eab\u4efd\u8bc1\u53f7\u3001\u90ae\u7bb1\u3001\u5730\u5740\u3001\u94f6\u884c\u5361\u53f7\n\u7528\u6237ID\u3001\u8ba2\u5355ID\u3001\u641c\u7d22\u5173\u952e\u5b57\u3001\u4e0b\u8f7d\u6587\u4ef6\u540d\n\u6269\u5c55ID1\u3001\u6269\u5c55ID2\n\n,`up_phone` string\n,`up_idno` string\n,`up_email` string\n,`up_address` string\n,`up_bankcard` string\n,`up_userid` string\n,`up_orderid` string\n,`up_keyword` string\n,`down_filename` string\n,`ext_tag1` string\n,`ext_tag2` string<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\">\u53c2\u8003\u94fe\u63a5\uff1a<\/h5>\n\n\n\n<p>GitLab log system<br><a href=\"https:\/\/docs.gitlab.com\/16.5\/ee\/administration\/logs\/\">https:\/\/docs.gitlab.com\/16.5\/ee\/administration\/logs\/<\/a><\/p>\n\n\n\n<p>JavaScript \u901a\u8fc7UserAgent\u83b7\u53d6\u7528\u6237\u8bbe\u5907\u4fe1\u606f\uff08\u6d4f\u89c8\u5668\u4fe1\u606f\u3001\u64cd\u4f5c\u7cfb\u7edf\u4fe1\u606f\uff09<br><a href=\"https:\/\/blog.csdn.net\/weixin_44477431\/article\/details\/117260001\">https:\/\/blog.csdn.net\/weixin_44477431\/article\/details\/117260001<\/a><\/p>\n\n\n\n<p>User-Agent Reduction deprecation trial \uff08\u65b0\u7684Chrome\u6d4f\u89c8\u5668\u5df2\u7ecf\u4e0d\u663e\u793a\u5c0f\u7248\u672c\u53f7\u4e86\uff0c\u5e76\u4e14\u9ad8\u7248\u672cmacOS\u7684\u7248\u672c\u53f7\u4e5f\u4e0d\u51c6\uff09<br><a href=\"https:\/\/developer.chrome.com\/blog\/user-agent-reduction-deprecation-trial\/\">https:\/\/developer.chrome.com\/blog\/user-agent-reduction-deprecation-trial\/<\/a><\/p>\n\n\n\n<p>Nginx\u65e5\u5fd7\u5206\u6790\u76d1\u63a7\u601d\u8def\u6574\u7406<br><a href=\"https:\/\/ixyzero.com\/blog\/archives\/5522.html\">https:\/\/ixyzero.com\/blog\/archives\/5522.html<\/a><\/p>\n\n\n\n<p>API\u65e5\u5fd7\u5206\u6790\u7ecf\u9a8c\u8bb0\u5f55<br><a href=\"https:\/\/ixyzero.com\/blog\/archives\/5451.html\">https:\/\/ixyzero.com\/blog\/archives\/5451.html<\/a><\/p>\n\n\n\n<p>=END=<\/p>\n","protected":false},"excerpt":{"rendered":"<p>=Start= \u7f18\u7531\uff1a \u7b80\u5355\u6574\u7406\u4e00\u4e0b\u4e2a\u4eba\u89c9\u5f97\u6709\u7528\/\u901a\u7528\u7684SQL\u4ee3\u7801\u7247\u6bb5\uff0c\u65b9\u4fbf\u540e\u9762\u6709\u9700\u8981\u7684\u65f6\u5019\u53c2\u8003\u548c\u4f7f\u7528\u3002 \u6b63\u6587 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,23,7],"tags":[1764,1261,1262,19,2048],"class_list":["post-5538","post","type-post","status-publish","format-standard","hentry","category-database","category-knowledgebase-2","category-programing","tag-gitlab","tag-hive","tag-sql","tag-tips","tag-useragent"],"views":1584,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/5538","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/comments?post=5538"}],"version-history":[{"count":1,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/5538\/revisions"}],"predecessor-version":[{"id":5539,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/5538\/revisions\/5539"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=5538"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=5538"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=5538"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}