In this article, we will try to extract the Sandbox, Features Enabled, Owner, Updated by and the status of sandboxes.
SQL Query to extract Sandbox details:
select name sandbox_name
,description
, (select NVL((select ppnf.full_name from per_person_names_f ppnf, per_users pu
where ppnf.person_id = pu.person_id
and pu.username = asu.user_name
and ppnf.name_type = 'GLOBAL'
and sysdate between ppnf.effective_start_date and ppnf.effective_end_date), asu.user_name) from ADF_SB_USERS asu
where asu.sandbox_id = sand.sandbox_id) Sandbox_Owner
,sandbox_state
,NVL((select ppnf.full_name from per_person_names_f ppnf, per_users pu
where ppnf.person_id = pu.person_id
and pu.username = sand.published_by
and ppnf.name_type = 'GLOBAL'
and sysdate between ppnf.effective_start_date and ppnf.effective_end_date), sand.published_by) published_by
,to_char(creation_date,'MM/DD/YYYY') creation_date
,NVL((select ppnf.full_name from per_person_names_f ppnf, per_users pu
where ppnf.person_id = pu.person_id
and pu.username = sand.created_by
and ppnf.name_type = 'GLOBAL'
and sysdate between ppnf.effective_start_date and ppnf.effective_end_date), sand.created_by) created_by
,to_char(last_update_date,'MM/DD/YYYY') last_update_date
,NVL((select ppnf.full_name from per_person_names_f ppnf, per_users pu
where ppnf.person_id = pu.person_id
and pu.username = sand.last_updated_by
and ppnf.name_type = 'GLOBAL'
and sysdate between ppnf.effective_start_date and ppnf.effective_end_date), sand.last_updated_by) last_updated_by
,to_char(sand.REFRESH_DATE,'MM/DD/YYYY') REFRESH_DATE
,NVL((select ppnf.full_name from per_person_names_f ppnf, per_users pu
where ppnf.person_id = pu.person_id
and pu.username = sand.REFRESHED_BY
and ppnf.name_type = 'GLOBAL'
and sysdate between ppnf.effective_start_date and ppnf.effective_end_date), sand.REFRESHED_BY) REFRESHED_BY
,(select listagg(Substr(feature_id, -Instr(Reverse(feature_id), '.') + 1), ',') WITHIN GROUP (ORDER BY sandbox_id) from ADF_SB_ENABLED_FEATURES where sandbox_id = sand.sandbox_id) Enabled_Features
from ADF_SB_SANDBOXES sand
--where name like 'SM_%'
order by creation_date desc
It’s better to create sandboxes with initials as prefix. I use “SM_”, so that the sandboxes I create can easily be identified.