Title
#macos
Seán O'Halloran

Seán O'Halloran

02/07/2020, 5:41 PM
@fritz Hmm, no change on unresponsiveness with the new query. It must be something else causing a timeout, maybe? It actually makes sense that maybe the 8 machines that were returning results did so because they hit a symlink loop before timing out. I thought maybe the query was blacklisted as @zwass suggested so I set up an
osquery_schedule
query and it produced the following (for one of the null-result endpoints):
{
  "@host_identifier": "<redacted>",
  "name": "osquery_schedule",
  "created": "2020-02-07T17:10:23.769858",
  "action": "added",
  "@timestamp": "2020-02-07T17:00:49",
  "@version": 1,
  "log_type": "result",
  "columns": {
    "system_time": "1627",
    "interval": "900",
    "blacklisted": "0",
    "output_size": "",
    "average_memory": "60030",
    "last_executed": "1581094836",
    "avg_user_time": "11",
    "executions": "92",
    "wall_time": "4",
    "query": "WITH mounted_volumes AS (\\x0D\\x0A       SELECT path \\x0D\\x0A       FROM mounts \\x0D\\x0A       LEFT JOIN block_devices ON mounts.device = block_devices.name \\x0D\\x0A       WHERE block_devices.type=\"USB\" AND path LIKE '/Volumes/%')\\x0D\\x0A   SELECT hash.path,\\x0D\\x0A          hash.md5,\\x0D\\x0A          file.filename,\\x0D\\x0A          file.size,\\x0D\\x0A          file.block_size,\\x0D\\x0A          file.type,\\x0D\\x0A          file.uid,\\x0D\\x0A          file.inode\\x0D\\x0A   FROM file\\x0D\\x0A       LEFT JOIN hash USING (path) \\x0D\\x0A   WHERE file.path IN (\\x0D\\x0A      SELECT file.path \\x0D\\x0A      FROM file, mounted_volumes \\x0D\\x0A      WHERE file.path LIKE mounted_volumes.path || '/%' OR file.path LIKE mounted_volumes.path || '/%/%' OR file.path LIKE mounted_volumes.path || '/%/%/%');",
    "avg_system_time": "17",
    "user_time": "1065",
    "name": "files_on_usb"
  }
}
I also have a far simpler query that just tries to use the
file
table to get the root of the Downloads folder, and I haven’t received any results for this either:
{
  "@host_identifier": "<redacted>",
  "name": "osquery_schedule",
  "created": "2020-02-07T17:10:23.769858",
  "action": "added",
  "@timestamp": "2020-02-07T17:00:49",
  "@version": 1,
  "log_type": "result",
  "columns": {
    "system_time": "127",
    "interval": "900",
    "blacklisted": "0",
    "output_size": "",
    "average_memory": "87243",
    "last_executed": "1581093928",
    "avg_user_time": "0",
    "executions": "90",
    "wall_time": "1",
    "query": "SELECT path,btime FROM file WHERE path like \"/Users/%/Downloads/%\";",
    "avg_system_time": "1",
    "user_time": "77",
    "name": "downloads_folder"
  }
}
zwass

zwass

02/07/2020, 5:43 PM
Are you able to query either of these from osqueryi on those machines or a live query from something like Fleet?
Seán O'Halloran

Seán O'Halloran

02/07/2020, 5:48 PM
So I get the results as expected when running the query via
osqueryi
. We use Doorman for orchestration. But it seems that with output_size=“” it’s the query that is failing midway
zwass

zwass

02/07/2020, 6:14 PM
Been meaning to look into it, but I think
output_size
may have a bug currently. Do you get a non-null entry from the machines that do return results?
6:14 PM
Is it possible that osqueryd is running as a user without sufficient perms?
Seán O'Halloran

Seán O'Halloran

02/07/2020, 6:24 PM
hmm that occurred to me, but osqueryd gets deployed by Jamf, which should install everything as root:
$ ps aux | grep osqueryd
root              8115   0.0  0.1  6029964  43564   ??  SN    5:14PM   0:14.69 /usr/local/bin/osqueryd  
root              8114   0.0  0.0  5955848  10692   ??  SNs   5:14PM   0:01.68 /usr/local/bin/osqueryd --flagfile /private/var/osquery/osquery.flags
6:37 PM
as to your other question: looking across my whole fleet it seems to be a binary: either machines store an output_size in their osquery_schedules table, or they don’t. Ooh but this is interesting — all the ones that don’t seem to be the ones that I tagged as a test group for the two queries I’ve been discussing
6:38 PM
Is it possible that I’ve broken something while recursing through the filesystem?
5:58 PM
@zwass turns out we needed to explicitly allow “Full Disk Access” for osqueryd on Catalina. Seems to have done the trick
f

fritz

02/14/2020, 6:05 PM
@Seán O'Halloran Ah yeah, sorry I didn't think of mentioning that. Catalina changed permissions for most of the
/Users/%
directories. We have explicit instructions for all of our SaaS users to grant the agent extended permissions to address this issue.
zwass

zwass

02/14/2020, 6:54 PM
Good find. This IIRC is something that can be managed automatically if you have MDM set up.
Seán O'Halloran

Seán O'Halloran

02/14/2020, 6:55 PM
Yep, we deployed a configuration profile via Jamf that gives osqueryd full permissions