Title
#macos
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
<http://osqueryd.INFO|osqueryd.INFO>
, but never find any results, even though I know they should. Any idea how to even go about trouble-shooting this?
zwass

zwass

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

fritz

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
file
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
LIKE
statements which can take a longer time to return results by instead doing something like this:
SELECT hash.path,
       hash.md5,
       file.size,
       file.block_size,
       file.type,
       file.uid,
       file.inode
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
mdfind
table keying off of some metadata attribute.
4:08 PM
Unfortunately, even though there is a
file.symlink
column, you cannot simply:
AND file.symlink = 0
to avoid the loops.