Query to find Inbound / Outbound connections on Mac

Query to find Inbound / Outbound connections on Mac


Query
WITH mac_assets AS (SELECT t.upt_asset_id

                                FROM   upt_asset_tags t

                                WHERE  t.key = 'upt-mac-edr')

 

SELECT DISTINCT pos.upt_asset_id,

                ps.name,

                pos.pid,

                pos.local_address,

                pos.remote_address,

                pos.local_port,

                pos.remote_port,

                CASE

                  WHEN pos.pid = lp.pid THEN 'INBOUND'

                  ELSE 'OUTBOUND'

                END AS directionality

FROM   process_open_sockets pos JOIN mac_assets m ON (pos.upt_asset_id = m.upt_asset_id)

       join processes ps

         ON ( ps.pid = pos.pid

              AND ps.upt_asset_id = pos.upt_asset_id )

       left join listening_ports lp

              ON ( pos.pid = lp.pid

                   AND pos.local_port = lp.port

                   AND pos.upt_asset_id = lp.upt_asset_id )

WHERE  pos.pid <> 0

       AND pos.local_address NOT IN ( '::1', '127.0.0.1' )

       AND pos.local_port <> 0

       AND pos.upt_added = TRUE

       AND ps.upt_added = TRUE

       AND lp.upt_added = TRUE;



    • Related Articles

    • Query to find Processes Running

      Processes running between certain times Query SELECT * FROM windowed_processes p WHERE p.upt_add_time >= <TIMESTAMP> AND p.upt_add_time <= <TIMESTAMP>; Example All processes running between 2018-07-18 2:00 to 2018-08-18 3:00 SELECT * FROM ...

    • Query to find the Process Tree

      Query WITH pstree AS (SELECT 0 AS LEVEL, pid, name, parent, Cast(pid AS TEXT) AS ppid, name AS pparent FROM processes WHERE parent = 0 UNION ALL SELECT LEVEL + 1, t.pid, t.name, t.parent, pstree.ppid || ', ' || Cast(t.pid AS TEXT), pstree.pparent || ...

    • Query: Query for Processes Running on a Set of Machines (and not on Other Machines)

      This article presents a sample Uptycs query to find processes that are running on a set of endpoints but not on a specific set of other endpoints.  TABLE OF CONTENTS No headings available. Use Paragraph Format to add one. In this example we are ...

    • Query to find Processes delta between last 2 weeks

      Query WITH last_week_processes_cmds AS ( SELECT DISTINCT upt_asset_id, name, cmdline, path FROM processes WHERE upt_day >= Cast(Date_format(current_date - interval '14' day, '%Y%m%d') AS INTEGER) AND upt_day < Cast(Date_format(current_date - interval ...

    • Find Realtime queries executed in the last 24 hours

      This article includes the query to find all the real time queries that were executed in the last 24 hours Query WITH queries      AS (SELECT Json_extract_scalar(logs.api_body, '$.query')   AS query, ...