Osquery uses basic SQL commands to leverage a relational data-model to describe a device.

osquery

Hi all. Fleet query for hash table not working in Desktop, Downloads, Documents  directories. Is this normal?

SELECT md5 FROM hash WHERE path='/Users/testuser/del.zip'; -&gt;&gt; OK
SELECT md5 FROM hash WHERE path='/Users/testuser/Documents/del.zip'; -&gt;&gt; NO

image.png

This query is also not working. It extracts the directory but not the hash value.

maybe try adding something like `and f.type = 'regular'` ? or handling nulls by using COALESCE() or LEFT JOIN to see if this is an actual bug or not?

also, i believe double %% is meant for traversals and you may want just `f.path LIKE '/Users/%/Downlaods/%.zip`

Is this a macOS host? Those specific directories are making me think the agent doesn't have permission to look inside them

that is a good callout <@U074CDF5TNZ>, <@U075BM9JBK8> out of curiousity does this query yield a different set of results?
```SELECT  DISTINCT path, sha256 FROM mdls JOIN hash USING(path) WHERE path LIKE '/Users/%/Downloads/%.zip';```

Hi <@UDYPXBNTG> The query also not returning any hash. Just the path.

```SELECT f.path, h.sha256 FROM file f JOIN hash h ON f.path=h.path WHERE f.path='/Users/testuser/Desktop/file' and f.type = 'regular';```
This also returns the same results. Just file path with no hash values. I also suspect Dale's explanation. As per my understanding, osquery is installed on system levels. Then, why it would not able to access user's directories. There is no error output for that.

It works when I run locally the osquery from the machine.

It turns out that orbit required Full Disk Access to be turned-on on macOS. Looking for MDM policy that will automatically deploy this.

Hey <@U075BM9JBK8> were you able to find this? I'm trying to write one now but running into some issues