Queries in Uptycs Global Investigation Page from Detection and Alerts table

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 Specific Detection:
******
SELECT
  display_name AS Rule_Name,
  count(*) AS Total
FROM
  upt_detections
WHERE
  created_at BETWEEN TIMESTAMP '2024-01-24 00:00:00' AND TIMESTAMP '2024-01-31 23:59:59'
  AND asset_host_name LIKE '%andromeda%'
  AND display_name LIKE 'Bad%'
GROUP BY
  1
ORDER BY
  total DESC;
******
This query provides the total count of assets associated with a specific detection within a defined time frame. Users can customize the timestamp and hostname criteria for precise filtering.


2. Query for Assets Matching Specific Detection Name:
******
SELECT
  asset_host_name AS Hostname,
  count(*) AS Total
FROM
  upt_detections
WHERE
  created_at BETWEEN TIMESTAMP '2024-01-24 00:00:00' AND TIMESTAMP '2024-01-31 23:59:59'
  AND display_name LIKE '%monitored utility%'
GROUP BY
  1
ORDER BY
  total DESC; 
******
This query retrieves assets matching a specific detection name within the specified time range. Users can adjust the timestamp and detection name pattern as needed.


3. Query for Hostnames Associated with a Specific Rule:
******
SELECT
  upt_hostname,
  alert_rule_name,
  display_name,
  json_extract_scalar(metadata, '$["User"]') AS UserName,
  json_extract_scalar(metadata, '$["Process"]') AS ParentProcess,
  /* Additional fields extracted from metadata */
  metadata,
  note,
  severity,
  alert_time,
  status,
  created_at,
  updated_by
FROM
  upt_alerts
WHERE
  alert_rule_name LIKE '%%'
  AND created_at BETWEEN TIMESTAMP '2024-01-24 00:00:00' AND TIMESTAMP '2024-01-31 23:59:59'
ORDER BY
  created_at DESC; 
******
This query retrieves detailed information associated with a specific rule, including hostnames, alert details, and metadata. Users can customize the alert rule name for pattern matching.


Feel free to utilize these queries within the Uptycs Global Investigation page to streamline your detection analysis and investigation processes effectively. Should you have any further inquiries or require assistance, please do not hesitate to reach out. 





    • Related Articles

    • Adding external or additional data into alerts and detections

      There might be a need to add extra data into alerts and detections ( e.g. External TIcket ID ). To do this in UI: Open Alerts or Detections page. Select the alert, click on the "Manage" icon and add the data into the "Additional Details" field. To ...

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

    • Find S3 Bucket by a tag

      This query helps to find an S3 bucket by any tag key or tag value with cte as (select bucket_name, JSON_EXTRACT_scalar(s3_tags, '$.Key') as tag_key, JSON_EXTRACT_scalar(s3_tags, '$.Value') as tag_value, bucket_mfa_delete_enabled, ...

    • List the vulnerabilities in the fleet which is having remediation

      please find the below query you can modify the query based on the need and conditons required with vulners as (SELECT *, ROW_NUMBER() OVER(PARTITION BY upt_asset_id, cve_list, package_name, package_version, os, os_version ORDER BY IF(upt_type = ...