Steampipe queries

queries.md

Steampipe queries

General

Query configured API limiters

steampipe query "select distinct name from steampipe_plugin_limiter"

AWS

Return an AWS account ID matched with its alias. Useful for matching account numbers to names.

select
  alias,
  substring(arn FROM 'arn:aws:::(\d+)') AS account_id
from
  aws_all.aws_account
  cross join jsonb_array_elements(account_aliases) as alias order by alias

Return the storage used of all log groups, grouped by the account id consuming.

select sum(stored_bytes) / 1024 / 1024 as gb,account_id from aws_direct_dev.aws_cloudwatch_log_group where retention_in_days is null group by account_id

Return total storage used by cloudwatch log groups across each AWS account.

with cw_log_usage_by_account as (
  select
      sum(stored_bytes) as total_stored_bytes,account_id
  from aws_all.aws_cloudwatch_log_group
  where retention_in_days is null
  group by account_id
),
account_mapping as (
  select
      replace(replace(alias::text, '''', ''), '"', '') as alias,
      substring(arn FROM 'arn:aws:::(\d+)') AS account_id
  from
      aws_all.aws_account
  cross join jsonb_array_elements(account_aliases) as alias
  order by alias
)
select
  cwu.total_stored_bytes / 1024 / 1024 as gb,
  cwu.total_stored_bytes / 1024 / 1024 / 1024 as tb,
  am.account_id,
  am.alias
from
  account_mapping as am
join
  cw_log_usage_by_account cwu on am.account_id = cwu.account_id
order by
  cwu.total_stored_bytes asc

Return total storage used by cloudwatch log groups across all AWS accounts.

with cw_log_usage_by_account as (
   select
       sum(stored_bytes) as total_stored_bytes,account_id
   from aws_all.aws_cloudwatch_log_group
   -- where retention_in_days is null
   group by account_id
),
total_storage as (
   select
       sum(cwu.total_stored_bytes) as total_stored_bytes_all_accounts
   from
       cw_log_usage_by_account cwu
)

-- Total storage used for all accounts
select
   total_stored_bytes_all_accounts / 1024 / 1024 / 1024 / 1024 as total_used_tb
from
   total_storage

GitHub

Retreive current GitHub API usage and rate limits.

select
  core_limit,
  core_remaining,
  search_limit,
  search_remaining,
  core_reset
from
  github_rate_limit;