Using Kolide + osquery to find and fix critical Windows Crypto Vulnerability
On Monday, the NSA announced a critical vulnerability (CVE-2020–0601) 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.local_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:
2019–12–13 14:25:11 can not 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 a SUBSTR (substring)so that we can pull only the 2 ending characters from the month and 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
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.local_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.local_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 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).
About the Author
Fritz Ifert-Miller is the UX designer for Kolide. When he is not working in Sketch, he can be found cursing Windows laptops and crafting SQL queries.
