Find S3 Bucket by a tag

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,
  bucket_policy_serverside_encryption_enabled,
  bucket_versioning_enabled
from upt_cloud_s3_inventory_current
cross join unnest (
  case when length(tags) > 0 then CAST(json_extract(tags, '$')    AS ARRAY(json)) 
when length(tags) = 0 then CAST(JSON '[null]' AS ARRAY(JSON))
end
) as x(s3_tags))
Select * from cte 
where tag_key like '%uptycs%'

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

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

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

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

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