Google Cloudの監査ログから未使用Service Account JSON Keyを見つける方法

以下の記事ではService Account単位で利用されているかどうかをチェックしていましたが、Service AccountのJSONキー単位でも同様のことをやってみます。

with service_account_keys as (
  select
    -- 先頭にiam.googleapis.comを付けないと監査ログのフォーマットと一致しないので付与
   '//iam.googleapis.com/' || json_value(resource.data, '$.name') as key_name,
  from <Cloud Asset Inventoryのresource情報>
  where asset_type = 'iam.googleapis.com/ServiceAccountKey'
), audit_logs as (
  select
    date(timestamp, 'Asia/Tokyo') as date,
    protopayload_auditlog.authenticationInfo.serviceAccountKeyName as key_name,
  from <Audit LogのActivityログ>
  where protopayload_auditlog.authenticationInfo.serviceAccountKeyName is not null
  union all
  select
    date(timestamp, 'Asia/Tokyo') as date,
    protopayload_auditlog.authenticationInfo.serviceAccountKeyName as key_name,
  from <Audit LogのDataAccessログ>
  where protopayload_auditlog.authenticationInfo.serviceAccountKeyName is not null
), usage_counts as (
  select
    key_name,
    count(*) as count
  from audit_logs
  where date > current_date('Asia/Tokyo') - interval 30 day
  group by key_name
)

select
  key_name,
  ifnull(count, 0) as count,
from service_account_keys
left join usage_counts using(key_name)
order by count asc