Contents

Tutorials

How to Find and Fix CVE-2020–0601 Using Osquery and Kolide

Fritz Ifert-Miller

On Monday, the NSA announced a critical vulnerability (CVE-2020–060) in Windows 10 which allows an attacker to “undermine how Windows verifies cryptographic trust and can enable remote code execution.”

“Exploitation of the vulnerability allows attackers to defeat trusted network connections and deliver executable code while appearing as legitimately trusted entities. Examples where validation of trust may be impacted include:

  • HTTPS connections
  • Signed files and emails
  • Signed executable code launched as user-mode processes”

Understanding which devices have not been patched within your organization is a critical first-step. For users who need cross-platform device visibility, a common solution is osquery.

Osquery is an open-source endpoint agent which allows you to query devices in real-time with SQL as if they were a relational database. You can retrieve lists of installed applications, running processes, listening ports and more.

Finding vulnerable devices

In this case, we can utilize the power of osquery to determine which patches have been installed and whether the CVE-2020–0601 hotfix has been applied. You can use the following query to find devices who lack the hotfix entirely:

SELECT 'true' AS CVE_2020_0601_vulnerable
  WHERE NOT EXISTS
    (SELECT 1
     FROM patches
     WHERE hotfix_id IN (
       'KB4534306',
       'KB4534271',
       'KB4534276',
       'KB4534293',
       'KB4534273',
       'KB4535550',
       'KB4528760'));

While this query is good, we can make it better. We run a slightly different Check within Kolide that is more complicated which you can find at the bottom of this article.

Patching the vulnerability

Now we know which devices have the problem, but we still don’t have a way to fix it.

That’s where Kolide’s Checks feature comes in. Kolide identifies the devices that are vulnerable, and then notifies your end-users automatically via Slack, alerting them of the problem, and giving them self-fix instructions.

When a user fixes an issue, they can click a button in Slack to recheck the device in real-time. No tedious back and forth with the call-center, just resolution.

And there you have it, a way to identify this issue at scale, educate your affected users, and patch the vulnerability, all without lifting a finger.

Not a Kolide customer yet? No problem! You can trial Kolide for free (no payment information required), by signing up here: Kolide Free Trial

Nuances of patch reporting (improving our original query)

After running Windows Update, the hotfix reports back as installed. Unfortunately, however, it technically does not complete the installation until after the device has been restarted.

What follows, is a step-wise walkthrough of crafting a SQL query which accommodates this behavior. If you are not interested in learning about writing SQL, I would recommend ducking out. If you are an osquery power-user buckle up and let’s dive in.

We need to look for the following:

  • Hotfix not installed
  • Computer not rebooted since Hotfix installation

Time of last reboot

Let’s start with determining whether the device has been rebooted since the patch was installed. We can compute the user’s local time of last reboot for a device using the following query:

SELECT
  datetime(time.unix_time - uptime.total_seconds, 'unixepoch') AS last_rebooted
FROM time, uptime;
+---------------------+
| last_rebooted       |
+---------------------+
| 2019-12-13 14:25:11 |
+---------------------+

Time of patch installation

We need to compare the last_rebooted value against the time of the of patch installation. We can see that the patches table includes a column called installed_on, unfortunately for us it is not formatted using the standard YYYYMMDD ISO8601 convention.

SELECT *
FROM patches
WHERE hotfix_id IN (
       'KB4534306',
       'KB4534271',
       'KB4534276',
       'KB4534293',
       'KB4534273',
       'KB4535550',
       'KB4528760');
      csname = LENOVO-THINKPAD
   hotfix_id = KB4534273
     caption = http://support.microsoft.com/?kbid=4534273
 description = Security Update
fix_comments =
installed_by = NT AUTHORITY\SYSTEM
install_date =
installed_on = 1/15/2020

As we can see our times are quite different:

The string 2019–12–13 14:25:11 cannot be compared against 1/15/2020

In order to compare the installed_on time to the last_rebooted time we will need to perform some string operations to get this value into YYYYMMDD.

Typically, we could use a standard SPLIT operator to pull the date apart into 3 separate columns: year, month, day:

WITH
date_value AS
    (SELECT '1/15/2020' AS installed_on),
split_date AS
    (SELECT SPLIT(installed_on, '/', 2) AS year,
            SPLIT(installed_on, '/', 0) AS month,
            SPLIT(installed_on, '/', 1) AS day
     FROM   date_value)
SELECT * FROM split_date;
+------+-------+-----+
| year | month | day |
+------+-------+-----+
| 2020 | 1     | 15  |
+------+-------+-----+

Already, we can see a problem. Microsoft does not adhere to the convention of leading 0‘s for its dates. We will need to prepend some of our months and days with 0 but only the ones that are single digits, otherwise we will get:

WITH
date_value AS
    (SELECT '1/15/2020' AS installed_on),
split_date AS
    (SELECT SPLIT(installed_on, '/', 2) AS year,
            SPLIT(installed_on, '/', 0) AS month,
            SPLIT(installed_on, '/', 1) AS day
     FROM   date_value)
SELECT
  year,
  ('0' || month) AS month,
  ('0' || day) AS day
FROM split_date;
+------+-------+-----+
| year | month | day |
+------+-------+-----+
| 2020 | 01    | 015 |
+------+-------+-----+

Our day column is reporting back as 015 because we prepended an existing 2 digit string. Let’s wrap our CONCAT (||) function in aSUBSTR(substring)so that we can pull only the 2 ending characters from themonthandday` columns:

WITH
date_value AS
    (SELECT '1/15/2020' AS installed_on),
split_date AS
    (SELECT SPLIT(installed_on, '/', 2) AS year,
            SPLIT(installed_on, '/', 0) AS month,
            SPLIT(installed_on, '/', 1) AS day
     FROM   date_value)
SELECT
  year,
  SUBSTR(('0' || month), -2) AS month,
  SUBSTR(('0' || day), -2) AS day
FROM split_date;
+------+-------+-----+
| year | month | day |
+------+-------+-----+
| 2020 | 01    | 15  |
+------+-------+-----+

Great! We can now take our SPLIT columns and reassemble them into a viable ISO 8601 timestamp using the || concatenate function. Between each column we will place a || '-' || and at the end we will add 00:00:01.

* Assuming the install time is the start of the day 00:00:01 is less than perfect and could lead to the occasional false positive. However, we feel it is better to fail safe, rather than relying on a query that may leave machines in an incomplete patched state:

WITH
date_value AS
    (SELECT '1/15/2020' AS installed_on),
split_date AS
    (SELECT SPLIT(installed_on, '/', 2) AS year,
            SPLIT(installed_on, '/', 0) AS month,
            SPLIT(installed_on, '/', 1) AS day
     FROM   date_value)
SELECT
  year
  || '-' ||
  SUBSTR(('0' || month), -2)
  || '-' ||
  SUBSTR(('0' || day), -2)
  || ' ' ||
  '00:00:01' AS install_date_utc
FROM split_date;
+---------------------+
| install_date_utc    |
+---------------------+
| 2020-01-15 00:00:01 |
+---------------------+

Phew! We are almost there! Now we just need to pull the installed_on values dynamically and compare against our last_rebooted query.

Let’s start with pulling in our values dynamically. We will remove our static date_value and query directly from the patches table:

WITH
split_date AS
    (SELECT *,
            SPLIT(installed_on, '/', 2) AS year,
            SPLIT(installed_on, '/', 0) AS month,
            SPLIT(installed_on, '/', 1) AS day
     FROM   patches),
date_reconstructed AS
    (SELECT *,
            year
            || '-' ||
            SUBSTR(('0' || month), -2)
            || '-' ||
            SUBSTR(('0' || day), -2)
            || ' ' ||
            '00:00:01' AS install_date_utc
     FROM split_date)
SELECT * FROM date_reconstructed LIMIT 1;
          csname = LENOVO-THINKPAD
       hotfix_id = KB4534273
         caption = http://support.microsoft.com/?kbid=4534273
     description = Security Update
    fix_comments =
    installed_by = NT AUTHORITY\SYSTEM
    install_date =
    installed_on = 1/15/2020
            year = 2020
           month = 1
             day = 15
install_date_utc = 2020-01-15 00:00:01

To accommodate our two possible vulnerable conditions we will utilize CASE logic to create two boolean conditions:

  • Hotfix not installed
  • Computer not rebooted since Hotfix installation

Let’s begin by adding our reboot query as a CASE boolean column:

WITH
split_date AS
    (SELECT *,
            SPLIT(installed_on, '/', 2) AS year,
            SPLIT(installed_on, '/', 0) AS month,
            SPLIT(installed_on, '/', 1) AS day
     FROM   patches),
date_reconstructed AS
    (SELECT *,
            year
            || '-' ||
            SUBSTR(('0' || month), -2)
            || '-' ||
            SUBSTR(('0' || day), -2)
            || ' ' ||
            '00:00:01' AS install_date_utc
     FROM split_date),
restart_check AS
    (SELECT *,
            CASE
             WHEN
              (SELECT
               datetime(
                time.unix_time - uptime.total_seconds, 'unixepoch')
               FROM time, uptime) > install_date_utc
             THEN 'true'
             ELSE 'false'
            END AS restart_since_install
     FROM date_reconstructed)
SELECT * FROM restart_check LIMIT 1;
               csname = LENOVO-THINKPAD
            hotfix_id = KB4534273
              caption = http://support.microsoft.com/?kbid=4534273
          description = Security Update
         fix_comments =
         installed_by = NT AUTHORITY\SYSTEM
         install_date =
         installed_on = 1/15/2020
                 year = 2020
                month = 1
                  day = 15
     install_date_utc = 2020-01-15 00:00:01
restart_since_install = true

We’re so close! All that is left is adding our very first query, looking for the specific hotfixes. Let’s start by writing the CASE query.

SELECT
   CASE
    WHEN NOT EXISTS
     (SELECT 1
      FROM patches
      WHERE hotfix_id IN (
       'KB4534306',
       'KB4534271',
       'KB4534276',
       'KB4534293',
       'KB4534273',
       'KB4535550',
       'KB4528760'))
    THEN 'true'
    ELSE 'false'
   END AS CVE_2020_0601_vulnerable;
+--------------------------+
| CVE_2020_0601_vulnerable |
+--------------------------+
| false                    |
+--------------------------+

And now we combine them for the final query:

WITH
split_date AS
    (SELECT *,
            SPLIT(installed_on, '/', 2) AS year,
            SPLIT(installed_on, '/', 0) AS month,
            SPLIT(installed_on, '/', 1) AS day
     FROM   patches),
date_reconstructed AS
    (SELECT *,
            year
            || '-' ||
            SUBSTR(('0' || month), -2)
            || '-' ||
            SUBSTR(('0' || day), -2)
            || ' ' ||
            '00:00:01' AS install_date_utc
     FROM split_date),
restart_check AS
    (SELECT *,
            CASE
             WHEN
              (SELECT
               datetime(
                time.unix_time - uptime.total_seconds, 'unixepoch')
               FROM time, uptime) > install_date_utc
             THEN 'true'
             ELSE 'false'
            END AS restart_since_install
     FROM date_reconstructed),
operating_system AS (
     SELECT
       CAST(SPLIT (version, '.', 0) AS integer) AS major,
       CAST(SPLIT (version, '.', 2) AS integer) AS build,
       CAST(SPLIT (version, '.', 3) AS integer) AS patch
     FROM kernel_info),
vulnerable_build AS (
  SELECT *,
   CASE
     WHEN major = 10 AND build = 10240 AND patch < 18453 THEN 'true'
     WHEN major = 10 AND build = 14393 AND patch <  3443 THEN 'true'
     WHEN major = 10 AND build = 16299 AND patch <  1625 THEN 'true'
     WHEN major = 10 AND build = 17134 AND patch <  1246 THEN 'true'
     WHEN major = 10 AND build = 17763 AND patch <   973 THEN 'true'
     WHEN major = 10 AND build = 18362 AND patch <   592 THEN 'true'
     WHEN major = 10 AND build = 18363 AND patch <   592 THEN 'true'
     ELSE 'false'
   END as affected_build
  FROM operating_system),
failing_state AS
    (SELECT CASE WHEN (SELECT 1
                       FROM restart_check
                       WHERE hotfix_id IN (
                         'KB4534306',
                         'KB4534271',
                         'KB4534276',
                         'KB4534293',
                         'KB4534273',
                         'KB4535550',
                         'KB4528760'))
                 THEN 'true'
                 ELSE 'false'
            END AS CVE_2020_0601_patch_installed,
            CASE WHEN (SELECT 1
                       FROM restart_check
                       WHERE hotfix_id IN (
                         'KB4534306',
                         'KB4534271',
                         'KB4534276',
                         'KB4534293',
                         'KB4534273',
                         'KB4535550',
                         'KB4528760')
                 AND restart_since_install = 'false')
                 THEN 'false'
            END AS restart_since_install,
            CASE WHEN (SELECT 1
                       FROM vulnerable_build
                       WHERE affected_build = 'true')
                 THEN 'true'
            END AS affected_build)
SELECT *,
       CASE WHEN (restart_since_install = 'false'
                  OR cve_2020_0601_patch_installed = 'false')
             AND affected_build = 'true'
            THEN 'true'
       END AS vulnerable
FROM failing_state
WHERE vulnerable = 'true'

All that fun with Osquery SQL!

Or you could just use [Kolide])(https://k2.kolide.com/trial/new) and have it written and automatically notifying for you without so much as opening your terminal. ;)

Get started today, with a free trial of Kolide (no payment information or credit card required).

Share this story:

More articles you
might enjoy:

Tutorials
How to Set up Windows File Integrity Monitoring Using Osquery and Kolide
Fritz Ifert-Miller
Deep Dives
Are Your Employees Slack Messages Leaking While Their Screen Is Locked?
Fritz Ifert-Miller
Deep Dives
Zoom Webcam Hijacking — Are Your Users Vulnerable?
Fritz Ifert-Miller
Try Kolide Free
Try Kolide Free