Status of Specific Advertisement can get from Default available report but what if you want to find the status of specific advertisement with its LastAcceptanceStatusTime greater or less than X days. Full Details :http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/30e4dbda-982b-43dc-8665-8dd142925d4f DECLARE  @olddate datetime         ,@NullVal datetime SET @olddate = DATEADD(day,-5, GETUTCDATE()) select adv.AdvertisementName 'Application', sys.Name0 'System', LastAcceptanceStateName 'Acceptance', sts.LastAcceptanceStatusTime 'AcceptTime_GMT', sts.LastStatusMessageIDName…

Previous report list the packages which are successfully distributed to Distribution Points http://eskonr.com/2012/06/sccm-report-total-number-of-packages-targeted-to-dp/ This post gives you more information about package status for selected distribution Point. Report for packages which are Pending: select s.sitecode,s.packageid,p.name,s.sourceversion as "dpversion",p.storedpkgversion as latesteversion from v_PackageStatusDistPointsSumm s join smspackages p on s.packageid = p.pkgid where s.installstatus !='Package Installation complete' and s.servernalpath…