Query: lambda_function_cloudtrail_logging_enabled

with function_logging_cloudtrails as (
  select
    distinct replace(replace(v::text,'"',''),'/','') as lambda_arn,
    d ->> 'Type' as type
  from
    aws_cloudtrail_trail,
    jsonb_array_elements(event_selectors) e,
    jsonb_array_elements(e -> 'DataResources') as d,
    jsonb_array_elements(d -> 'Values') v
  where
    d ->> 'Type' = 'AWS::Lambda::Function'
    and replace(replace(v::text,'"',''),'/','') <> 'arn:aws:lambda'
), function_logging_region as (
    select
      region as cloudtrail_region,
      replace(replace(v::text,'"',''),'/','') as lambda_arn
    from
      aws_cloudtrail_trail,
      jsonb_array_elements(event_selectors) e,
      jsonb_array_elements(e -> 'DataResources') as d,
      jsonb_array_elements(d -> 'Values') v
    where
      d ->> 'Type' = 'AWS::Lambda::Function'
      and replace(replace(v::text,'"',''),'/','') = 'arn:aws:lambda'
    group by
      region,
      lambda_arn
),
function_logging_region_advance_es as (
  select
    region as cloudtrail_region
  from
    aws_cloudtrail_trail,
    jsonb_array_elements(advanced_event_selectors) a,
    jsonb_array_elements(a -> 'FieldSelectors') as f,
    jsonb_array_elements_text(f -> 'Equals') e
  where
    e = 'AWS::Lambda::Function'
    and f ->> 'Field' != 'eventCategory'
  group by
    region
)
select
  distinct l.arn as resource,
  case
    when (l.arn = c.lambda_arn)
      or (r.lambda_arn = 'arn:aws:lambda' and r.cloudtrail_region = l.region )
      or a.cloudtrail_region = l.region then 'ok'
    else 'alarm'
  end as status,
  case
    when (l.arn = c.lambda_arn)
      or (r.lambda_arn = 'arn:aws:s3' and r.cloudtrail_region = l.region )
      or a.cloudtrail_region = l.region then l.name || ' logging enabled.'
    else l.name || ' logging not enabled.'
  end as reason
  
  , l.region, l.account_id
from
  aws_lambda_function as l
  left join function_logging_cloudtrails as c on l.arn = c.lambda_arn
  left join function_logging_region as r on r.cloudtrail_region = l.region
  left join function_logging_region_advance_es as a on a.cloudtrail_region = l.region;