Query: project_no_api_key

with project_api_key as (
  select
    project,
    count(*) as api_key_count
  from
    gcp_apikeys_key
  group by
    project
), gcp_projects as (
  select
    self_link,
    name,
    project_id
  from
    gcp_project
)
select
  p.self_link as resource,
  case
    when k.api_key_count > 0  then 'alarm'
    else 'ok'
  end as status,
  case
    when k.api_key_count > 0 then p.name || ' has ' ||  k.api_key_count || ' api key(s).'
    else p.name || ' has no api key(s).'
  end as reason
  , project_id as project
from
  gcp_projects as p
  left join project_api_key as k on k.project = p.project_id;