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.
Windows
Kicking off with Windows.
Basic disk information
In windows the primary table you’ll want to use to grab a system disk information is the logical_drives table. Which will give you the Windows drive letter and basic disk stats including the primary drive marker (boot partition).
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
You can grab a lot of information from the bitlocker_info table. Including the progress of the encryption task itself. You’ll need to ensure both the conversion_status and protection_status are both 1 .If you want to ensure the disk is encrypted. During a disk encryption task the conversion_status will be 2. You can also express whether the disk is fully encrypted as a boolean value using the CASE clause within your SQLite query.
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
The last table on this list. Which is actually really useful for production servers and disk intensive applications. Osquery provides access to the Windows disk management utility metrics. So that you can actually get access to disk performance metrics via the physical_disk_performance table.
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
The last thing I wanted to touch on with the Windows tables — when cross joining other disk tables . Is that the only table at present (as of Osquery 4.6.0 ), the physical_disk_performance table has both the disk index and the disk name. Which the other tables use to identify the hard drives themselves. You can use the WITH clause to grab both these identifiers, for joining together subsequent disk based tables like so:
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
Now that we’ve got Windows under our belt. Mac OS and Linux are more or less the same unless otherwise, stated.
Disk usage
First things first, just like with the Windows logical_drives table. For UNIX systems basic disk information resides within the mounts table. The only thing you need to do here, versus the Windows table is multiply the available disk blocks with the blocks_size field. In order to get the total size in bytes. Which can then be further transformed to get the size in gigabytes.
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
The disk_encryption table joined with the mounts we used above will let you see the encryption status of the root partition. However, the disk_encryption table by itself will give you the encryption status for each partition.
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
Unfortunately as of 4.6.0 there are no direct disk performance tables for Mac OSX and Linux for Osquery. With the exception of docker_containers and the processes table. Which does provide the disk_bytes_written and disk_bytes_read columns. Which could be polled at a set interval to determine a rough read and write 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!
Hopefully that’s given you a quick dive into how to query hard drive information with Osquery. We’ll be following up on this topic in the near future with Linux devices. However, that’s all for now. Please feel free to get in touch if you have any questions.
