National Cyber Warfare Foundation (NCWF)

Welcome back, aspiring cyberwarriors! In a modern environment, numerous devices can be connected to an organization’s infrastructure and networks. Security teams and digital forensics professionals tasked with tracking and gathering information from these devices need a tool that allows them to query the operating system like a database. And we have a tool for this […]

The post Digital Forensics: System Monitoring with osquery first appeared on Hackers Arise.

Welcome back, aspiring cyberwarriors!

In a modern environment, numerous devices can be connected to an organization’s infrastructure and networks. Security teams and digital forensics professionals tasked with tracking and gathering information from these devices need a tool that allows them to query the operating system like a database.

And we have a tool for this task: osquery.
In this article, we’ll discuss osquery, its core functionalities, and explore its use cases.

What is osquery?

Osquery is an operating system instrumentation framework that allows you to query your system using SQL syntax. Originally developed by Facebook (now Meta) and released as open source, osquery transforms your operating system into a relational database. This means you can use standard SQL queries to explore running processes, loaded kernel modules, open network connections, browser plugins, hardware events, and file hashes.

Why osquery Matters for Cyberwarriors

Normally, system admins need to run many commands, check different logs, and combine data from several places. Osquery makes this easier by giving one simple way to access all system information.

For cyberwarriors, osquery offers several critical advantages:

Real-time System Visibility: Query live system state without impacting performance

Historical Analysis: When combined with logging, track changes over time

Cross-platform Consistency: Same queries work across Linux, macOS, and Windows

Extensibility: Custom tables and decorators for specialized use cases

Integration Friendly: Easy to incorporate into existing security toolchains

Installation and Initial Setup

Let’s get osquery installed and configured on Kali Linux.

kali> sudo mkdir -p /etc/apt/keyrings

kali> curl -L https://pkg.osquery.io/deb/pubkey.gpg | sudo tee /etc/apt/keyrings/osquery.asc

kali> echo “deb [arch=amd64 signed-by=/etc/apt/keyrings/osquery.asc] https://pkg.osquery.io/deb deb main” | sudo tee /etc/apt/sources.list.d/osquery.list

kali> sudo apt update
kali> sudo apt install osquery

Understanding osquery Architecture

Before we dive into practical usage, it’s important to understand osquery’s architecture:

osqueryi: The interactive shell for running ad-hoc queries. Means you directly run a SQL-like query against your system (e.g., checking running processes, installed packages, or listening ports) instead of running from a scheduled pack or config.

osqueryd: The daemon that runs scheduled queries and logging

Tables: Virtual tables that represent different aspects of the system

Extensions: Plugins that add additional functionality

Configuration: JSON files that define query schedules and options

osquery doesn’t store data persistently—it queries the live system state. When you SELECT from a table, osquery makes real-time system calls to gather the requested information.

Your First osquery Session

Let’s start with the interactive shell:

kali> sudo osqueryi

You’ll see the osquery prompt:

Let’s run some basic queries to get familiar with the system. First of all, let’s get basic system information.

osquery> SELECT hostname, computer_name, hardware_model, cpu_brand FROM system_info;

Looks pretty straightforward, isn’t it? If you find this syntax unfamiliar, I recommend to check the Database Hacking category on the Hackers-Arise website.

Now, let’s list running processes. But usually systems run hundreds or thousands of processes, so let’s limit the lists only to the first 10:

kali> SELECT pid, name, path, cmdline FROM processes LIMIT 10;

Essential osquery Tables

osquery provides hundreds of tables. Here are the most important ones for cyberwarriors:

Process and Execution Tables

processes: Currently running processes

process_events: Process execution events (requires configuration)

process_open_sockets: Network connections by process

process_memory_map: Memory mappings for processes

File System Tables

file: File metadata and hashes

hash: File hashes (MD5, SHA1, SHA256)

file_events: File system changes (requires configuration)

mounts: Mounted filesystems

Network Tables

listening_ports: Services listening on network ports

interface_addresses: Network interface configurations

arp_cache: ARP table entries

routes: Network routing table

User and Authentication Tables

users: System user accounts

logged_in_users: Currently logged-in users

last: Login history

authorized_keys: SSH authorized keys

System Configuration Tables

startup_items: Auto-start programs and services

services: System services (Windows/Linux)

launchd: macOS launch daemons and agents

registry: Windows registry (Windows only)

Setting Up osquery Daemon for Continuous Monitoring

The real power of osquery comes from continuous monitoring using osqueryd. Let’s set up scheduled queries and logging. To get started, we need to create a configuration file.

Here is an example of /etc/osquery/osquery.conf:

{
  "options": {
    "config_plugin": "filesystem",
    "logger_plugin": "filesystem",
    "logger_path": "/var/log/osquery",
    "disable_logging": "false",
    "log_result_events": "true",
    "schedule_splay_percent": "10",
    "pidfile": "/var/osquery/osquery.pidfile",
    "events_expiry": "3600",
    "database_path": "/var/osquery/osquery.db",
    "verbose": "false",
    "worker_threads": "2",
    "enable_monitor": "true"
  },
  "schedule": {
    "system_info": {
      "query": "SELECT hostname, cpu_brand, physical_memory FROM system_info;",
      "interval": 3600,
      "description": "Basic system information"
    },
    "network_connections": {
      "query": "SELECT pid, name, local_address, local_port, remote_address, remote_port FROM process_open_sockets WHERE remote_address != '';",
      "interval": 300,
      "description": "Active network connections"
    },
    "process_monitoring": {
      "query": "SELECT pid, name, path, cmdline, parent FROM processes;",
      "interval": 60,
      "description": "Running processes"
    },
    "login_events": {
      "query": "SELECT * FROM logged_in_users;",
      "interval": 300,
      "description": "User login events"
    },
    "file_integrity": {
      "query": "SELECT path, md5, mtime FROM file WHERE path IN ('/etc/passwd', '/etc/shadow', '/etc/hosts', '/etc/sudoers');",
      "interval": 900,
      "description": "Critical file monitoring"
    }
  },
  "decorators": {
    "load": [
      "SELECT uuid AS host_uuid FROM system_info;",
      "SELECT user AS username FROM logged_in_users WHERE type='user' ORDER BY time DESC LIMIT 1;"
    ]
  }
}

This config makes osquery act like a lightweight security/monitoring agent: it logs system info, process activity, network connections, user logins, and critical file integrity, all with metadata about the host and user.

The next step is to start up the daemon:

kali> sudo systemctl start osqueryd

Enable it to start on boot:

kali> sudo systemctl enable osqueryd

Check status:

kali> sudo systemctl status osqueryd

osquery logs are typically stored in JSON format. You can analyze them using standard tools, for example, by tail:

kali> sudo tail -f /var/log/osquery/osqueryd.results.log

tail -f continuously prints new lines as they’re written (live streaming the log).

Summary

osquery changes the way we monitor systems and analyze security. It lets you use SQL to easily access system data, making advanced analysis available to more cyberwarriors.

To get good at osquery, focus on practice and your own use cases. Start with simple queries, build up to more complex ones.

In this article, we kicked off our exploration of osquery by covering basic commands, looking at its architecture, and setting up a daemon for ongoing monitoring. The next step is on you – start querying your infrastructure like the database it truly is!

If you find this field interesting, consider checking out our Digital Forensics course. Master OTW will walk you through the techniques used to find evidence of criminal activity on a computer or network.