How to extract the Sandbox, features enabled, and published status?

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.