goosquery

SQL-powered operating system instrumentation, monitoring, and analytics framework for Windows, inspired by osquery. Built in Go.

Demo

Click the preview to open the MP4 video.

CLI

# Run a query
goosquery.exe -q "select pid, port, address from listening_ports;"

# JSON output
goosquery.exe -q "select * from authenticode where path = 'C:/Windows/System32/ntoskrnl.exe';" -json

# Interactive mode
goosquery.exe -i

# List registered tables
goosquery.exe -tables

# Show a table schema
goosquery.exe -schema processes

Flags:

Flag	Description
-q <sql>	Execute one SQL query.
-i	Start the interactive REPL.
-json	Print query results as JSON.
-tables	List registered tables and descriptions.
-schema <table>	Print a table's columns and metadata flags.
-version, -v	Print version/banner information.
-help, -h	Print CLI help.

Interactive commands:

Command	Description
.quit	Exit the REPL.
.json	Switch to JSON output.
.table	Switch to table output.
.mode	Show the current output mode.
.tables	List registered tables.
.schema NAME	Print columns for a table.
.help	Print REPL help.

Examples

Querying the authenticode table for a specific file:

goosquery.exe -q "select * from authenticode where path = 'C:/Windows/System32/ntoskrnl.exe';" -json

[
  {
    "path": "C:/Windows/System32/ntoskrnl.exe",
    "original_program_name": "Microsoft",
    "serial_number": "33000002ec6579ad1e670890130000000002ec",
    "issuer_name": "Microsoft Windows Production PCA 2011",
    "subject_name": "Microsoft Windows",
    "result": "trusted"
  }
]
Total rows: 1

Querying the processes table for the first 10 processes:

goosquery.exe -q "select pid, parent, name, path from processes limit 10;"

+-----+--------+------------------+----------------------------------+
| pid | parent | name             | path                             |
+-----+--------+------------------+----------------------------------+
| 0   | 0      | [System Process] |                                  |
| 4   | 0      | System           |                                  |
| 92  | 4      | Registry         |                                  |
| 384 | 4      | smss.exe         | C:\Windows\System32\smss.exe     |
| 496 | 484    | csrss.exe        | C:\Windows\System32\csrss.exe    |
| 576 | 484    | wininit.exe      | C:\Windows\System32\wininit.exe  |
| 592 | 568    | csrss.exe        | C:\Windows\System32\csrss.exe    |
| 672 | 568    | winlogon.exe     | C:\Windows\System32\winlogon.exe |
| 708 | 576    | services.exe     | C:\Windows\System32\services.exe |
| 720 | 576    | lsass.exe        | C:\Windows\System32\lsass.exe    |
+-----+--------+------------------+----------------------------------+
Total rows: 10

Viewing the schema for the os_version table:

goosquery.exe -schema os_version

os_version
  A single row containing the operating system name and version.
  name                         TEXT     Distribution or product name
  version                      TEXT     Pretty, suitable for presentation, OS version
  major                        INTEGER  Major release version
  minor                        INTEGER  Minor release version
  patch                        INTEGER  Optional patch release
  build                        TEXT     Optional build-specific or variant string
  platform                     TEXT     OS Platform or ID
  platform_like                TEXT     Closely related platforms
  codename                     TEXT     OS version codename
  arch                         TEXT     OS Architecture
  install_date                 BIGINT   The install date of the OS.
  revision                     INTEGER  Update Build Revision, refers to the specific revision number of a Windows update

Some tables are intentionally bounded by constraints. For example, file requires a path or directory constraint, curl requires url = ..., and tables such as hash, authenticode, and process_memory_map should be queried with their indexed input columns.

SQL Support

SQL is handled by SQLite. Standard SQLite syntax works, including:

• SELECT
• WHERE
• ORDER BY
• GROUP BY
• LIMIT
• joins and subqueries supported by SQLite
• aggregate functions such as count, sum, avg, min, and max

The virtual table planner passes usable constraints and selected columns into table scanners. Table code should use those inputs to avoid expensive full data collection when a bounded lookup is possible.

Registered Tables

Use goosquery.exe -tables for descriptions. The current registered table set is:

Registered tables

Table
appcompat_shims
arp_cache
authenticode
background_activities_moderator
bitlocker_info
certificates
chocolatey_packages
connectivity
cpu_info
curl
default_environment
deviceguard_status
disk_info
dns_cache
drivers
etc_hosts
etc_protocols
etc_services
file
groups
hash
interface_addresses
interface_details
kernel_info
kva_speculative_info
listening_ports
logged_in_users
logical_drives
logon_sessions
memory_devices
ntdomains
os_version
patches
pipes
platform_info
process_memory_map
process_open_sockets
processes
programs
python_packages
registry
routes
scheduled_tasks
security_profile_info
services
shared_resources
ssh_configs
system_info
time
uptime
user_groups
user_ssh_keys
users
winbaseobj
windows_firewall_rules
windows_optional_features
windows_security_center
windows_security_products
windows_update_history

Table Implementation

Each table has:

• schema.go with a table.Spec
• a generator with signature func(table.Request) (table.Rows, error)

Example:

package example

import (
    "fmt"

    "github.com/sonx4444/goosquery/sql/table"
)

var Spec = table.Spec{
    Name:        "example",
    Description: "Example bounded table.",
    Columns: []table.ColumnSpec{
        {
            Name:        "path",
            Type:        table.Text,
            Description: "Input path",
            Required:    true,
            PushdownOps: []table.ConstraintOp{table.OpEQ},
        },
        {Name: "size", Type: table.BigInt, Description: "Size in bytes"},
    },
}

func GenExample(req table.Request) (table.Rows, error) {
    paths := req.ConstraintValues("path", table.OpEQ)
    if len(paths) == 0 {
        return nil, fmt.Errorf("example requires path = ...")
    }

    row := Spec.NewRow(req)
    row.Set("path", paths[0])
    row.Set("size", int64(0))
    return table.Rows{row}, nil
}

Register the table in sql/engine/defaults.go:

{
    Spec:    example.Spec,
    Scanner: example.GenExample,
}

Column flags:

Flag	Meaning
Required	The table should error if no usable constraint is supplied.
Hidden	The column is available for predicates but hidden from normal select *.
PushdownOps	Operators SQLite may pass into the scanner for that column. Other operators remain valid SQL and are evaluated by SQLite after scanning.

Request.Columns contains every column SQLite needs for projection, filtering, ordering, or another query operation. An empty list means no table columns are needed, such as for count(*). Generators should guard optional work with IsColumnUsed or IsAnyOfColumnsUsed.

Build

go test ./...
go build -o build/goosquery.exe ./cmd/goosquery