SELECT t.unix_time AS query_time,
       pos.family,
       h.md5,
       h.sha1,
       h.sha256,
       h.directory,
       ltrim(pos.local_address, ':f') AS src_connection_ip,
       pos.local_port AS src_connection_port,
       pos.remote_port AS dest_connection_port,
       ltrim(remote_address, ':f') AS dest_connection_ip,
       name,
       p.path AS file_path,
       cmdline,
       pos.protocol
FROM process_open_sockets AS pos
JOIN processes AS p ON p.pid=pos.pid
LEFT JOIN time AS t
LEFT JOIN
  (SELECT *
   FROM listening_ports) AS lp ON lp.port=pos.local_port
AND lp.protocol=pos.protocol
LEFT JOIN hash AS h ON h.path=p.path
WHERE NOT remote_address=''
  AND NOT remote_address='::'
  AND NOT remote_address='0.0.0.0'
  AND NOT remote_address='127.0.0.1'
  AND port IS NULL;