Query: servicecatalog_portfolio_shared_only_with_aws_organization

with account_type_servicecatalog_portfolio_share as (
  select
    portfolio_id,
    account_id,
    principal_id,
    portfolio_display_name,
    region
  from
    aws_servicecatalog_portfolio_share
  where
    type = 'ACCOUNT'
)
select
  coalesce (t.portfolio_id, a.arn) as resource,
  case
    when t.portfolio_id is null then 'ok'
    else 'alarm'
  end as status,
  case
    when t.portfolio_id is null then a.title || ' has no shared portfolios.'
    else t.portfolio_display_name || ' shared portfolio with AWS account ' || t.principal_id || '.'
  end as reason
  , a.region, a.account_id
from
  aws_account as a
  left join account_type_servicecatalog_portfolio_share t on t.account_id = a.account_id;