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

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

GROUP BY upt_hostname 

ORDER BY 2 DESC


Note: The value of interval parameter can be changed based you which day you would want to query the data for.
            Also make sure cut and paste doesn't result in single quotes replaced by some other quotes





    

    • Related Articles
      • 

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

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