Editing OSQuery (section)

=== Check the schema for individual tables ===
Now that you know the table names, you can see what information each table provides. As an example, choose processes, since the ps command is used quite often to get this information. Run the .schema command followed by the table name to see what information is saved in this table. If you want to check the results, you could quickly run ps -ef or ps aux and compare the output with the contents of the table:
 osquery> .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, `is_elevated_token` INTEGER HIDDEN, `elapsed_time` BIGINT HIDDEN, `handle_count` BIGINT HIDDEN, `percent_processor_time` BIGINT 
 HIDDEN, `upid` BIGINT HIDDEN, `uppid` BIGINT HIDDEN, `cpu_type` INTEGER HIDDEN, `cpu_subtype` INTEGER HIDDEN, `phys_footprint` BIGINT HIDDEN, PRIMARY KEY 
 (`pid`)) WITHOUT ROWID;

To drive home the point, use the following command to see the schema for the RPM packages and compare the information with rpm -qa and rpm -qi operating system commands:
 osquery> .schema rpm_packages
 
 CREATE TABLE rpm_packages(`name` TEXT, `version` TEXT, `release` TEXT, `source` TEXT, `size` BIGINT, `sha1` TEXT, `arch` TEXT, `epoch` INTEGER, 
 `install_time` INTEGER, `vendor` TEXT, `package_group` TEXT, `pid_with_namespace` INTEGER HIDDEN, `mount_namespace_id` TEXT HIDDEN, PRIMARY KEY (`name`, 
 `version`, `release`, `arch`, `epoch`, `pid_with_namespace`)) WITHOUT ROWID;


In case that schema information is too cryptic for you, there is another way to print the table information in a verbose, tabular format: the PRAGMA command. For example, I'll use PRAGMA to see information for the rpm_packages table in a nice format.  One benefit of this tabular information is that you can focus on the field you want to query and see the type of information that it provides:
<pre>osquery> PRAGMA table_info(users);
 
+-----+-------------+--------+---------+------------+----+
| cid | name        | type   | notnull | dflt_value | pk |
+-----+-------------+--------+---------+------------+----+
| 0   | uid         | BIGINT | 1       |            | 1  |
| 1   | gid         | BIGINT | 0       |            | 0  |
| 2   | uid_signed  | BIGINT | 0       |            | 0  |
| 3   | gid_signed  | BIGINT | 0       |            | 0  |
| 4   | username    | TEXT   | 1       |            | 2  |
| 5   | description | TEXT   | 0       |            | 0  |
| 6   | directory   | TEXT   | 0       |            | 0  |
| 7   | shell       | TEXT   | 0       |            | 0  |
| 8   | uuid        | TEXT   | 1       |            | 3  |
+-----+-------------+--------+---------+------------+----+</pre>