Monitoring hard drives (HDD) with Osquery

Osquery Windows, Mac OS and Linux disk information and monitoring

Windows

Basic disk information

osquery> SELECT device_id, ROUND(free_space * 10e-10) AS free_gb, ROUND(size * 10e-10) AS size_gb, boot_partition AS boot 
FROM logical_drives WHERE file_system = 'NTFS';
+-----------+---------+---------+------+
| device_id | free_gb | size_gb | boot |
+-----------+---------+---------+------+
| C: | 214.0 | 268.0 | 1 |
| H: | 1639.0 | 2199.0 | 0 |
+-----------+---------+---------+------+
osquery> SELECT device_id AS path, type, (
free_space * 10e-10
) AS free_gb, ROUND(
(free_space * 1.0 / size * 1.0) * 100, 2
) AS free_perc FROM logical_drives WHERE device_id = 'C:';
+------+---------+---------------+-----------+
| path | type | free_gb | free_perc |
+------+---------+---------------+-----------+
| C: | Unknown | 214.013345792 | 79.92 |
+------+---------+---------------+-----------+
osquery> SELECT disk_index AS id, hardware_model, manufacturer, serial FROM disk_info;
+----+--------------------------+------------------------+--------+
| id | hardware_model | manufacturer | serial |
+----+--------------------------+------------------------+--------+
| 1 | VMware Virtual disk SCSI | (Standard disk drives) | (none) |
| 0 | VMware Virtual disk SCSI | (Standard disk drives) | (none) |
+----+--------------------------+------------------------+--------+

Disk encryption

osquery> SELECT drive_letter AS device_id, CASE WHEN (conversion_status = 1 AND protection_status = 1) THEN TRUE ELSE FALSE END AS encrypted, encryption_method AS method, percentage_encrypted AS progress FROM bitlocker_info;+-----------+-----------+-------------+----------+
| device_id | encrypted | method | progress |
+-----------+-----------+-------------+----------+
| C: | 1 | XTS_AES_128 | 0 |
| H: | 0 | XTS_AES_128 | 34 |
+-----------+-----------+-------------+----------+

Disk performance

osquery> SELECT * FROM physical_disk_performance 
WHERE name = '0 C:';
name = 0 C:
avg_disk_bytes_per_read = 1048576
avg_disk_bytes_per_write = 6656
avg_disk_read_queue_length = 8
avg_disk_write_queue_length = 5
avg_disk_sec_per_read = 0
avg_disk_sec_per_write = 0
current_disk_queue_length = 0
percent_disk_read_time = 831
percent_disk_write_time = 513
percent_disk_time = 1345
percent_idle_time = 31

Bringing it all together

osquery> WITH performance AS (
SELECT SUBSTR(name, 0, INSTR(name, ' ')) AS id,
TRIM(SUBSTR(name, INSTR(name, ' '), LENGTH(name))) AS name,
physical_disk_performance.*
FROM physical_disk_performance WHERE name != '_Total'
)
SELECT * FROM performance
INNER JOIN logical_drives
ON logical_drives.device_id = performance.name
INNER JOIN disk_info
ON disk_info.disk_index = performance.id;

Mac OS & Linux

Disk usage

osquery> SELECT path, type, ROUND(
(blocks_available * blocks_size * 10e-10), 2
) AS free_gb, ROUND (
(blocks_available * 1.0 / blocks * 1.0) * 100, 2
) AS free_perc FROM mounts WHERE path = '/';
+------+------+---------+-----------+
| path | type | free_gb | free_perc |
+------+------+---------+-----------+
| / | apfs | 52.7 | 5.27 |
+------+------+---------+-----------+

Disk encryption

osquery> SELECT device, path, disk_encryption.type, 
encrypted, encryption_status FROM mounts
INNER JOIN disk_encryption
ON disk_encryption.name = mounts.device
WHERE mounts.path = '/';
+--------------+------+-----------------+-----------+
| device | path | type | encrypted |
+--------------+------+-----------------+-----------+
| /dev/disk1s1 | / | APFS Encryption | 1 |
+--------------+------+-----------------+-----------+

Disk performance

osquery> SELECT SUM(disk_bytes_read * 10e-10) AS disk_gb_read, SUM(disk_bytes_written * 10e-10) AS disk_gb_written FROM processes WHERE on_disk = 1;+----------------+------------------+
| disk_gb_read | disk_gb_written |
+----------------+------------------+
| 1230.873003008 | 592.224878591999 |
+----------------+------------------+

Its all over!

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store