Osquery -Query Your System, Operations, Processes, and Apps with SQL for Windows, Linux and macOS (Free Tool)

Osquery is a free open-source tool for endpoint monitoring and system instrumentation, initially developed by Facebook and now maintained by the open-source community.

It is totally compatible with Linux, macOS, and Windows, Osquery represents OS data—like hardware configuration, file integrity, processes, and network connections—as SQL tables.

Osquery users can apply SQL queries to retrieve, analyze, and monitor this data in real-time or on a schedule, making it efficient and scalable for both small and enterprise environments, especially for developers, DevOps teams, and security operations centers (SOCs).

Features

• Core Features
• SQL-Powered Querying: Access system data using SQL queries to monitor OS state, making it intuitive for those familiar with SQL.

Security and Compliance Features

• User Activity Tracking: Record and analyze user logins, logouts, and activity, helping maintain security policies.
• System Inventory Management: Catalog hardware, software, and configurations, assisting with asset management and audits.
• Audit and Compliance Reporting: Generate reports based on system configurations, file integrity, and user activity, supporting compliance needs.
• Flexible Logging Options: Integrate with logging and monitoring solutions (e.g., Splunk, ELK) to centralize and analyze data.

Developer and DevOps-Friendly Features

• Customizable and Extensible: Add custom tables and extend functionality to tailor Osquery for unique use cases.
• Integration with DevOps Pipelines: Integrate with CI/CD tools and other DevOps environments to monitor configurations and changes.
• Lightweight and Scalable: Low system overhead allows deployment across large environments without impacting performance.
• Interactive Query Console (osqueryi): Run interactive queries for on-the-spot data inspection.
• Cross-Platform Support: Available for Linux, macOS, and Windows, allowing consistent monitoring across multiple operating systems.
• Real-Time Event Monitoring: Capture real-time events, such as file changes, network activity, and process executions, enabling proactive monitoring.
• File Integrity Monitoring (FIM): Track changes in files, directories, and configuration files for enhanced security and compliance.
• Process Monitoring: Query and monitor active processes, users, and system activity, supporting system audits and security checks.
• Network Monitoring: Inspect network connections, listening ports, and traffic for improved network security.
• Scheduled Queries: Automate and schedule SQL queries to gather data at regular intervals, ensuring continuous monitoring.

Quick Start

# Install on Ubuntu/Debian
apt-get install osquery

# Install on macOS
brew install osquery

# Start the interactive shell
osqueryi

Basic queries to get you started:

-- List all running containers
SELECT * FROM docker_containers;

-- Find processes using too much memory
SELECT pid, name, uid, resident_size 
FROM processes 
WHERE resident_size > 100000000;

-- Monitor SSH attempts
SELECT * FROM auth 
WHERE path = '/var/log/auth.log' 
AND message LIKE '%sshd%';

Why You'll Love Osquery

1. Native SQL Interface

Instead of remembering dozens of different shell commands or writing complex scripts, use familiar SQL:

-- Instead of ps aux | grep nginx
SELECT pid, name, uid 
FROM processes 
WHERE name LIKE '%nginx%';

-- Instead of netstat -tulpn
SELECT * FROM listening_ports 
WHERE port > 1024;

2. DevOps-Ready Features

Configuration Management

-- Track config changes
SELECT path, mode, size, mtime 
FROM file 
WHERE path LIKE '/etc/%' 
AND mtime > (SELECT unix_time FROM time) - 3600;

Container Monitoring

-- Monitor Docker container resource usage
SELECT c.name, 
       c.status, 
       p.resident_size, 
       p.user_time 
FROM docker_containers c 
JOIN processes p ON c.pid = p.pid;

Security Monitoring

-- Find processes with open ports and unexpected users
SELECT p.name, 
       p.path, 
       p.uid, 
       l.port, 
       l.address 
FROM processes p 
JOIN listening_ports l ON p.pid = l.pid 
WHERE p.uid != 0;

3. Integration Points

Prometheus Integration

# prometheus.yml
scrape_configs:
  - job_name: 'osquery'
    static_configs:
      - targets: ['localhost:9100']

ELK Stack Integration

{
  "input": {
    "osquery": {
      "result_file": "/var/log/osquery/osqueryd.results.log",
      "interval": "10s"
    }
  }
}

Production Deployment Tips

1. Performance Optimization

• Use scheduled queries instead of real-time for resource-intensive checks
• Index frequently queried columns in custom tables
• Implement rate limiting for heavy queries

{
  "schedule": {
    "system_checks": {
      "query": "SELECT ... FROM processes ...",
      "interval": 300,
      "snapshot": true
    }
  }
}

2. Scaling Tips

• Deploy with configuration management (Ansible, Chef, Puppet)
• Use fleet management tools for large deployments
• Implement proper logging and monitoring

3. Custom Extension Example

#include <osquery/sdk/sdk.h>

class CustomTable : public TablePlugin {
 private:
  TableColumns columns() const {
    return {
      std::make_tuple("id", INTEGER_TYPE, ColumnOptions::DEFAULT),
      std::make_tuple("data", TEXT_TYPE, ColumnOptions::DEFAULT),
    };
  }
  
  QueryData generate(QueryContext& context) {
    QueryData results;
    // Your custom logic here
    return results;
  }
};

REGISTER_EXTERNAL(CustomTable, "table", "custom_table");

Advanced Use Cases

1. CI/CD Pipeline Integration

# GitLab CI example
osquery_check:
  script:
    - osqueryi --json "SELECT * FROM os_version" > system_info.json
    - python validate_system.py system_info.json

2. Automated Compliance Checks

-- Check password policy compliance
SELECT * 
FROM shadow 
WHERE password_max_days > 90 
OR password_min_days < 7;

3. Performance Monitoring

-- Track system resource usage
SELECT * FROM cpu_time;
SELECT * FROM memory_info;
SELECT * FROM system_info;

Resources

• Home
• Official Documentation
• GitHub Repository
• Schema Reference
• Community Slack: osquery.slack.com

Pro Tips

1. Use --json flag for structured output
2. Create views for commonly used queries
3. Implement rate limiting for resource-intensive queries
4. Use the explain command to optimize queries
5. Set up proper logging and monitoring

Remember: Osquery is powerful but requires careful consideration in production.

Always test queries on a development environment first, and implement proper monitoring and alerting.