osquery 5.23.1 查询语言/DSL 详解
系统讲解 osquery 5.23.1 基于 SQLite 方言的查询语言：虚拟表体系、SELECT/WHERE/JOIN/聚合/内置函数逐类实测、元命令与输出格式、场景化查询模板与性能陷阱，全部语句附 Ubuntu 22.04 实测回显
osquery 5.23.1 查询语言/DSL 详解
目标：系统讲解 osquery 5.23.1 的查询语言——基于 SQLite 方言的 SQL：虚拟表体系、语法基础、SELECT/WHERE/JOIN/聚合/内置函数逐类实测、元命令与输出格式、场景化查询模板与性能陷阱。全部语句与回显已在 Ubuntu 22.04.5 LTS（x86_64，osquery 5.23.1）实测通过，逐条可复现。
一、环境信息
本章为全部实测回显的环境基准，语句在不同环境下结果值会变（如进程列表、包数量），但语法与结构完全一致。
- 操作系统：Ubuntu 22.04.5 LTS（Jammy Jellyfish），内核 6.8.0-138-generic
- 架构：x86_64（amd64）
- osquery 版本：osqueryi version 5.23.1（
osqueryi --version实测） - 实测工具：
osqueryi（交互 shell 与--json单次输出两种形态） - 表清单规模：
.tables实测输出 158 行（Ubuntu 22.04 x86_64 平台） - 演示环境状态：系统装有 nginx（监听 80 端口）、约 179 个 deb 软件包（
SELECT count(*) FROM deb_packages;实测）、root 用户留有 shell 历史、/etc/cron.d/含示例定时任务 - 参考查询目标：deb_packages、processes、listening_ports、users、file、hash、suid_bin、shell_history、crontab、curl 等高频表
二、前置条件
- 已按《Ubuntu 22.04 安装 osquery 5.23.1 指南》第四章完成安装（
osqueryi --version输出5.23.1）。 - 以 root 运行查询（部分表如
suid_bin、shell_history全量视图需要 root 权限，非 root 时仅返回当前用户可见数据）。 - 了解 SQL 基本概念（SELECT/FROM/WHERE），osquery 查询语言是 SQLite 方言的超集裁剪——只读、无写操作。
验证环境就绪：
osqueryi --version
实测回显：
osqueryi version 5.23.1
三、语言总览
osquery 查询语言 = SQLite SQL 方言（只读子集）+ 虚拟表体系（schema 决定可查什么）+ 点命令元命令（shell 内的辅助指令，非 SQL）。三者的分工：
| 组成 | 形态 | 作用 | 示例 |
|---|---|---|---|
| SQL 方言 | 标准语句 | 对虚拟表做查询、过滤、关联、聚合 | SELECT name FROM processes; |
| 虚拟表 | schema 定义 | 把操作系统状态映射为行列结构 | processes、deb_packages |
| 元命令 | . 开头 | shell 辅助：列表、看结构、换输出格式、计时 | .tables、.schema users、.mode json |
适用场景与 CLI/API 分工：
- 即席排查（人工）：
osqueryi交互式，SQL 即席查询，输出表格人读友好。 - 脚本/自动化：
osqueryi --json "SELECT * FROM os_version;"单次执行输出机器可读 JSON。 - 持续监控（无人值守）：同样的 SQL 写进 osqueryd 配置
schedule，按 interval 周期执行、结果落盘（配置写法见《Ubuntu 22.04 安装 osquery 5.23.1 指南》第八章）。
💡 技术拓展：同为「查询语言」，osquery SQL 与其他监控 DSL 的定位差异——
语言 载体 查询对象 风格 osquery SQL osqueryi/osqueryd 主机状态虚拟表 标准 SQL，关系型思维 PromQL Prometheus 时序指标 函数式向量表达式 LogQL Loki 日志流 标签选择+管道过滤 ES DSL Elasticsearch 索引文档 JSON 声明式 osquery 的独特之处：不需要额外学习 DSL 语法——会 SQL 就会查主机。本文实际采用标准 SQL（SQLite 方言）作为唯一查询语言。
进入交互 shell 的实测形态（提示符 osquery>，启动提示 Using a virtual database. Need help, type '.help'）：
$ osqueryi
Using a virtual database. Need help, type '.help'
osquery> SELECT version, instance_id FROM osquery_info;
+---------+--------------------------------------+
| version | instance_id |
+---------+--------------------------------------+
| 5.23.1 | 738a1d93-76ae-487b-81da-9448872fc521 |
+---------+--------------------------------------+
osquery> .exit
要点：osqueryi 连接的是一个瞬态内存虚拟数据库（transient 'in-memory' virtual database），每次查询实时采集系统状态，退出即消失；
.exit（或.quit、Ctrl+D）退出。
四、语法基础
4.1 数据类型
osquery 虚拟表的列类型在 .schema 中声明，实测 processes 表可见（.schema processes 输出节选）：
CREATE TABLE processes(`pid` BIGINT, `name` TEXT, `path` TEXT, `cmdline` TEXT, `state` TEXT, `cwd` TEXT, `root` TEXT, `uid` BIGINT, `gid` BIGINT, `euid` BIGINT, `egid` BIGINT, `suid` BIGINT, `sgid` BIGINT, `on_disk` INTEGER, `wired_size` BIGINT, `resident_size` BIGINT, `total_size` BIGINT, `user_time` BIGINT, `system_time` BIGINT, `disk_bytes_read` BIGINT, `disk_bytes_written` BIGINT, `start_time` BIGINT, `parent` BIGINT, `pgroup` BIGINT, `threads` INTEGER, `nice` INTEGER, `elevated_token` INTEGER HIDDEN, `cgroup_path` TEXT, PRIMARY KEY (`pid`)) WITHOUT ROWID;
类型归纳与说明：
| 类型 | 含义 | 典型列 | 注意点 |
|---|---|---|---|
| TEXT | 字符串 | name、path、cmdline | 比较用 =/LIKE |
| BIGINT | 64 位整数 | pid、uid、resident_size | 单位看列义（字节/秒） |
| INTEGER | 整数/布尔 | threads、on_disk | 布尔用 0/1 |
| HIDDEN 列 | 隐藏列 | elevated_token、elapsed_time | 不 SELECT * 输出，显式点名才返回，多为平台差异列 |
要点：看似数字的列也可能是 TEXT（如
os_version.major是 INTEGER，而listening_ports.port在 JSON 输出里带引号是 TEXT），过滤比较时不确定就先.schema <表>确认类型。
4.2 表达式与操作符
常用操作符全部为 SQLite 语义，实测一组对比：
osqueryi --json "SELECT 1 = 1 AS eq, 2 > 1 AS gt, 'a' != 'b' AS ne, 1 IN (1, 2) AS in_op, 'abc' LIKE 'a%' AS like_op, NULL IS NULL AS null_op;"
实测回显：
[
{"eq":"1","gt":"1","in_op":"1","like_op":"1","ne":"1","null_op":"1"}
]
布尔真值输出为 1。LIKE 的通配是 %（任意串）与 _（单字符）；GLOB 用 */? 语法但语义受限（见 5.2 与第九章实测）。
五、核心语句分类详解
5.1 SELECT 与列筛选
全列与指定列、行值读取、列别名（AS）：
osqueryi --json "SELECT hostname, uuid, cpu_brand, cpu_logical_cores, physical_memory FROM system_info;"
实测回显：
[
{"cpu_brand":"Intel(R) Core(TM) i7-14700KF","cpu_logical_cores":"12","hostname":"3099d8ae819b","physical_memory":"33607307264","uuid":"32074d56-8f30-5439-31f0-14b7ebd3b4d8"}
]
osqueryi --json "SELECT name AS os_name, version AS os_ver, codename FROM os_version;"
实测回显：
[
{"codename":"jammy","os_name":"Ubuntu","os_ver":"22.04.5 LTS (Jammy Jellyfish)"}
]
开机时长（uptime 表把秒拆解为天/时/分）：
osqueryi --json "SELECT days, hours, minutes, total_seconds FROM uptime;"
实测回显：
[
{"days":"1","hours":"6","minutes":"56","total_seconds":"111417"}
]
CASE WHEN 条件表达式（把 0/1 布尔翻译成人话）：
osqueryi --json "SELECT name, CASE WHEN on_disk = 1 THEN 'yes' ELSE 'no' END AS on_disk_flag FROM processes WHERE pid = 1;"
实测回显：
[
{"name":"sleep","on_disk_flag":"yes"}
]
要点：
WHERE pid = 1精确命中 1 号进程（本实测环境 1 号进程为sleep常驻）；进程表按 PID 主键组织，单点查询代价极小。
5.2 WHERE 行过滤
等值与列表（IN 子查询形态见 5.6）：过滤名称含 ssl 的 deb 包：
osqueryi --json "SELECT name, version FROM deb_packages WHERE name LIKE '%ssl%' ORDER BY name;"
实测回显：
[
{"name":"libssl3","version":"3.0.2-0ubuntu1.29"},
{"name":"openssl","version":"3.0.2-0ubuntu1.30"}
]
LIKE 目录前缀过滤（file 表的标准打开方式）：列 /etc/nginx 下的文件（path LIKE '/etc/nginx/%'）：
osqueryi --json "SELECT path, mode, size FROM file WHERE path LIKE '/etc/nginx/%' ORDER BY path LIMIT 6;"
实测回显：
[
{"mode":"0755","path":"/etc/nginx/conf.d/","size":"4096"},
{"mode":"0644","path":"/etc/nginx/fastcgi.conf","size":"1125"},
{"mode":"0644","path":"/etc/nginx/fastcgi_params","size":"1055"},
{"mode":"0644","path":"/etc/nginx/koi-utf","size":"2837"},
{"mode":"0644","path":"/etc/nginx/koi-win","size":"2223"},
{"mode":"0644","path":"/etc/nginx/mime.types","size":"3957"}
]
等值单文件（file 表最精确的形态）：
osqueryi --json "SELECT path, mode, size FROM file WHERE path = '/etc/nginx/nginx.conf';"
实测回显：
[
{"mode":"0644","path":"/etc/nginx/nginx.conf","size":"1447"}
]
directory 列（等价于按父目录列举，file 表专用优化路径）：
osqueryi --json "SELECT path FROM file WHERE directory = '/etc/nginx' LIMIT 5;"
实测回显：
[
{"path":"/etc/nginx/proxy_params"},
{"path":"/etc/nginx/conf.d"},
{"path":"/etc/nginx/uwsgi_params"},
{"path":"/etc/nginx/fastcgi_params"},
{"path":"/etc/nginx/koi-utf"}
]
⚠️ GLOB 陷阱：按 SQLite 语义写
WHERE path GLOB '/etc/nginx/*.conf'在 5.23.1 实测返回空集（连字面量GLOB '/etc/nginx/nginx.conf'也为空），而同样的行用=/LIKE/directory都能查到。结论：file 表路径过滤用 LIKE/=/directory，不依赖 GLOB；完整现象与排查见第十章 Q1。
5.3 JOIN 多表关联
osquery 的威力在跨表 JOIN。两表：监听端口 → 进程（端口画像最经典查询）：
osqueryi --json "SELECT p.port, p.address, pr.name AS process_name, pr.pid FROM listening_ports p LEFT JOIN processes pr ON p.pid = pr.pid WHERE p.address != '127.0.0.1' ORDER BY p.port;"
实测回显（LEFT JOIN 保证无主 socket 不被过滤掉）：
[
{"address":"0.0.0.0","pid":"28","port":"80","process_name":"nginx"},
{"address":"::","pid":"28","port":"80","process_name":"nginx"}
]
三表 JOIN：端口 → 进程 → 属主用户：
osqueryi --json "SELECT lp.port, lp.address, p.name AS process_name, u.username AS owner FROM listening_ports lp JOIN processes p ON lp.pid = p.pid JOIN users u ON p.uid = u.uid ORDER BY lp.port;"
实测回显：
[
{"address":"0.0.0.0","owner":"www-data","port":"80","process_name":"nginx"},
{"address":"::","owner":"www-data","port":"80","process_name":"nginx"}
]
JOIN：文件 → 哈希（关键配置基线核对）：
osqueryi --json "SELECT f.path, f.size, h.md5 FROM file f JOIN hash h ON f.path = h.path WHERE f.path = '/etc/nginx/nginx.conf';"
实测回显：
[
{"md5":"cc3810567659920d50f2527a6f774519","path":"/etc/nginx/nginx.conf","size":"1447"}
]
JOIN：shell 历史 → 用户（历史命令归属排查）：
osqueryi --json "SELECT u.username, sh.time, sh.command FROM shell_history sh JOIN users u ON sh.uid = u.uid;"
实测回显（注意 stderr 的官方提示——shell_history 默认按当前用户返回，官方建议 JOIN users；此处以 root 执行故拿到 root 的历史）：
W1018 08:16:06.894250 117 virtual_table.cpp:1005] The shell_history table returns data based on the current user by default, consider JOINing against the users table
W1018 08:16:06.894279 117 virtual_table.cpp:1022] Please see the table documentation: https://osquery.io/schema/#shell_history
[
{"command":"SELECT version();","time":"0","username":"root"},
{"command":"systemctl status nginx","time":"0","username":"root"}
]
要点：JOIN 键多为「ID 类」列（pid/uid/path）；
shell_history.time为 0 表示该环境历史文件无时间戳记录（HISTTIMEFORMAT 未开启时的正常现象）。
5.4 聚合与分组（GROUP BY / ORDER BY / LIMIT）
COUNT 全量统计：
osqueryi --json "SELECT count(*) AS pkg_total FROM deb_packages;"
实测回显：
[
{"pkg_total":"179"}
]
osqueryi --json "SELECT count(*) AS suid_total FROM suid_bin;"
实测回显：
[
{"suid_total":"28"}
]
GROUP BY 分类统计（登录 shell 分布——盘点谁有交互权限）：
osqueryi --json "SELECT shell, count(*) AS cnt FROM users GROUP BY shell ORDER BY cnt DESC;"
实测回显：
[
{"cnt":"17","shell":"/usr/sbin/nologin"},
{"cnt":"2","shell":"/bin/bash"},
{"cnt":"1","shell":"/bin/sync"}
]
GROUP BY 多表聚合（每个属主名下的进程数与内存合计）：
osqueryi --json "SELECT u.username, count(*) AS proc_count, sum(p.resident_size) AS total_rss FROM processes p JOIN users u ON p.uid = u.uid GROUP BY u.username ORDER BY proc_count DESC LIMIT 5;"
实测回显：
[
{"proc_count":"12","total_rss":"41779200","username":"www-data"},
{"proc_count":"4","total_rss":"56598528","username":"root"}
]
ORDER BY + LIMIT 榜单（内存占用 TOP 进程）：
osqueryi --json "SELECT pid, name, cmdline, resident_size FROM processes ORDER BY resident_size DESC LIMIT 3;"
实测回显：
[
{"cmdline":"osqueryi --json","name":"osqueryi","pid":"529","resident_size":"49852416"},
{"cmdline":"nginx: worker process","name":"nginx","pid":"16","resident_size":"3317760"},
{"cmdline":"nginx: worker process","name":"nginx","pid":"17","resident_size":"3317760"}
]
说明：查询时刻的进程集合决定结果（osqueryi 自身也在表里）；
resident_size单位字节。包体 TOP5（deb_packages 按 size 降序）：
osqueryi --json "SELECT name, version, size, arch FROM deb_packages ORDER BY size DESC LIMIT 5;"
实测回显：
[
{"arch":"amd64","name":"osquery","size":"93532","version":"5.23.1-1.linux"},
{"arch":"amd64","name":"libicu70","size":"34444","version":"70.1-2"},
{"arch":"amd64","name":"libc6","size":"13595","version":"2.35-0ubuntu3.15"},
{"arch":"amd64","name":"perl-base","size":"7740","version":"5.34.0-3ubuntu1.9"},
{"arch":"amd64","name":"coreutils","size":"7112","version":"8.32-4.1ubuntu1.4"}
]
5.5 内置函数（正则/时间/JSON）
regex_match(text, pattern, n)：返回第 n 个捕获组（三参数签名，实测验证）：
osqueryi --json "SELECT regex_match('nginx/1.18.0 (Ubuntu)', '[0-9]+\\.[0-9]+\\.[0-9]+', 0) AS ver;"
实测回显：
[
{"ver":"1.18.0"}
]
time 表与时间函数：
osqueryi --json "SELECT weekday, year, month, day, timestamp FROM time;"
实测回显：
[
{"day":"18","month":"10","timestamp":"Sun Oct 18 08:17:21 2026 UTC","weekday":"Sunday","year":"2026"}
]
osqueryi --json "SELECT strftime('%Y-%m-%d %H:%M', 'now') AS ts;"
实测回显：
[
{"ts":"2026-10-18 08:17"}
]
JSON 函数（SQLite 内置全家桶可用）。真实场景演示：用 curl 虚拟表抓取一个 JSON API 响应，再用 json_extract 抽字段——先看 curl 表原始输出：
osqueryi --json "SELECT url, response_code, substr(result, 1, 100) AS resp_head FROM curl WHERE url = 'https://httpbin.org/json';"
实测回显：
[
{"resp_head":"{\n \"slideshow\": {\n \"author\": \"Yours Truly\", \n \"date\": \"date of publication\", \n \"slides\": [","response_code":"200","url":"https://httpbin.org/json"}
]
json_extract 抽取嵌套字段与 json_array_length 求数组长度：
osqueryi --json "SELECT response_code, json_extract(result, '$.slideshow.title') AS title, json_extract(result, '$.slideshow.author') AS author FROM curl WHERE url = 'https://httpbin.org/json';"
实测回显：
[
{"author":"Yours Truly","response_code":"200","title":"Sample Slide Show"}
]
osqueryi --json "SELECT json_array_length(json_extract(result, '$.slideshow.slides')) AS slide_count FROM curl WHERE url = 'https://httpbin.org/json';"
实测回显：
[
{"slide_count":"2"}
]
应用映射：这套「curl + json_extract」组合可直接用于解析本机服务暴露的 JSON API（如本地管理接口的健康检查端点），把外部 JSON 状态并入主机巡检 SQL。
5.6 子查询与集合
IN 子查询（语义等价于 5.2 的 LIKE 直查，展示写法）：
osqueryi --json "SELECT name, version FROM deb_packages WHERE name IN (SELECT name FROM deb_packages WHERE name LIKE '%ssl%');"
实测回显：
[
{"name":"libssl3","version":"3.0.2-0ubuntu1.29"},
{"name":"openssl","version":"3.0.2-0ubuntu1.30"}
]
FROM 子查询（对聚合结果二次过滤的惯用法）：
osqueryi --json "SELECT shell, cnt FROM (SELECT shell, count(*) AS cnt FROM users GROUP BY shell) WHERE cnt > 1 ORDER BY cnt DESC;"
实测回显：
[
{"cnt":"17","shell":"/usr/sbin/nologin"},
{"cnt":"2","shell":"/bin/bash"}
]
六、元命令与输出格式
元命令是 osqueryi shell 内置的辅助指令（. 开头，非 SQL）。.help 实测全文如下（保留原始格式）：
Welcome to the osquery shell. Please explore your OS!
You are connected to a transient 'in-memory' virtual database.
.all [TABLE] Select all from a table
.bail ON|OFF Stop after hitting an error
.connect PATH Connect to an osquery extension socket
.disconnect Disconnect from a connected extension socket
.echo ON|OFF Turn command echo on or off
.exit Exit this program
.features List osquery's features and their statuses
.headers ON|OFF Turn display of headers on or off
.help Show this message
.mode MODE Set output mode where MODE is one of:
csv Comma-separated values
column Left-aligned columns see .width
line One value per line
list Values delimited by .separator string
pretty Pretty printed SQL results (default)
.nullvalue STR Use STRING in place of NULL values
.print STR... Print literal STRING
.quit Exit this program
.schema [TABLE] Show the CREATE statements
.separator STR Change separator used by output mode
.socket Show the local osquery extensions socket path
.show Show the current values for various settings
.summary Alias for the show meta command
.tables [TABLE] List names of tables
.types [SQL] Show result of getQueryColumns for the given query
.width [NUM1]+ Set column widths for "column" mode
.timer ON|OFF Turn the CPU timer measurement on or off
6.1 .tables：列出全部虚拟表
实测规模：输出 158 行（Ubuntu 22.04 x86_64）。首尾节选（实测，中段省略）：
osquery> .tables
=> acpi_tables
=> apparmor_events
=> apparmor_profiles
=> apt_sources
=> arp_cache
=> augeas
=> authorized_keys
=> azure_instance_metadata
=> azure_instance_tags
=> block_devices
=> bpf_process_events
=> bpf_socket_events
（中段表清单略）
=> shell_history
=> smbios_tables
=> socket_events
=> ssh_configs
=> startup_items
=> sudoers
=> suid_bin
=> syslog_events
=> system_controls
=> system_info
=> systemd_units
=> time
=> ulimit_info
=> uptime
=> usb_devices
=> user_events
=> user_groups
=> user_ssh_keys
=> users
=> vscode_extensions
=> yara_events
=> yara_file
=> yara_process
=> ycloud_instance_metadata
=> yum_sources
支持模糊过滤：.tables proc 只列名字含 proc 的表。
6.2 .schema：查看表结构
osquery> .schema os_version
CREATE TABLE os_version(`name` TEXT, `version` TEXT COLLATE VERSION, `major` INTEGER, `minor` INTEGER, `patch` INTEGER, `build` TEXT, `platform` TEXT, `platform_like` TEXT, `codename` TEXT, `arch` TEXT, `extra` TEXT HIDDEN, `install_date` BIGINT HIDDEN, `revision` INTEGER HIDDEN, `pid_with_namespace` INTEGER HIDDEN, `mount_namespace_id` TEXT HIDDEN, PRIMARY KEY (`name`, `version`, `major`, `minor`, `patch`, `build`, `platform`, `platform_like`, `codename`, `arch`, `extra`, `install_date`, `revision`, `pid_with_namespace`, `mount_namespace_id`)) WITHOUT ROWID;
shell_history 结构（确认它没有 username 列、需 JOIN users 的依据）：
CREATE TABLE shell_history(`uid` BIGINT, `time` INTEGER, `command` TEXT, `history_file` TEXT, PRIMARY KEY (`uid`, `time`, `command`, `history_file`)) WITHOUT ROWID;
curl 结构（response_code/result 两列名是实测踩点结论，见 5.5）：
CREATE TABLE curl(`url` TEXT, `method` TEXT, `user_agent` TEXT, `response_code` INTEGER, `round_trip_time` BIGINT, `bytes` BIGINT, `result` TEXT, PRIMARY KEY (`url`, `user_agent`)) WITHOUT ROWID;
6.3 .mode 与 .timer：输出格式与计时
一段实测覆盖三种形态（.timer on 让每条语句追加耗时行；.mode csv 逗号分隔；.mode line 每字段一行）：
osquery> .timer on
osquery> SELECT count(*) AS etc_files FROM file WHERE path LIKE '/etc/%';
+-----------+
| etc_files |
+-----------+
| 94 |
+-----------+
Run Time: real 0.004 user 0.000816 sys 0.001562
osquery> .mode csv
osquery> SELECT name, version FROM deb_packages WHERE name LIKE '%ssl%';
name,version
libssl3,3.0.2-0ubuntu1.29
openssl,3.0.2-0ubuntu1.30
Run Time: real 0.004 user 0.002918 sys 0.000000
osquery> .mode line
osquery> SELECT hostname, uuid FROM system_info;
hostname = 3099d8ae819b
uuid = 32074d56-8f30-5439-31f0-14b7ebd3b4d8
Run Time: real 0.001 user 0.000969 sys 0.000000
要点：交互分析用默认 pretty（表格）；导出到脚本用
.mode csv或命令行--json；.timer on是评估查询代价的第一工具（配合第九章陷阱自查）。
七、场景化查询模式（模板 + 实测）
以下 6 个模板覆盖主机巡检最高频诉求，均已实测；替换 WHERE 条件即可复用到任何机器。
7.1 资产盘点：这台机器是谁、装了什么
osqueryi --json "SELECT name, version, arch FROM os_version;"
osqueryi --json "SELECT count(*) AS pkg_total FROM deb_packages;"
实测回显：
[
{"arch":"x86_64","build":"","codename":"jammy","major":"22","minor":"4","name":"Ubuntu","patch":"0","platform":"ubuntu","platform_like":"debian","version":"22.04.5 LTS (Jammy Jellyfish)"}
]
[
{"pkg_total":"179"}
]
7.2 端口画像：谁在对外监听
模板（三表 JOIN 一条出全量对外监听画像）：
SELECT lp.port, lp.address, p.name AS process_name, u.username AS owner
FROM listening_ports lp
JOIN processes p ON lp.pid = p.pid
JOIN users u ON p.uid = u.uid
WHERE lp.address NOT IN ('127.0.0.1', '::1')
ORDER BY lp.port;
实测回显：
[
{"address":"0.0.0.0","owner":"www-data","port":"80","process_name":"nginx"},
{"address":"::","owner":"www-data","port":"80","process_name":"nginx"}
]
7.3 安全巡检：SUID 文件与定时任务
SUID 全量计数与抽样：
osqueryi --json "SELECT count(*) AS suid_total FROM suid_bin;"
osqueryi --json "SELECT path, username, groupname, permissions FROM suid_bin LIMIT 5;"
实测回显：
[
{"suid_total":"28"}
]
[
{"groupname":"root","path":"/bin/su","permissions":"S","username":"root"},
{"groupname":"shadow","path":"/bin/chage","permissions":"G","username":"root"},
{"groupname":"root","path":"/bin/chsh","permissions":"S","username":"root"},
{"groupname":"shadow","path":"/bin/expiry","permissions":"G","username":"root"},
{"groupname":"root","path":"/bin/gpasswd","permissions":"S","username":"root"}
]
说明：
permissions列输出的是特殊权限标志字母（S=setuid 位、G=setgid 位），不是八进制权限数字；/bin与/usr/bin常为同一目录（usrmerge），路径以实际文件系统为准。
定时任务全量（crontab 表聚合 /etc/crontab 与 /etc/cron.d/*）：
osqueryi --json "SELECT path, minute, hour, day_of_week, command FROM crontab ORDER BY path LIMIT 3;"
实测回显：
[
{"command":"root cd / && run-parts --report /etc/cron.hourly","day_of_week":"*","hour":"*","minute":"17","path":"/etc/crontab"},
{"command":"root test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.daily )","day_of_week":"*","hour":"6","minute":"25","path":"/etc/crontab"},
{"command":"root test -e /run/systemd/system || SERVICE_MODE=1 /sbin/e2scrub_all -A -r","day_of_week":"*","hour":"3","minute":"10","path":"/etc/cron.d/e2scrub_all"}
]
7.4 持久化机制排查：谁有交互 shell、谁的 shell 里留了命令
osqueryi --json "SELECT uid, username, directory, shell FROM users WHERE shell NOT LIKE '%nologin%' AND shell NOT LIKE '%false%';"
osqueryi --json "SELECT u.username, sh.command FROM shell_history sh JOIN users u ON sh.uid = u.uid;"
实测回显：
[
{"directory":"/root","shell":"/bin/bash","uid":"0","username":"root"},
{"directory":"/bin","shell":"/bin/sync","uid":"4","username":"sync"},
{"directory":"/home/admin","shell":"/bin/bash","uid":"1000","username":"admin"}
]
[
{"command":"SELECT version();","username":"root"},
{"command":"systemctl status nginx","username":"root"}
]
说明：
/bin/sync属系统内置账号（登录即执行 sync 后退出），模板中可再按uid >= 1000收窄到人工账号。
7.5 外部 JSON 状态并入查询
见 5.5 节 curl + json_extract 实测（response_code/title/author 抽取与 slide_count 计数），模板化即：
SELECT response_code, json_extract(result, '$.<字段路径>') AS value
FROM curl
WHERE url = '<JSON API 地址>';
7.6 关键文件基线：配置变更核对
模板（文件元数据 + MD5 一次拿全，基线比对存这张结果即可）：
SELECT f.path, f.size, f.mtime, h.md5
FROM file f
JOIN hash h ON f.path = h.path
WHERE f.directory = '/etc/nginx'
AND f.path LIKE '%.conf';
单文件等价形态实测（本环境 /etc/nginx 下 .conf 只有主配置）：
osqueryi --json "SELECT f.path, f.size, h.md5 FROM file f JOIN hash h ON f.path = h.path WHERE f.path = '/etc/nginx/nginx.conf';"
实测回显：
[
{"md5":"cc3810567659920d50f2527a6f774519","path":"/etc/nginx/nginx.conf","size":"1447"}
]
八、函数与运算符速查
以下条目均在本篇实测或为 SQLite 内置语义，按用途归类：
| 类别 | 名称 | 签名/用法 | 实测要点 |
|---|---|---|---|
| 比较 | = != <> > < >= <= | 标准比较 | 布尔真输出 1 |
| 集合 | IN (v1, v2) / IN (SELECT ...) | 成员判断 | 5.2/5.6 实测 |
| 模式 | LIKE '%x%' / '_' | 百分号/下划线通配 | deb_packages、file 表实测 |
| 模式 | GLOB 'pattern' | shell 风格通配 | file 表实测返回空集，避免使用（见 Q1） |
| 判空 | IS NULL / IS NOT NULL | NULL 专用 | NULL 等值判断必须用 IS |
| 条件 | CASE WHEN ... THEN ... ELSE ... END | 行内分支 | 5.1 实测 on_disk_flag |
| 聚合 | count(*) / sum(x) / avg(x) / min(x) / max(x) | 分组聚合 | 5.4 实测 count/sum |
| 字符串 | substr(s, start, len) | 截取 | 5.5 curl resp_head 实测 |
| 正则 | regex_match(text, pattern, n) | 返回第 n 捕获组 | 三参数，5.5 实测 |
| 时间 | strftime(fmt, 'now') / datetime('now') | SQLite 时间族 | 5.5 实测 strftime |
| JSON | json_extract(j, '$.path.to') | 取嵌套值 | 5.5 实测 |
| JSON | json_array_length(j, '$.arr') | 数组长度 | 5.5 实测 slide_count |
| 别名 | AS | 列/表别名 | 全篇通用 |
九、性能与陷阱（实测对照）
9.1 查询代价自查（.timer）
file 表是遍历型表，过滤条件的书写直接决定扫描范围。实测对照（.timer on）：
osquery> SELECT count(*) AS etc_files FROM file WHERE path LIKE '/etc/%';
+-----------+
| etc_files |
+-----------+
| 94 |
+-----------+
Run Time: real 0.004 user 0.000816 sys 0.001562
/etc 前缀限定了目录范围，4ms 返回。把前缀放宽到全盘级别的目录（如 LIKE '/usr/%'、LIKE '/%'）会把遍历推到数万文件级，交互时可先用 directory =/等值路径收窄；调度场景务必避免无过滤的宽域 file 遍历（对照 osqueryd 官方 Performance Safety 原则）。
9.2 事件表陷阱
socket_events、process_events、file_events 等事件表在 osqueryi 即席查询下通常为空——事件数据由 osqueryd 的事件订阅进程持续采集并存入其 RocksDB；bpf_process_events 等还要求配置显式启用 BPF。排查思路：先确认数据应由 osqueryd 产出（systemctl is-active osqueryd），再看 /var/log/osquery/osqueryd.results.log 是否有对应查询结果（配置方法见《Ubuntu 22.04 安装 osquery 5.23.1 指南》第八章）。
9.3 结果可变性
虚拟表是「查询时刻快照」：同一语句两次执行的 processes 行数可能不同（进程生灭）、uptime 持续增长、curl 表每次都真实发起网络请求（有响应时间波动）。做基线比对时把「采集时刻」写入结果（strftime('%Y-%m-%dT%H:%M:%S','now') AS collected_at）。
十、常见问题（FAQ）
Q1：WHERE path GLOB '/etc/nginx/*.conf' 为什么查不到文件，但 LIKE 能查到？
实测现象（5.23.1，同一环境）：
SELECT path FROM file WHERE path GLOB '/etc/nginx/*.conf';→ 返回[]（空）- 连字面量
GLOB '/etc/nginx/nginx.conf'也返回空 WHERE path = '/etc/nginx/nginx.conf'→ 正常返回该文件WHERE path LIKE '/etc/nginx/%'、WHERE directory = '/etc/nginx'→ 正常返回目录列举
结论：osquery 的 file 表对路径谓词做了「目录列举」优化，=/LIKE/directory 走该路径；GLOB 谓词在该版本实测不返回数据（属实现差异而非语法错误）。实践约定：file 表一律用 LIKE/=（或 directory 列），把 GLOB 从 file 查询习惯中移除。
Q2：报 no such column: username 之类的列不存在错误怎么办？
实测案例：SELECT username FROM shell_history; → Error: near line 1: no such column: username。.schema shell_history 确认其列为 uid/time/command/history_file，用户名需 JOIN users 表获得（完整写法见 5.3 节实测）。方法论：先 .schema <表> 看真实列名再写语句，不同表命名习惯不完全一致（如 curl 表是 response_code/result 而非直觉的 status_code/body）。
Q3：报 no such function: read_file / no such function: unix_time？
实测案例：SELECT read_file('/tmp/a.json'); → no such function: read_file。osquery 没有把「读文件」做成 SQL 函数（文件内容属 carve/事件域，不是查询函数）；unix_time 也不是内置函数名——取时间用 strftime('%s','now') 或直接查 time 表（实测见 5.5）。JSON 解析对任意 TEXT 列可用（json_extract），无需专用读文件函数。
Q4：正则函数报 wrong number of arguments to function regex_match()？
实测确认 regex_match 是三参数签名：regex_match(目标文本, 模式, 捕获组序号)，第三参从 0 起（0 = 整体匹配）。两参数写法在 5.23.1 直接报错（实测现象即本问标题）。
Q5：查询结果里 pid 是负数 / 为空是什么意思？
listening_ports 等表的 pid 列为 -1 表示 osquery 无法把该 socket 关联到进程（典型原因是环境安全策略限制读取其他进程的 fd 链接，实测现象与解决见《Ubuntu 22.04 安装 osquery 5.23.1 指南》第九章 Q4）。LEFT JOIN processes 时注意 -1 关联不上会得到 NULL 行，用 WHERE lp.pid != -1 先过滤（本篇 5.3 节示例已含此习惯）。
Q6：同一查询两次结果不一样，是 bug 吗？
不是。虚拟表实时采集（第九章 9.3 节），进程/网络/时间类表每次查询都是新快照；curl 表每次真实发请求。这是 osquery 的设计语义而非缺陷，持久化监控交给 osqueryd schedule 完成。
十一、参考链接
- osquery 官方 Schema 手册（全表全列文档）：https://osquery.io/schema
- 官方文档站 SQL Introduction：https://osquery.readthedocs.io
- osquery GitHub 仓库：https://github.com/osquery/osquery
- 官方下载页（版本 5.23.1 current，查询日期 2026-10-18）：https://osquery.io/downloads
- SQLite 表达式与内置函数文档（osquery SQL 的语义基座）：https://www.sqlite.org/lang_expr.html
- SQLite JSON 函数文档：https://www.sqlite.org/json1.html
- 本组件同系列文档：《osquery 5.23.1 介绍与选型指南》、《Ubuntu 22.04 安装 osquery 5.23.1 指南》