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

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 columns if you have any specific requirement
Replace hostname and time stamp values based on requirements

  2. with upt_asset_activity as
  3. (select upt_time as activity , login_name as user , cmdline as CMD_Line ,path as Key_Attributes from process_events where upt_day=CAST(date_format((CURRENT_DATE - INTERVAL '0' DAY), '%Y%m%d') AS INT) and upt_hostname='con-ubuntu-20-04-vm1'
  4. Union all
  5. select upt_time as activity , login_name as user , cmdline as CMD_Line ,path as Key_Attributes from user_events where upt_day=CAST(date_format((CURRENT_DATE - INTERVAL '0' DAY), '%Y%m%d') AS INT) and upt_hostname='con-ubuntu-20-04-vm1'
  6. union all select upt_time as activity , login_name as user , cmdline as CMD_Line ,path as Key_Attributes from process_file_events where upt_day=CAST(date_format((CURRENT_DATE - INTERVAL '0' DAY), '%Y%m%d') AS INT) and upt_hostname='con-ubuntu-20-04-vm1'
  7. union all
  8. select upt_time as activity , login_name as user , cmdline as CMD_Line ,path as Key_Attributes from http_events where upt_day=CAST(date_format((CURRENT_DATE - INTERVAL '0' DAY), '%Y%m%d') AS INT) and upt_hostname='con-ubuntu-20-04-vm1'
  9. union all
  10. select upt_time as activity , login_name as user , cmdline as CMD_Line ,path as Key_Attributes from socket_events where upt_day=CAST(date_format((CURRENT_DATE - INTERVAL '0' DAY), '%Y%m%d') AS INT) and upt_hostname='con-ubuntu-20-04-vm1'
  11. union all
  12. select upt_time as activity , login_name as user , cmdline as CMD_Line ,path as Key_Attributes from yara_process_events where upt_day=CAST(date_format((CURRENT_DATE - INTERVAL '0' DAY), '%Y%m%d') AS INT) and upt_hostname='con-ubuntu-20-04-vm1')

  14. select * from upt_asset_activity where activity >= to_timestamp('2024-02-16 00:00:01','yyyy-mm-dd hh24:mi:ss') and activity <= to_timestamp('2024-02-18 00:00:01','yyyy-mm-dd hh24:mi:ss') and Key_Attributes like '%/etc/shadow%'

    • Related Articles

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

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

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

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