Constructing SQL Queries for Asset Management: Osquery Tutorial

In this post, we will give you tools to understand the software assets running on your system using osquery. A previous post covers parts of this topic of gathering software asset inventory, please check it out if you are interested in learning more!

This tutorial builds on the discussion from the recent webinar on software asset management and consists of three parts:

1. First, a table of SQL queries to use as building blocks in your own Uptycs deployment. This table bridges the gap between knowing in plain English what you are looking to accomplish, and connecting that description with a targeted SQL query that will get you to that answer.
2. Second, a breakdown of tables in osquery to use when performing software asset management.
3. And third, a walkthrough of applying these queries to gain visibility into applications running on your systems.

1) See below for a list of queries to build into your own SQL tool set.

Many of these can be used as building blocks for running queries in your system. Tweak them as you see fit to address the assets and questions within your unique environment.

These queries have been optimized for the Uptycs platform and the Uptycs osquery-based agent. If you are using a schema different from the Uptycs schema, please modify the queries as needed to be relevant to your own.

The below list of open source osquery tables are useful for tailoring these queries across different platforms. For example, let’s look deeper at the query from above for seeing what software is installed on a specific host:

<pre><code class="language-sql"> select * from apps where hostname = '<hostname>’ </code></pre>

The query above is looking at a macOS host. To investigate a Windows system we would need to update “apps” to “programs” as shown below.

<pre><code class="language-sql">select * from programs where hostname = '<hostname>’ </code></pre>

2) Tables from the open source osquery schema 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.) and can be used with rpm_package_files to determine installed rpm packages on the host system.
• yum_sources: List of Yum repositories
• Package_install_history: macOS package install history

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_scripts to see the actual content scripts in those extensions
• firefox_addons: Firefox extensions, web apps, and add-ons
• ie_extensions: Internet Explorer extensions
• safari_extensions: Safari extensions

Tables related to common third-party package managers:

• atom_packages: Atom (the text editor) packages installed
• 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
• python_packages: Python packages, such as those installed with PyPI, the Python package manager

Tables related to containers and cloud instances:

• docker_containers and docker_images: Information about running containers and their images

• docker_info: Docker system information

• docker_container_processes: Information about Docker container processes

• ec2_instance_metadata: information about ec2 instance metadata

• lxd_images: information about lxd images

3) Applying SQL queries to a use case

Now let's integrate the above SQL queries and tables into a use case:

A new CVE is released for software that your company currently uses on most macOS devices. This new disclosure has a Critical CVSS score and you have been asked to understand the scope of the work to remediate this item and to prioritize certain asset groups for prioritized remediation.

Let’s break this use case down into three actions. We will be using the sections above to build our queries and prioritize our software assets.

1. Baseline where the software is present in your environment. We have a fleet of macOS laptops and servers that our employees use to run this software. Lets run a query to see how many installations there are in our environment and what those versions are.

<pre><code class="language-sql"> Select distinct(upt_hostname, name) from apps where lower(name) like ‘%%<app_name>%%’ </code></pre>

Results and breakdown: The power of osquery is that we can ask these questions to >100,000 assets in real-time and get an immediate response, with no impact on production performance. This query uses the apps table to query all our macOS assets and return back the list of hostnames (upt_hostname), with the software name (name) and the version number (bundle_version). 

1. Tag assets into groups by priority and risk. One useful feature at Uptycs is that we can add a tag to each asset to say that a certain software is present on these assets or tag high priority assets to be remediated faster. This alleviates a pain point in our future remediation cycles, as we track and run updates on pre-tagged assets. 

<pre><code class="language-sql"> select 'software_name-tag’' as tag from apps where lower(name) like '%%<app_name>%%' </code></pre>

Results and Breakdown: In the Uptycs UI, we can configure this tag rule to run automatically. What this means is that as our environment changes over time we don’t have to manually track where all the software is installed. Instead, we can use this query to immediately parse out which assets have the targeted software.

Tagging becomes a powerful tool for prioritizing assets, allowing teams to break down a complicated environment into bite size pieces based around geolocation, data sensitivity, operational performance, or anything specific to your environment.

1. Run a query to monitor for new software installations. From the principle of least privilege and keeping our assets to hosting only needed software, we can go the extra mile here to track when particular assets add new software. Reducing our attack surface goes a long way in mitigating potential points of access for threat actors. If we see that certain assets are becoming bloated with unused software, this becomes a prime opportunity to reduce your attack surface and reconfigure a software download policy or just evaluate the software configured for certain asset groups.

<pre><code class="language-sql">select min(upt_time) as min_time, upt_hostname, name from apps where upt_day >= CAST(date_format(localtimestamp - INTERVAL '30' DAY, '%Y%m%d') AS INTEGER) and upt_hostname = 'hostname' group by upt_hostname, name order by upt_hostname, name</code></pre >

Results and Breakdown: This query looks at our macOS fleet and returns the software applications observed in the apps table within the interval of the last 30 days. The results are then presented with the earliest point in time that software was observed in the last 30 days. If you have a table capturing install date this query could be used to extend beyond the last 30 days. Using this information we can go beyond the initial use case to track recent software downloads (approved or unapproved) and potentially harden your assets.

Through investigating our environment with these queries, we now have a clearer picture of which assets require the patch applied ASAP, which assets can afford to be part of the standard remediation cycle, and what assets may be better serviced by fully removing the software as they are not needed. If you want to learn more about Uptycs comprehensive operational monitoring and how it will integrate with your assets, please reach out!

To Learn About The Latest Threats, Check Out Our Threat Research Bulletin Below.