I m trying to create a query that returns the MD5 hash of an osquery #macos

I’m trying to create a query that returns the MD5 ...

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:

Copy code

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

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?

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.

fritz

02/05/2020, 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:

Copy code

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

fritz

02/05/2020, 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.

fritz

02/05/2020, 4:08 PM

Unfortunately, even though there is a

file.symlink

column, you cannot simply:

AND file.symlink = 0

to avoid the loops.

3 Views

Open in Slack

Previous Next