Osquery Query Helper

What This Skill Does

Help users with all aspects of osquery query work:

• Write queries from scratch based on investigation goals

• Validate queries the user has written against the schema

• Troubleshoot queries that aren't working as expected

• Suggest improvements for performance and accuracy

All work is grounded in the tables and columns defined in the provided schema files for the specified EDR platform.

When to Use

• User needs a query written for incident response or threat hunting

• User wants to validate an existing query against the correct schema

• User has a query that's failing and needs help troubleshooting

• User wants suggestions to improve query performance

Schema File Format

Schema files are located in resources/ and named by EDR platform: <platform>_osquery_schema.spec

• standard_osquery_schema.spec — Baseline vanilla osquery (default when no platform specified)

• EDR-specific examples: bitdefender_ , kolide_ , crowdstrike_ , sentinelone_ , carbonblack_

To discover available platforms:

ls resources/_osquery_schema.spec 2>/dev/null || ls resources/.spec

Platform Notation Formats

Schema files use one of two formats to indicate OS compatibility:

Format 1: Explicit platforms([...]) field

table_name("<table_name>") description("Brief description.") schema([ Column("column_name", TYPE, "Column description") ]) implementation("<table_name>@genTable") platforms(["darwin", "linux", "windows"])

Format 2: #platform marker before table definitions

A platform marker on its own line applies to all tables until the next marker:

#darwin table_name("<mac_only_table>") ...

#linwin table_name("<linux_windows_table>") ...

Platform Marker Reference

Marker Platforms

#darwin

macOS only

#linux

Linux only

#windows

Windows only

#linwin

Linux and Windows

#macwin

macOS and Windows

#posix

macOS, Linux, FreeBSD

#sleuthkit

macOS, Linux (requires The Sleuth Kit)

#utility

Cross-platform utility tables

#cross-platform

All supported platforms

Schema Lookup Procedure

The schema files are large. Always follow the workflow below to extract table definitions.

Helper scripts are provided in scripts/ to simplify complex operations:

• scripts/detect-format.sh

• Determines schema format

• scripts/extract-table.sh

• Extracts complete table definitions

1. Search for Relevant Tables

Search by table name keyword

grep -i 'table_name(".*<keyword>' "$SCHEMA_FILE"

Search descriptions for concepts

grep -i 'description(".*<concept>' "$SCHEMA_FILE"

2. Extract Full Table Definition

Use the extraction script to get the complete table definition:

bash scripts/extract-table.sh <schema_file> <table_name>

Example:

bash scripts/extract-table.sh resources/standard_osquery_schema.spec processes

The script automatically:

• Detects the schema format (platforms array vs platform markers)

• Extracts the complete table definition including all columns and platform info

• Includes the #platform marker for Format 2 schemas

If you need to check the format manually:

bash scripts/detect-format.sh <schema_file>

Returns: "platforms_array" or "platform_markers"

3. Verify Before Using

• Confirm columns exist: Only use columns explicitly listed in the schema([...]) block

• Check OS compatibility: Via platforms([...]) field or preceding #platform marker

• If unsupported: Inform the user and suggest alternatives

Guidelines

When Writing Queries

• Schema is law: Only use tables/columns confirmed via grep from the correct platform schema

• Platform awareness: Verify OS support for each table

• Performance first: Always include WHERE clauses, use LIMIT for exploration, avoid SELECT * , minimize JOINs

• Use placeholders: '<path_to_file>' , '<username>' , '<timestamp>' , '<ip_address>'

• Be upfront about gaps: If a table/column doesn't exist, say so and suggest alternatives

When Validating User Queries

• Verify each table exists in the platform schema

• Confirm all referenced columns exist

• Check OS compatibility

• Flag performance issues (missing WHERE, SELECT *, expensive JOINs)

• Offer corrected queries, not just problems

When Troubleshooting

• Start with schema—most failures are mismatches

• Check basics: table exists, columns spelled correctly, OS supported

• Consider EDR differences—query may be from a different platform

• Explain why something failed, not just how to fix it

Safety and Privacy

• Redact sensitive data in examples

• Query only necessary data

• Prefer hashes over file content dumps

• Use time constraints to limit data volume

Workflow

0. Identify EDR Platform

Ask user or check context. Default to standard_osquery_schema.spec if unspecified.

1. Understand the Request

Writing new query? Validating? Troubleshooting? What data is needed?

2. Search and Extract Schema

Use grep to find tables, then bash scripts/extract-table.sh to get full definitions. Verify columns and OS compatibility from the extracted output.

3. Write, Validate, or Fix

Use only verified tables/columns. Add WHERE clauses and LIMIT for performance.

4. Deliver Response

Include:

• EDR platform/schema used

• Schema lookups performed (show grep commands)

• The query with syntax highlighting

• Explanation of what it does or what was wrong

• Assumptions (platform, OS, environment)

• Limitations (missing tables/columns, OS restrictions, performance concerns)