Query to find Processes delta between last 2 weeks

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 '7' day, '%Y%m%d') AS  INTEGER)

                AND             upt_added = TRUE 

                AND             name IS NOT NULL 

                AND             cmdline IS NOT NULL 

                AND             path IS NOT NULL), 



current_week_processes_cmds AS 

( 

                SELECT DISTINCT upt_asset_id, 

                                name, 

                                cmdline, 

                                path 

                FROM            processes 

                WHERE           upt_day >= Cast(Date_format(current_date - interval '7' day, '%Y%m%d') AS INTEGER)

                AND             upt_day < Cast(Date_format(current_date, '%Y%m%d') AS                     INTEGER)

                AND             upt_added = TRUE 

                AND             name IS NOT NULL 

                AND             cmdline IS NOT NULL 

                AND             path IS NOT NULL) 

SELECT * 

FROM   current_week_processes_cmds 

EXCEPT DISTINCT 

SELECT * 

FROM   last_week_processes_cmds;



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

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

    • 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 the Average CPU Load for the last 1 Hr.

      Query SELECT upt_asset_id, Avg((100 - (Cast(cpu_idle AS DOUBLE)/Cast(cpu_total AS DOUBLE))*100)) AS avg_busy_percent , Avg((Cast(cpu_idle AS DOUBLE)/Cast(cpu_total AS DOUBLE))*100) AS idle_percent FROM upt_metrics WHERE upt_time >= localtimestamp - ...

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