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:
|
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>
Browser extension and plugin-related tables:
Tables related to common third-party package managers:
Tables related to containers and cloud instances:
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.
<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).
<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.
<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!