Does anyone know where a good repository of advanced queries

Question

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 lt = 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 lt = 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

fritz · Answer

< 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 ```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 lt = 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 lt = 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 · Answer

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

fritz · Answer

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 slightly smiling face

fritz · Answer

Having said that it appears they broke my logic for html parsing the windows update page but the macos one still works ``` Retrieve the Zoom Update curl result for both mac and windows WITH zoom update html AS SELECT CASE WHEN url = THEN windows WHEN url = THEN darwin END AS platform result FROM curl WHERE url IN 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 lt h2 gt Current Release lt h2 gt 1 lt h2 gt Previous Releases lt h2 gt 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 lt 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 gt 1 lt br 0 WHEN platform = darwin THEN REGEX SPLIT REGEX SPLIT current release lt strong gt 1 lt 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 ```

fritz · Answer

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