=Start=

缘由：

简单整理一下个人觉得有用/通用的SQL代码片段，方便后面有需要的时候参考和使用。

正文：

参考解答：

Nginx日志中的UA字段的切分和信息提取

-- 从HTTP请求的user_agent字段中提取一些信息
SELECT
`op_datetime`
,`device`
,`device_id`
,split(replace(user_agent, ')', '('),'\\(')[1] as ua
,case
when user_agent rlike 'Windows NT|Macintosh' THEN 'PC'
when user_agent rlike 'iPhone|iPad|Android' THEN 'mobile'
else 'others'
END AS platform
,case
when locate('Windows NT',user_agent) > 0 THEN 'win'
when locate('Macintosh',user_agent) > 0 THEN 'mac'
when locate('iPhone',user_agent) > 0 THEN 'iphone'
when locate('iPad',user_agent) > 0 THEN 'ipad'
when locate('Android',user_agent) > 0 THEN 'android'
when locate('Linux x86_64',user_agent) > 0 THEN 'linux'
when locate('Java/',user_agent) > 0 THEN 'java'
else 'others'
END AS os_type
,`user_agent`

GitLab中和代码仓库下载有关的日志和查询条件

-- 在GitLab的3个日志（production_json.log、api_json.log、gitlab-shell.log）中查看下载行为记录
SELECT
from_unixtime(cast(`timestamp`/1000 as BIGINT)) as time_str
,username
,remote_ip
,action as op
,project_path
,status
,ua
,p_dt
,p_hour
FROM
origin_sec_log.gitlab_rails_production_json
WHERE
p_dt between '20231102' and '20231102'
and (project_path like '%reponame%'
)
AND action = 'git_upload_pack'

union all

SELECT
from_unixtime(cast(`timestamp`/1000 as BIGINT)) as time_str
,username
,remote_ip
,'git-upload-pack' as op
,project_path
,status
,ua
,p_dt
,p_hour
FROM
origin_sec_log.gitlab_rails_api_json
WHERE
p_dt between '20231102' and '20231102'
and params like '%"git-upload-pack"%'
and username IS NOT NULL
and (project_path like '%reponame%'
)

union all

SELECT
from_unixtime(cast(`timestamp`/1000 as BIGINT)) as time_str
,username
,null as remote_ip
,command as op
,gl_project_path as project_path
,status
,null as ua
,p_dt
,p_hour
FROM
origin_sec_log.gitlab_shell_log
WHERE
p_dt between '20231102' and '20231102'
AND command = 'git-upload-pack'
and (gl_project_path like '%reponame%'
)
;

分析或清洗Nginx日志时会用到的一些字段以及需要关注的一些类型的数据

CREATE TABLE IF NOT EXISTS origin_sec_log.nginx_access_log (
`unix_timestamp` bigint,
`datetime_str` string,
`user_name` string,
`ipv4` string,
`ipv6` string,
`scheme` string,
`domain` string,
`uri` string,
`req_method` string,
`req_args` string,
`req_body` string,
`resp_content` string,
`status` int,
`http_user_agent` string,
`device_id` string,
`http_referer` string,
`http_x_forwarded_for` string,
`http_content_type` string,
`upstream_response_length` bigint,
`bytes_sent` bigint
) 
PARTITIONED BY (
`p_dt` string
)

req_body rlike '(银行卡|手机号|电话|地址|订单|身份证|实名|addr|street|company|phone|mobile|token|accesskey|secretkey|passport|key|userid|uid|idcard|idn|order|card|merchant|shop|logistics|asset|cert)'

resp_content rlike '(银行卡|手机号|电话|地址|订单|身份证|实名|addr|street|company|phone|mobile|token|accesskey|secretkey|passport|key|userid|uid|idcard|idn|order|card|merchant|shop|logistics|asset|cert)'

手机号、身份证号、邮箱、地址、银行卡号
用户ID、订单ID、搜索关键字、下载文件名
扩展ID1、扩展ID2

,`up_phone` string
,`up_idno` string
,`up_email` string
,`up_address` string
,`up_bankcard` string
,`up_userid` string
,`up_orderid` string
,`up_keyword` string
,`down_filename` string
,`ext_tag1` string
,`ext_tag2` string

参考链接：

GitLab log system
https://docs.gitlab.com/16.5/ee/administration/logs/

JavaScript 通过UserAgent获取用户设备信息（浏览器信息、操作系统信息）
https://blog.csdn.net/weixin_44477431/article/details/117260001

User-Agent Reduction deprecation trial （新的Chrome浏览器已经不显示小版本号了，并且高版本macOS的版本号也不准）
https://developer.chrome.com/blog/user-agent-reduction-deprecation-trial/

Nginx日志分析监控思路整理
https://ixyzero.com/blog/archives/5522.html

API日志分析经验记录
https://ixyzero.com/blog/archives/5451.html

=END=