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 ...
    • Windows Disk Usage query

      The article includes query to fetch the Windows Disk usage: Query SELCET DISTINCT                 upt_hostname,                 upt_time,                 device_id,                 size,                 free_space,                 size - free_space ...