Osquery tutorial: Gathering software inventory
Gathering software inventory is an important part of security and systems management. There’s a good reason software inventory is No. 2 in the list of CIS Critical Controls!
Software inventory can be easier said than done, as there are more and more sources for software on our workstations and servers. Regular applications, app stores, browser extensions, third-party package managers ... getting a thorough understanding of what is installed requires looking at many different sources of information.
Once you know the tables osquery has for software inventory, you will be able to automate the collection of that data, so it is always available to you. Next time you hear about a critical vulnerability on a Chrome extension, or you notice malware targeting a specific version of a PDF reader in your environment, you’ll be able to instantly see which assets are at risk.
Knowing which software repositories are configured also allows you to detect unauthorized repositories.
Below you'll find multiple tables available in osquery that will allow you to manage software inventory, from the standard package management software packages to browser extensions and much more.
Tables to gather inventory from first-party package managers and built-in OS application installs:
apps: Applications installed on macOS
apt_sources: List of APT repositories
deb_packages: .deb packages (Debian, Ubuntu, etc.)
pkg_packages: FreeBSD pkg packages
portage_packages: Portage packages (Gentoo)
programs: Applications installed on Windows, typically shown in “Add/Remove Programs”
rpm_packages: .rpm packages (RedHat, CentOS, etc.)
yum_sources: List of Yum repositories
Browser extension and plugin-related tables:
browser_plugins: All C/NPAPI browser plugin details for all users
chrome_extensions: Chrome extensions, which can be supplemented with
chrome_extensions_content_scriptsto see the actual content scripts in those extensions
firefox_addons: Firefox extensions, web apps, and add-ons
ie_extensions: Internet Explorer extensions
opera_extensions: Opera extensions
safari_extensions: Safari extensions
Tables related to common third-party package managers:
chocolatey_packages: All packages installed using the Windows package manager Chocolatey (similar to Homebrew on Mac)
homebrew_packages: Homebrew packages, which are super popular on Mac and need to be tracked if we want to be able to deal with vulnerabilities
npm_packages: Packages installed using the popular Node package manager npm
atom_packages: Atom (the text editor) packages installed
python_packages: Python packages, such as those installed with PyPI, the Python package manager
Tables related to containers:
docker_images: Information about running containers and their images
For each of these tables you find valuable, you will need to select information.
For example, here’s how you gather the name and version of Debian packages:
SELECT name, version FROM deb_packages;
And here’s how you can track the package repositories on a Debian/Ubuntu system:
SELECT source, base_uri, release, version, maintainer, components, architectures FROM apt_sources;
Some of these tables report per-user data. You will find this common with browser-related tables, such as the
firefox_addons table. These tables need to be joined to the users table, so you can know which user profile has the add-on installed. The following query extracts valuable information about Firefox add-ons:
SELECT username, name, identifier, version, creator, active FROM users JOIN firefox_addons USING (uid);
Another way to join tables, demonstrated here with the
SELECT * FROM chrome_extensions WHERE chrome_extensions.uid IN (SELECT uid FROM users);
By using all of these tables—by configuring queries for them in your query packs that get executed regularly—you will have a complete picture of the software installed on your systems, including browser extensions and software installed by third-party package managers.
Benefits and compliance requirements
Being able to track installed software is extremely important. It is useful to detect unauthorized software, in more locked-down environments, but it can also be useful to hunt down known vulnerable versions of software. By having the full picture for all machines in your environment, you will be able to better understand the attack surface of your systems, and see if some specific packages could benefit from being managed by IT. This allows updates to be enforced, rather than letting end-users deal with the trouble of updates on their own.
Software inventory is the second of the CIS Critical Controls, but it's also an important part of PCI DSS. Requirement 2.4 states that software on systems in scope for PCI DSS must be inventoried and kept up to date. Other standards, like HIPAA, require risk assessments to be performed, which is facilitated by the existence of a good inventory of hardware and software.
With osquery, you can not only gather the inventory, but do it automatically, and leverage the data to improve the security of your environment.