Restarts in osquery

Query to Retrieve Asset Restart Counts and Reasons for Specific Time Intervals / a specific day

This SQL query is designed to retrieve the number of restarts by assets and their reasons for restarts within a specified time interval. The query allows you to choose the date or time interval by replacing the {required_date} placeholder with your desired date or interval.

SQL Query Overview

  1. restart_groups CTE:

    • Purpose: Group and count restarts by asset and reason within the specified time interval.
    • Key Operations:
      • upt_asset_id: Groups the data by asset.
      • regexp_replace(restart_reason, '(\\d+)', ''): Removes any numeric characters from the restart_reason to standardize the reasons.
      • COUNT(*) AS restartCounts: Counts the number of restarts.
      • MIN(upt_hostname) AS hostname: Retrieves the minimum hostname for the asset.
      • MAX(upt_time) AS lastOccurence: Retrieves the latest occurrence time of a restart for the asset.

  2. Final SELECT Statement:

    • Purpose: Aggregates the restart counts, identifies the reason for the maximum number of restarts, and retrieves the last occurrence time for each asset.
    • Joins:
      • Joins the restart_groups CTE with another subquery (max_counts) that calculates the maximum number of restarts for each asset.
    • Aggregations:
      • SUM(rg.restartCounts) AS totalRestarts: Sums up the total restarts for each asset.
      • MAX(CASE WHEN rg.restartCounts = max_counts.max_count THEN rg.restart_reason END) AS reason_for_max_restarts: Identifies the reason associated with the maximum number of restarts.
      • MAX(rg.lastOccurence) AS lastOccurence: Retrieves the latest restart occurrence time for each asset.

How to Use the Query

  • To choose a specific date, replace {required_date} in the WHERE clause with your desired date string (e.g., '2024-08-22').
  1.  
  2. WITH restart_groups AS (
  3.   SELECT
  4.     upt_asset_id,
  5.     regexp_replace(restart_reason, '(\\d+)', '') AS restart_reason,
  6.     COUNT(*) AS restartCounts,
  7.     MIN(upt_hostname) AS hostname,
  8.     MAX(upt_time) AS lastOccurence
  9.   FROM
  10.     upt_osquery_diagnostic_logs
  11.   WHERE
  12.     upt_day = {required_date}
  13.   GROUP BY
  14.     GROUPING SETS (upt_asset_id),
  15.     (
  16.       upt_asset_id,
  17.       regexp_replace(restart_reason, '(\\d+)', '')
  18.     )
  19. ),
  20. asset_filter AS (
  21.   SELECT
  22.     upt_asset_id
  23.   FROM
  24.     upt_assets
  25.   WHERE
  26.     json_array_contains(CAST(tags AS JSON), 'asset-group=assets') -- You can use the required  asset-groups here
  27. )
  28. SELECT
  29.   rg.upt_asset_id AS asset_id,
  30.   MIN(rg.hostname) AS hostname,
  31.   SUM(rg.restartCounts) AS totalRestarts,
  32.   MAX(
  33.     CASE
  34.       WHEN rg.restartCounts = max_counts.max_count THEN rg.restart_reason
  35.     END
  36.   ) AS reason_for_max_restarts,
  37.   MAX(rg.lastOccurence) AS lastOccurence
  38. FROM
  39.   restart_groups AS rg
  40. JOIN (
  41.   SELECT
  42.     upt_asset_id,
  43.     MAX(restartCounts) AS max_count
  44.   FROM
  45.     restart_groups
  46.   GROUP BY
  47.     upt_asset_id
  48. ) max_counts ON rg.upt_asset_id = max_counts.upt_asset_id
  49. WHERE
  50.   rg.upt_asset_id IN (SELECT upt_asset_id FROM asset_filter)
  51. GROUP BY
  52.   rg.upt_asset_id,
  53.   max_counts.max_count;

You can also modify the WHERE clause to select a broader time interval (e.g., WHERE upt_day BETWEEN '2024-08-20' AND '2024-08-22').
Using this query, you can efficiently identify the number of restarts and the reasons for those restarts that appear in the osquery logs. This can help in diagnosing issues and understanding patterns in system behavior over specific time periods.

    • Related Articles

    • Sql query to find the asset-activity within the given time stamps

      please find the below query for finding the list of commands executed along with the user and cmd line and key attributes you can add multiple tables based on your needs ,in this query added only 5 tables which are mostly used you can also modify the ...

    • Query to get Installed date of apps on apps table

      Following query can be used to get the installed date of apps The installed date of apps on 'apps' table will be available from 5.10.x Uptycs Osquery release. select path, datetime(date_added_time,'unixepoch') as app_installed_time from apps Here ...

    • SQL query to fetch all the details of assets

      SQL query to fetch the instance id, tags ,last activity ,OS, OS version ,Last enrolled ,OSQ version, Gateway IP ,Interface Name ,IP ,asset group select ua.host_name as Hostname, ua.id as InstanceID, ua.tags as TAGS, ua.last_activity_at as ...

    • Queries in Uptycs Global Investigation Page from Detection and Alerts table

      Leveraging SQL queries within the Global Investigation page allows users to extract precise insights regarding detections. Below, we've compiled a set of SQL queries tailored to fulfill various investigative needs: 1. Query for Total Assets by ...

    • SQL Query to get list of S3 buckets exposed to internet

      WITH nested_acl_grants as ( select *, contains( transform( CAST(acl_grants AS ARRAY < JSON >), a -> json_extract_scalar(a, '$.Grantee.URI') in ( 'http://acs.amazonaws.com/groups/global/AllUsers', ...