Seán O'Halloran

Seán O'Halloran

02/04/2020, 10:05 PM
I’m trying to create a query that returns the MD5 hash of any files found on a mounted volume other than the internal ones. I came up with this, which seems to work perfectly on 9/18 of the machines I deployed to:
SELECT hash.path, file.btime, file.size, file.block_size, file.type, file.uid, file.inode, hash.md5 FROM file LEFT JOIN hash ON hash.path = file.path WHERE file.path LIKE "/Volumes/%%" AND file.path NOT LIKE "/Volumes/Macintosh%" AND file.path NOT LIKE "/Volumes/Recovery%" AND hash.path = file.path AND size>0;
The other half of the machines run the query locally, judging by entires in
, but never find any results, even though I know they should. Any idea how to even go about trouble-shooting this?


02/04/2020, 10:15 PM
Is the watchdog killing the query? Can you query the
table to see whether it is blacklisted?


02/05/2020, 3:39 PM
I agree with @zwass this definitely sounds like a query timeout issue or also very likely a symlink loop error. When recursively querying the
table the results will stop being returned as soon as you hit a symlink loop. You can determine if this is the case by running the query locally (on one of the null result devices) in an osqueryi instance that you start in verbose mode.
3:50 PM
In the event it was a timeout issue I would recommend reducing the usage of
statements which can take a longer time to return results by instead doing something like this:
SELECT hash.path,
FROM file
    LEFT JOIN hash USING (path)
WHERE file.path LIKE '/Volumes/%%'
AND SPLIT(file.path, '/', 1) NOT IN ('Macintosh HD', 'Recovery', 'BOOTCAMP')
AND file.size > 0
3:54 PM
@Seán O'Halloran ^^ Having said that, when I run this query, (and your original query) I get symlink loop errors, specifically, on paths that are within a recursive Bootcamp volume. For example:
/Volumes/Macintosh HD/Volumes/BOOTCAMP/$Recycle.Bin/
It might be worth investigating whether you can get the desired files instead via the
table keying off of some metadata attribute.
4:08 PM
Unfortunately, even though there is a
column, you cannot simply:
AND file.symlink = 0
to avoid the loops.