OSQUERY Credit: https://osquery.io
Using Osquery to explore your system
Ever-curious David Bolton shows how to use the Osquery application to view your system via a series of SQL select queries.
OUR EXPERT
David Bolton is on a quest to leave no byte unturned on his computers, so has installed yet another utility to document the insides of his Ubuntu system with SQL.
Put simply, Osquery is software that enables you to run SQL queries to P provide information about your system. With Osquery, SQL tables represent abstract concepts such as running processes, loaded kernel modules, open network connections, browser plugins, hardware events or file hashes.
The idea is that rather than running lots of different utilities to find out things about your system, you instead run an SQL query on one of the tables. Behind the scenes, Osquery has mapped the state of your system into lots of different tables.
How many tables? Well https://osquery.io/schema/5.12.1/ lets you select your OS type (Linux, Mac and Windows) and shows a clickable list. For Linux, there are 154 tables. A significant proportion of these are tied into software you have installed, so there are tables for Chrome, Firefox, Docker, npm packages and quite a few more.
Just click on a table name in the list to see all the fields. When you want to inspect a concept, you ‘select’ the data, and the associated OS APIs are called in real time.
Ironically, the query select * from cpu_info; returns nothing on our system because it’s running in a virtual machine, although cpuid does. Other queries, such as select * from deb_packages; , return many rows. In that case, you might find select count(*) from deb_ packages; more useful.
To see all the tables, use the Osquery command tables . You can then follow it with the command .schema tablename to see the fields for the specified table. This produces the SQL create table for the specified table. Here, for example, is what .schema cupid produces:
CREATE TABLE cpuid( `feature` TEXT, `value` TEXT, `output_register` TEXT, `output_bit` INTEGER, `input_eax` TEXT);
QUICK TIP
Some tables have a lot of rows. Use the where clause to restrict the number of rows returned or count(*) to return a count of how many there are. The query select count(*) from deb_packages; returns 1861 on our system.