Channels
  • Seán O'Halloran

    Seán O'Halloran

    2 years ago
    @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

    2 years ago
    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

    2 years ago
    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

    2 years ago
    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?
    Is it possible that osqueryd is running as a user without sufficient perms?
  • Seán O'Halloran

    Seán O'Halloran

    2 years ago
    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

    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
    Is it possible that I’ve broken something while recursing through the filesystem?
    @User turns out we needed to explicitly allow “Full Disk Access” for osqueryd on Catalina. Seems to have done the trick
  • f

    fritz

    2 years ago
    @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

    2 years ago
    Good find. This IIRC is something that can be managed automatically if you have MDM set up.