https://github.com/osquery/osquery logo
Join Slack
Powered by
Does anyone know where a good repository of advanc...
# general
k
Does 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
@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
Thanks 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
Very cool Karl! I did a very janky PoC using 
curl
to get latest versions of software to check for vulnerable versions of Zoom that you might enjoy looking over in terms of horrifying yourself 🙂
Having 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(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
33 Views
Open in Slack
PreviousNext
Powered by