Name: Behavioral_Reverse_Shell
Find shell processes that have open sockets and no open files or TTY (https://clo.ng/blog/osquery_reverse_shell/): Behavioral detection for potential reverse shells

Query:

SELECT DISTINCT(processes.pid), processes.parent, processes.name, processes.path, \
        processes.cmdline, processes.cwd, processes.root, processes.uid, processes.gid, \
        processes.start_time, process_open_sockets.remote_address, process_open_sockets.remote_port, \
        (SELECT cmdline FROM processes AS parent_cmdline WHERE pid=processes.parent) AS parent_cmdline \
        FROM processes JOIN process_open_sockets USING (pid) \
        LEFT OUTER JOIN process_open_files \
        ON processes.pid = process_open_files.pid \
        WHERE (name='sh' OR name='bash') \
        AND process_open_files.pid IS NULL;

Additional Query Info:
Version: 2.8.0
Platform: darwin
Interval: 3600

JSON:

{
  "queries": {
    "Behavioral_Reverse_Shell": {
      "query" : "SELECT DISTINCT(processes.pid), processes.parent, processes.name, processes.path, \
        processes.cmdline, processes.cwd, processes.root, processes.uid, processes.gid, \
        processes.start_time, process_open_sockets.remote_address, process_open_sockets.remote_port, \
        (SELECT cmdline FROM processes AS parent_cmdline WHERE pid=processes.parent) AS parent_cmdline \
        FROM processes JOIN process_open_sockets USING (pid) \
        LEFT OUTER JOIN process_open_files \
        ON processes.pid = process_open_files.pid \
        WHERE (name='sh' OR name='bash') \
        AND process_open_files.pid IS NULL;",
      "interval" : "3600",
      "platform" : "darwin",
      "version": "2.8.0",
      "description" : "Find shell processes that have open sockets and no open files or TTY (https://clo.ng/blog/osquery_reverse_shell/)",
      "value" : "Behavioral detection for potential reverse shells"
    }
  }
}