Skip to content
Tags
Tags

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.

Looking for…?

Start with this SQL query

How to query events by hostname?

SELECT * FROM dns_lookup_events WHERE hostname = '<hostname>'

What software is installed on a specific host?

SELECT * FROM apps WHERE hostname = '<hostname>’

What software (with versions) are currently running on this specific host?

SELECT pid, process_name, path, commandLine, state, start_time, parent_process, version_info FROM processes WHERE hostname = '<hostname>'

What OS version is on all hosts?

SELECT host_name, os, os_version, os_flavor, live as status FROM upt_assets;

What hosts have this software installed on them?

SELECT DISTINCT(upt_hostname, name, bundle_version) FROM apps WHERE lower(name) like ‘%%<app_name>%%’

Which assets are online but are not generating events?

SELECT upt_asset_id

FROM upt_asset_activity

WHERE online_at > (Now() - interval '24' hour)

AND upt_asset_id NOT IN

(

SELECT DISTINCT upt_asset_id

FROM upt_events

WHERE upt_day >= cast(date_format(now() - interval '24' hour, '%Y%m%d') AS integer)

AND event_time > (now() - interval '24' hour))

Get count of hostnames from a particular query pack on specific day

SELECT count(upt_hostname) FROM qp_art_ec2data_q_ec2_instance_metadata WHERE upt_day=20210430

Query assets with particular OS, but exclude certain tags

SELECT distinct ua.host_name, ua.os, uat.tag FROM upt_asset_tags uat

JOIN upt_assets ua on uat.upt_asset_id = ua.id

WHERE ua.os='Mac OS X' and uat.tag NOT IN ('all', 'compliance')

ORDER by 1;

In the last 30 days, when was the earliest time we saw software on our system?

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

 

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:

 

Browser extension and plugin-related tables:

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:

 

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.

threat bulletin cta image

Recommended Content