Query: mod_installed_controls_error_list

with controls_list as
(
  select
    id as control_id,
    state as control_state,
    reason as control_reason,
    details #>> '{0,value}' as control_details,
    resource_id as resource_id,
    substring(workspace
  from
    'https://([a-z]+)(.)') as "Workspace",
    workspace as "Workspace URL"
  from
    guardrails_control
  where
    filter = 'state:error controlTypeId:"tmod:@turbot/turbot#/control/types/modInstalled" level:self'
)
,
mods_resources as
(
  select
    substr(title, 9) as mod_name,
    id as resource_id
  from
    guardrails_resource
  where
    filter = 'resourceTypeId:"tmod:@turbot/turbot#/resource/types/mod" level:self'
)
select
  mr.mod_name as "Mod Name",
  c.control_id,
  c.control_reason as "Reason",
  c.control_details as "Detail",
  mr.resource_id,
  c."Workspace",
  c."Workspace URL"
from
  controls_list c
  left join mods_resources mr on c.resource_id = mr.resource_id;