System plugin

The system plugin provides a way to query information about the system running Anyquery.

Installation

You need Anyquery to run this plugin.

Then, install the plugin with the following command:

anyquery install system

Usage

The plugin supports only the SELECT statement. Here are some examples:

-- List all processes
SELECT * FROM system_processes;

-- Available memory
SELECT * FROM system_memory_stats;

Tables definition

system_processes

List all processes running on the system.

-- List all processes
SELECT * FROM system_processes;

-- See instances of a specific executable
SELECT * FROM system_processes WHERE exe = '/usr/bin/bash';

-- See child processes of a specific process
SELECT * FROM system_processes WHERE parent_pid = 1;

-- See child processes recursively
WITH RECURSIVE children AS (
    SELECT * FROM system_processes WHERE parent_pid = 1
    UNION ALL
    SELECT p.* FROM system_processes p JOIN children c ON p.parent_pid = c.pid
)
SELECT * FROM children;

-- See processes with a specific name
SELECT * FROM system_processes WHERE name LIKE '%bash%';

system_process_status

List the status of a process. Requires the pid of the process. It can be either passed as an argument to the table or as a WHERE clause.

-- List the status of a process
SELECT * FROM system_process_status WHERE pid = 1;
-- Or
SELECT * FROM system_process_status(1);

-- List the status of a process with a specific name
SELECT
    p.pid,
    name,
    status
FROM
    system_processes p
    JOIN system_process_status s ON p.pid = s.pid
WHERE
    p.name LIKE '%zsh%';

system_process_memory

List the memory usage of a process. Requires the pid of the process. It can be either passed as an argument to the table or as a WHERE clause.

-- List the memory usage of a process
SELECT * FROM system_process_memory WHERE pid = 1;
-- Or
SELECT * FROM system_process_memory(1);

-- List the memory usage of a process with a specific name
SELECT
    p.pid,
    name,
    resident_set_size,
    virtual_memory_size
FROM
    system_processes p
    JOIN system_process_memory m ON p.pid = m.pid
WHERE
    p.name LIKE '%zsh%';

system_process_files

See the files opened by a process. Requires the pid of the process. It can be either passed as an argument to the table or as a WHERE clause.

-- List the files opened by a process
SELECT * FROM system_process_files WHERE pid = 1;
-- Or
SELECT * FROM system_process_files(1);

-- List the files opened by a process with a specific name
SELECT
    p.pid,
    name,
    path
FROM
    system_processes p
    JOIN system_process_files f ON p.pid = f.pid
WHERE
    p.name LIKE '%zsh%';

system_process_networks

See the network connections of a process. Requires the pid of the process. It can be either passed as an argument to the table or as a WHERE clause.

-- List the network connections of a process
SELECT * FROM system_process_networks WHERE pid = 1;
-- Or
SELECT * FROM system_process_networks(1);

-- List the network connections of a process with a specific name
SELECT
    p.pid,
    name,
    local_address,
    remote_address,
    status
FROM
    system_processes p
    JOIN system_process_networks n ON p.pid = n.pid
WHERE
    p.name LIKE '%zsh%';

system_process_stats

List the CPU/memory/io usage of a process. Requires the pid of the process. It can be either passed as an argument to the table or as a WHERE clause.

-- List the CPU/memory/io usage of a process
SELECT * FROM system_process_stats WHERE pid = 1;
-- Or
SELECT * FROM system_process_stats(1);

-- List the CPU/memory/io usage of a process with a specific name
SELECT
    p.pid,
    name,
    cpu_percent,
    memory_percent,
    io_read_count,
    io_write_count
FROM
    system_processes p
    JOIN system_process_stats s ON p.pid = s.pid
WHERE
    p.name LIKE '%zsh%';

system_cpu_infos

List the CPU information. Depending of the OS, the information may return one row, or one row per core (including hyperthreading).

-- Get the CPU name
SELECT cpu_name FROM system_cpu_infos;

-- Get the count of cores
SELECT cpu_cores FROM system_cpu_infos LIMIT 1;

system_cpu_stats

List the CPU usage. Return one row per core (including hyperthreading).

-- Get the CPU usage
SELECT * FROM system_cpu_stats;

system_swaps

Works only on Linux. List the swap partitions.

-- Get the swap partitions
SELECT * FROM system_swaps;

-- Get the remaining swap space
SELECT sum(free) FROM system_swaps;

-- Get the used swap space
SELECT sum(used) FROM system_swaps;

-- Get the total swap space
SELECT sum(total) FROM system_swaps;

-- Get the percentage of used swap space per swap partition
SELECT
    swap_name,
    (used * 100.0) / total AS used_percent
FROM
    system_swaps;

system_memory_stats

List the memory usage.

-- Get the memory usage
SELECT * FROM system_memory_stats;

-- Get the total memory
SELECT total FROM system_memory_stats;

-- Get the available memory
SELECT available FROM system_memory_stats;

system_partitions

List the partitions.

-- Get the partitions
SELECT * FROM system_partitions;

system_partition_stats

List the usage of the partitions. Requires the mountpoint of the partition. It can be either passed as an argument to the table or as a WHERE clause.

-- Get the usage of the partitions
SELECT * FROM system_partition_stats WHERE mountpoint = '/';
-- Or
SELECT * FROM system_partition_stats('/');

-- Get the usage of the partitions with a specific device
SELECT
    device,
    total,
    free,
    used,
    used_percent
FROM
    system_partitions p
    JOIN system_partition_stats s ON p.mountpoint = s.mountpoint
WHERE
    p.device = '/dev/sda1';

system_network_interfaces

List the network interfaces.

-- Get the network interfaces
SELECT * FROM system_network_interfaces;

system_network_stats

List the network statistics of each network interface.

-- Get the network statistics
SELECT * FROM system_network_stats;

-- Get the network statistics of a specific network interface
SELECT * FROM system_network_stats WHERE name = 'eth0';

Known limitations

• Some tables may not be available on all platforms.