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

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 looking to find processes running on 4 machines that crashed during Jan 15-21 time frame, the result set is ordered to show processes that were running on the least number of other (non crashed machines) in the fleet.  


The Uptycs Query

Query 
with all as (

  select path, count(distinct upt_hostname) crash_count, 0 as other_count 

  from processes 

  where upt_hostname in ('machine1', 'machine2', 'machine3', 'machine4') 

  and upt_day between 20200115 and 20200121 

  group by 1

  having count(distinct upt_hostname) = 4 

union

  select path, 0 crash_count, count(distinct upt_hostname) as other_count 

  from processes 

  where upt_hostname not in ('machine1', 'machine2', 'machine3', 'machine4') 

  and upt_day between 20200115 and 20200121 

  group by 1

)  

select path, sum(crash_count) crash_count, sum(other_count) other_count

from all 

group by 1 

order by other_count

The Query Results


    • 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 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 ...

    • 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 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 ...

    • Query to find the number of Osquery restarts in a day for all hosts

      Following query, run on Global database, would provide details on number of osquery restarts in a day (GMT) Query SELECT upt_hostname, Count(0)  FROM processes  WHERE name LIKE 'osqueryd%' AND upt_added  AND upt_day = Cast(Date_format(current_date - ...