New fleetd table: `ai_tools`

[noahtalerman]

• @noahtalerman: ai_tools table is based on the extension @karmine05 wrote: https://github.com/karmine05/agentic-detector

Goal

User story
As a Security Engineer,
I want to query an ai_tools table that returns all AI software (apps, IDE extensions, etc.) and their risk factors
so that I can decide which tools to allow/block.

Changes

Product

• UI changes: No changes
• CLI (fleetctl) usage changes: No changes
• YAML changes: No changes
• REST API changes: No changes
• Fleet's agent (fleetd) changes: New fleetd table: ai_tools #47641
  • UPDATE: @noahtalerman: Include this table, if we're not already, in the fleetd tables extension. This way, customers that deploy plain osquery (like ufa) get this new table.
• Fleet server configuration changes: No changes
• Exposed, public API endpoint changes: No changes
• fleetdm.com changes: No changes
• GitOps mode UI changes: No changes
• GitOps generation changes: No changes
• Activity changes: No changes
• Permissions changes: No changes
• Changes to paid features or tiers: Fleet Free and Fleet Premium
• My device and fleetdm.com/better changes: No changes
• Usage statistics: No changes
• Other reference documentation changes: No changes
• First draft of test plan added
• Once shipped, requester has been notified
• Once shipped, dogfooding issue has been filed

Engineering

• Test plan is finalized
• Contributor API changes: No changes
• Feature guide changes: No changes
• Database schema migrations: No changes
• This is a premium only feature: No

ℹ️  Please read this issue carefully and understand it. Pay special attention to UI wireframes, especially "dev notes".

Risk assessment

• Requires testing in a hosted environment: No
• Requires load testing: No
• Risk level: Low

Test plan

Make sure to go through the list and consider all events that might be related to this story, so we catch edge cases earlier.

Pre-QA schema check (do first)

• SELECT * FROM ai_tools LIMIT 1; — confirm the discriminator column is kind (per docs PR New fleetd table: ai_tools #47641) vs type (per issue comments), and whether browser_extension rows exist. File a docs bug if the shipped table diverges from schema/tables/ai_tools.yml.
• Confirm queryable on Fleet Free and Premium (not premium-gated).
• Confirm fleetd runs the extension as root/SYSTEM; verify it enumerates all home dirs (/Users/*, /home/*, /root, C:\Users\*), not just the daemon account.

Core flow

For each row: install/create the artifact → query → confirm one matching row with correct name/path/source/running and a valid sha256. Then remove it and confirm the row disappears.

1. mcp_server — SELECT name, source AS client, location, endpoint, running, risk_flags FROM ai_tools WHERE kind='mcp_server';

• macOS: Claude Desktop config (~/Library/Application Support/Claude/claude_desktop_config.json) with a local stdio server launched via npx → expect remote_fetch_exec/unpinned_dependency
• macOS: Cursor config with a remote MCP server over http:// → expect location=remote, cleartext_endpoint
• Windows: Claude Desktop config (%APPDATA%\Claude\claude_desktop_config.json) local server
• Windows: VS Code mcp.json (servers key) remote server
• Linux: Cline/Continue config under ~/.config/ local server with inline API_KEY env → expect plaintext_secret
• Linux: VS Code (~/.config/Code/User/) remote server

2. ide_plugins — SELECT name, identifier, version, category, source FROM ai_tools WHERE kind='ide_plugins';

• macOS: GitHub Copilot in VS Code (~/.vscode/extensions); confirm identifier = publisher.name, version populated 🔴🐛
• macOS: Continue or Cursor extension
• Windows: Copilot in VS Code (%USERPROFILE%\.vscode\extensions) 🔴🐛
• Windows: a JetBrains AI plugin
• Linux: Copilot in VSCodium/VS Code 🔴🐛
• Linux: Zed AI plugin

3. agents — SELECT name, identifier, running, pid, risk_flags, detail FROM ai_tools WHERE kind='agents';

• macOS: Claude Code with permissions.defaultMode set to bypass → expect bypass_permissions/auto_accept_edits
• macOS: aider running → expect running=1, valid pid
• Windows: Claude Code
• Windows: Gemini CLI or Codex
• Linux: goose or opencode
• Linux: aider running unattended (auto-approve) → expect skip_permissions_runtime

4. apps — SELECT name, identifier, version, running, path FROM ai_tools WHERE kind='apps';

• macOS: Claude Desktop.app (bundle ID in identifier); test installed-not-running (running=0)
• macOS: Ollama.app or ChatGPT.app, running (running=1, pid set)
• Windows: LM Studio
• Windows: Ollama or ChatGPT desktop 🔴🐛
• Linux: Ollama
• Linux: Jan or GPT4All

5. sockets — SELECT name, location, endpoint, port, pid, category FROM ai_tools WHERE kind='sockets';

• macOS: run Ollama → local listener on port 11434, location=local
• macOS: run Claude Code doing real work → location=remote, endpoint=addr:port, attributed to agent process
• Windows: Ollama local listener
• Windows: ChatGPT/Claude desktop egress
• Linux: Ollama / local inference listener
• Linux: aider/goose egress (tested via Claude Code instead — same "agent doing real work → remote egress" assertion)
• Attribution: a non-AI process talking to a shared cloud IP is NOT flagged; loopback treated as local IPC, not egress

6. agent_instruction — SELECT name, path, risk_flags, sha256, detail FROM ai_tools WHERE kind='agent_instruction';

• macOS: CLAUDE.md in a project under ~/
• macOS: .cursorrules with prompt-injection phrasing → injection_markers; add a zero-width char → hidden_unicode
• Windows: AGENTS.md under C:\Users\<u>\...
• Windows: GEMINI.md set world-writable → world_writable — FIXED, PR Read real Windows ACLs in ai_tools fsutil.Stat #50772 merged to main 2026-08-10 (real Windows DACL reading via perm_windows.go). Verified 2026-08-11: rebuilt fleetd directly from main for MARCUSALLEN2FDD, recreated GEMINI.md with icacls /grant Everyone:F, confirmed risk_flags now includes world_writable (previously could never fire). Note: verified against main, not yet cherry-picked into rc-minor-fleet-v4.90.0 — needed there before this specific RC ships with the fix.
• Linux: .github/copilot-instructions.md
• Linux: Cursor .mdc rule with injection markers
• Confirm sha256 changes when file content changes

7. browser_extension (only if shipped — confirm in pre-QA check) — SELECT name, identifier, source, risk_flags, detail FROM ai_tools WHERE kind IN ('browser_extension','browser_ext');

• macOS: AI extension in Chrome/Arc with <all_urls> → broad_host_permissions; AI extension in Firefox
• Windows: Edge/Brave AI extension, sideloaded unpacked → sideloaded_unverified; Firefox/LibreWolf
• Linux: Chrome/Chromium AI extension; Firefox temporary/unsigned add-on → sideloaded_unverified

Edge cases

• Empty state per OS: host with no AI tools → every kind returns 0 rows, no query error
• kind constraint pushdown: WHERE kind='X' only runs the matching collector (spot-check latency; sockets collector not run when filtering for apps)
• Risk sweep: SELECT kind,name,risk_flags,path FROM ai_tools WHERE risk_flags != ''; returns only intentionally-risky rows 🔴🐛 — fix merged to main in Check trusted location before from_webstore in chromiumSideloaded #50770 (resolves ai_tools: chromiumSideloaded() misclassifies legitimate browser components as sideloaded_unverified #50706); verified 2026-08-10 on a local-only cherry-pick onto rc-minor-fleet-v4.90.0 that Edge Copilot Bridge's risk_flags correctly goes empty. Not yet cherry-picked into this RC branch for real — recommend cherry-picking Check trusted location before from_webstore in chromiumSideloaded #50770 before this release ships.
• Multi-user: artifact under a second user's home dir is surfaced when run as root; uid/username correct
• SELECT kind, count(*) FROM ai_tools GROUP BY kind; matches what was installed
• detail is valid JSON with kind-appropriate keys (e.g. transport/args/env_keys for MCP, permission_mode for agents, engine/profile for extensions)
• Malformed MCP config (invalid JSON) → skipped gracefully, no crash
• Config present but tool uninstalled → confirm expected behavior (presence-of-config is the signal)
• Long paths, non-ASCII usernames, spaces in paths, symlinked home dirs
• Performance: query completes in reasonable time on a host with many extensions / large dev dirs (bounded walk doesn't hang)
• macOS universal build verified on both Apple Silicon and Intel — verified on Apple Silicon only. Code review found no Intel-specific risk (Homebrew paths already handled for both /opt/homebrew and /usr/local, no CGO/unsafe/arch-specific code anywhere in ai_tools), so very unlikely to fail on Intel.

Supplemental testing

Mid-cycle addition by QA: the Core flow's own instruction ("install/create the artifact → query → confirm one matching row ... Then remove it and confirm the row disappears") was validated for detection but not yet for removal. This section tracks that removal half specifically, per type, against the artifacts actually created during this session's testing.

1. mcp_server — SELECT name, source AS client, location, endpoint, running, risk_flags FROM ai_tools WHERE type='mcp_server';

• macOS: delete Claude Desktop config (claude_desktop_config.json) → test-local-fs row disappears
• macOS: delete/edit Cursor config to remove the test-remote entry → row(s) disappear
• macOS: delete Cline config (cline_mcp_settings.json) → test-local row disappears
• Windows: delete Claude Desktop config → test-local-fs row disappears
• Windows: delete VS Code mcp.json → test-remote row disappears
• Linux: delete Cline/Continue config → test-local row disappears
• Linux: delete VS Code mcp.json → test-remote row disappears
• Confirm SELECT count(*) FROM ai_tools WHERE type='mcp_server'; returns 0 once all configs above are removed on a given host

2. ide_plugins — SELECT name, identifier, version, category, source FROM ai_tools WHERE type='ide_plugins';

• macOS: code --uninstall-extension continue.continue → Continue row disappears
• macOS: code --uninstall-extension saoudrizwan.claude-dev → Cline row disappears
• Windows: uninstall Continue/Cline extensions (same --extensions-dir override used to install) → rows disappear
• Windows: uninstall the JetBrains AI Assistant plugin folder (ml-llm) and Continue plugin folder from the profile plugins/ dir → both rows disappear
• Linux: uninstall Continue/Cline extensions → rows disappear

3. agents — SELECT name, identifier, running, pid, risk_flags, detail FROM ai_tools WHERE type='agents';

• macOS: npm uninstall -g @anthropic-ai/claude-code (and same for @google/gemini-cli, @openai/codex) → rows disappear
• macOS: remove aider/goose/opencode binaries → rows disappear
• Windows: npm uninstall -g for claude-code/gemini-cli/codex → rows disappear
• Linux: npm uninstall -g for claude-code/gemini-cli/codex → rows disappear (confirm the known count=2 duplicate — Additional Checks Build docs #7 — also drops to 0, not 1)
• Linux: remove aider/goose/opencode binaries → rows disappear
• Removing ~/.claude/settings.json (bypassPermissions) → risk_flags reverts to empty on the still-installed Claude Code row (tests the posture flag independently of the binary)

4. apps — SELECT name, identifier, version, running, path FROM ai_tools WHERE type='apps';

• macOS: uninstall/delete Ollama.app → row disappears; confirm paired sockets listener row also disappears
• macOS: uninstall/delete ChatGPT.app → row disappears
• Linux: uninstall Ollama (systemctl stop ollama + remove binary) → row disappears; confirm paired sockets row also disappears
• Linux: remove the jan.desktop file and/or the Jan AppImage → row disappears
• (Windows Ollama excluded — never showed up in apps in the first place due to the confirmed registry-visibility gap, Additional Checks Implement fleetctl user create #9; nothing to "remove" for a row that was never detected)

5. sockets — SELECT name, location, endpoint, port, pid, category FROM ai_tools WHERE type='sockets';

• macOS: stop the Ollama process → local listener row(s) disappear
• Windows: stop the Ollama process → local listener row disappears
• Windows: quit ChatGPT.exe → the 5 ai-api-egress rows disappear
• Linux: systemctl stop ollama → local listener row disappears

6. agent_instruction — SELECT name, path, risk_flags, sha256, detail FROM ai_tools WHERE type='agent_instruction';

• macOS: delete the ~/ai-instr-test/ directory (all 6 files) → all 6 rows disappear
• Windows: delete the C:\Users\marcus\ai-instr-test\ directory (4 files) → all 4 rows disappear
• Linux: delete the /root/ai-instr-test/ directory (6 files) → all 6 rows disappear
• Confirm SELECT count(*) FROM ai_tools WHERE type='agent_instruction'; returns 0 per host after cleanup

7. browser_extension — SELECT name, identifier, source, risk_flags, detail FROM ai_tools WHERE type='browser_extension';

• macOS: remove the synthetic Chrome extension (Extensions/<id>/ + its Preferences entry) → row disappears
• macOS: remove the synthetic Firefox extension (extensions.json entry) → row disappears
• Windows: remove the synthetic Firefox extension from the real p5sc6vbh.default-release profile's extensions.json → RESOLVED 2026-08-06: retested with a real extension ("Claude in Sidebar") installed via the Add-ons manager into a genuine profile (OMduSLYm.Profile 1). Confirmed detected by ai_tools, then removed via the Add-ons manager UI + fully quitting Firefox (which is required for the removal to flush to extensions.json/disk). Confirmed both the .xpi file and its extensions.json entry gone, and the corresponding ai_tools row disappeared. Clean pass.
• Linux: remove the synthetic Chromium extension at ~/.config/chromium/Default → row disappears
• Linux: remove the synthetic Firefox extension → row disappears

Testing notes

Confirmation

1. Engineer: Added comment to user story confirming successful completion of test plan (include any special setup, test data, or configuration used during development/testing if applicable).
2. QA: Added comment to user story confirming successful completion of test plan.
3. QA: Determined whether this story needs Playwright automation.
   • Needs automation: Yes / No
   • If yes, filed a follow-up issue in the :help-qa project with status "Needs automation":