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

  1. with upt_asset_activity as
  2. (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'
  3. Union all
  4. 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'
  5. 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'
  6. union all
  7. 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'
  8. union all
  9. 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'
  10. union all
  11. 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')

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

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