Monitoring hard drives (HDD) with Osquery

There are a number of really useful tables within Osquery to determine information about a systems hard drive. Not least the availability of disk space but the disks encryption status and even some performance metrics.

One thing to bear in mind is that there is no common hard drive table within Osquery. Each operating system platform has its own distinct tables. These tables also need to be joined together in most instances to get a full picture.

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 |
+-----------+---------+---------+------+

You can also express the available disk space as a percentage. Just make sure to multiply the free_space column by 1.0 . Which converts the field to a decimal so that it can be correctly divided. Otherwise, you’ll just end up with a zero.

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 |
+------+---------+---------------+-----------+

For more information around the make and model of the drives the disk_info table is what you’ll need to query:

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) |
+----+--------------------------+------------------------+--------+

You’ll notice however, that this table only provides you with a disk_index which isn't directly join-able with the logical_drives table as this uses the drive name e.g. C:. As there is no foreign key available. What you can do instead is join together the physical_disk_performance to link the two tables together which I’ll show you how to do at the bottom of this section.

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

One thing to note with this table is that the name column is the disk index followed by the disk name. Usually 0 C: , which will be your primary disk.

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!

Real-time security and compliance delivered.

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