Karl Ackerman
06/19/2020, 12:03 PMfritz
06/20/2020, 3:49 PMWITH RECURSIVE
proc_tree(pid, level, startTime, cmdLine, procName) AS
(
SELECT
p.pid,
0,
p.start_time,
p.cmdLine,
p.name
FROM processes p
-- Get all processes without running parents (root / orphan processes)
WHERE p.parent = 0 OR p.parent NOT IN (SELECT DISTINCT pid FROM processes p2 WHERE p2.start_time <= p.start_time AND pid = p.parent)
UNION ALL
-- Add each process with a parent in proc_tree to the table at the level it is down from a root
SELECT
p.pid,
proc_tree.level + 1,
p.start_time,
p.cmdLine,
p.name
FROM processes p
JOIN proc_tree
ON p.parent = proc_tree.pid
AND proc_tree.startTime <= p.start_time
WHERE p.parent != 0
ORDER BY 2 DESC
)
SELECT
CAST(datetime(pt.startTime,'unixepoch') AS TEXT) processStartTime,
/* Add an indentation for every level the process is from it's root */
substr((CHAR(9658) || CHAR(9658) || CHAR(9658) || CHAR(9658) || CHAR(9658)
|| CHAR(9658) || CHAR(9658) || CHAR(9658) || CHAR(9658) || CHAR(9658)
|| CHAR(9658) || CHAR(9658) || CHAR(9658) || CHAR(9658) || CHAR(9658)
|| CHAR(9658) || CHAR(9658)), 1, level) || ' ' || pt.procName processBranch,
pt.pid,
pt.cmdLine
FROM proc_tree pt;
Karl Ackerman
06/26/2020, 9:39 AMfritz
06/26/2020, 2:05 PMcurl
to get latest versions of software to check for vulnerable versions of Zoom that you might enjoy looking over in terms of horrifying yourself 🙂-- Retrieve the Zoom Update curl result for both mac and windows
WITH
zoom_update_html AS (
SELECT
CASE WHEN url = '<https://support.zoom.us/hc/en-us/articles/201361953-New-Updates-for-Windows>'
THEN 'windows'
WHEN url = '<https://support.zoom.us/hc/en-us/articles/201361963-New-Updates-for-macOS>'
THEN 'darwin'
END AS platform,
result FROM curl WHERE url IN (
'<https://support.zoom.us/hc/en-us/articles/201361963-New-Updates-for-macOS>',
'<https://support.zoom.us/hc/en-us/articles/201361953-New-Updates-for-Windows')>),
-- Regex split the returned html result to return only the block that contains current release data
current_release_version AS (
SELECT
platform,
REGEX_SPLIT((REGEX_SPLIT(result, '<h2>Current Release</h2>', 1)), '<h2>Previous Releases</h2>', 0) AS current_release
FROM zoom_update_html),
-- Regex split the current release block to parse the update download type
download_type AS (
SELECT
platform,
REGEX_SPLIT((REGEX_SPLIT(current_release, 'Download Type: ', 1)), '<br', 0) AS zoom_update_type
FROM current_release_version),
-- Regex split the current release html block to parse the plain-text version string eg. 4.6.9 (190453.104)
version_string_dirty AS (
SELECT platform,
CASE WHEN platform = 'windows'
THEN REGEX_SPLIT((REGEX_SPLIT(current_release, '">', 1)), '<br ', 0)
WHEN platform = 'darwin'
THEN REGEX_SPLIT((REGEX_SPLIT(current_release, '<strong>', 1)), '<br ', 0)
END AS version_dirty FROM current_release_version),
-- Split the plain-text version string into separate pieces to prepare for later concatenation
version_string_split AS (
SELECT platform,
CAST(SPLIT(version_dirty, ".", 0)AS int) AS bsv_major,
CAST(SPLIT(version_dirty, ".", 1)AS int) AS bsv_minor,
CAST(SPLIT(version_dirty, ".", 2)AS int) AS bsv_patch_cond,
CAST(SPLIT(version_dirty, "(", 1)AS int) AS bsv_patch,
CAST(SPLIT(version_dirty, ".", 3)AS int) AS bsv_build
FROM version_string_dirty),
-- Concatenate the version split strings to match the osquery returned semver
version_clean AS (
SELECT
platform,
(bsv_major || '.' || bsv_minor || '.' || bsv_patch || '.' || bsv_build) AS latest_zoom_update
FROM version_string_split),
-- Join the version information to the download_type information
zoom_update_info AS (
SELECT * FROM version_clean, download_type USING(platform))
SELECT * FROM zoom_update_info;
MAX(CASE
to pivot can be really helpful when you want to prettify your EAV style output: https://blog.kolide.com/manipulating-plist-and-registry-output-in-osquery-ad98b067c574