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
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.
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'
).
-
- WITH restart_groups AS (
- SELECT
- upt_asset_id,
- regexp_replace(restart_reason, '(\\d+)', '') AS restart_reason,
- COUNT(*) AS restartCounts,
- MIN(upt_hostname) AS hostname,
- MAX(upt_time) AS lastOccurence
- FROM
- upt_osquery_diagnostic_logs
- WHERE
- upt_day = {required_date}
- GROUP BY
- GROUPING SETS (upt_asset_id),
- (
- upt_asset_id,
- regexp_replace(restart_reason, '(\\d+)', '')
- )
- ),
- asset_filter AS (
- SELECT
- upt_asset_id
- FROM
- upt_assets
- WHERE
- json_array_contains(CAST(tags AS JSON), 'asset-group=assets') -- You can use the required asset-groups here
- )
- SELECT
- rg.upt_asset_id AS asset_id,
- MIN(rg.hostname) AS hostname,
- SUM(rg.restartCounts) AS totalRestarts,
- MAX(
- CASE
- WHEN rg.restartCounts = max_counts.max_count THEN rg.restart_reason
- END
- ) AS reason_for_max_restarts,
- MAX(rg.lastOccurence) AS lastOccurence
- FROM
- restart_groups AS rg
- JOIN (
- SELECT
- upt_asset_id,
- MAX(restartCounts) AS max_count
- FROM
- restart_groups
- GROUP BY
- upt_asset_id
- ) max_counts ON rg.upt_asset_id = max_counts.upt_asset_id
- WHERE
- rg.upt_asset_id IN (SELECT upt_asset_id FROM asset_filter)
- GROUP BY
- rg.upt_asset_id,
- 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', ...