Query to find all unused collections without any advert targeted

select a.collectionID,a.Name,a.lastChangeTime, a.LastMemberChangetime,b.AdvertisementID
from v_collection as a left outer join v_advertisement as b
on a.CollectionID=b.collectionID
where a.CollectionID not in (select collectionID from v_FullCollectionMembership)
and a.CollectionID not like 'SMS%'
and b.AdvertisementID is null
order by name

Description:

This query will list all the unused collections without any advertisements targeted against it. This will help when you have lot of collections get created in your console
and you want to filter all those unused one to cleanup your console.