Channels
#general
Title
# general
k
Karl Ackerman
06/19/2020, 12:03 PMDoes anyone know where a good repository of advanced queries for OSQ can be found. I am not so much interested in how to get information from a single table but more interested in folks who are using it to get some more complex queries to work. For example below is a query that will generate the process tree for all running processes on the system, so you can see parent child relationship:
WITH 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
f
fritz
06/20/2020, 3:49 PM@Karl Ackerman there is very little query sharing that happens within the community, I am unsure if this is out of lack of interest, perceived environment-specificity, or just a general resistance to sharing queries as they represent a value-prop for the individuals that craft them.
With that aside, I reformatted your query to it is a little more legible for those who want to try it out:
Copy code
WITH 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;👍 1
k
Karl Ackerman
06/26/2020, 9:39 AMThanks for the formating. If folks are interested Sophos publishes queries for our Intercept X with EDR product. Many will work with OSQ out of the box but others depend on a sophos extension that makes about 90 days of historic data available. Just go to the community site at sophos and select intercept x Early Access Program and then Live Discover Queries. My hope was to find a community of folks already building more advanced queries and to supply some of the interesting work we have done. Currently I'm playing with curl requests to download threat intel from a public site, load it into a virtual table then scan the systems historic data to perform retrospective detections of malicious activity.
f
fritz
06/26/2020, 2:05 PMVery cool Karl! I did a very janky PoC using
curlHaving said that, it appears they broke my logic for html parsing the windows update page but the macos one still works:
Copy code
-- 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;The other item I would mention @Karl Ackerman is that you appear to work with the registry a lot of the time I didn't see it (though you and Andy are likely already aware) but using
MAX(CASE18 Views