SCCM Report to get advertisement details for a program, package and collection

Below is a simple SQL query which provides Program Name, Package Name, Collection Name, Advertisement Name and Advertisement expiration Time. **********************************************************************
SELECT AdvertisementName AS [Advertisement Name], ProgramName AS [Program Name], PackageName AS [PKG Name], CollectionName AS [Collection Name], ExpirationTime AS [Adv Exp Time] 
FROM v_AdvertisementInfo **********************************************************************

More SCCM custom reports can be found here