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 'ExecutionStatus',
sts.LastStateName 'ExecutionState',sts.LastStatusTime 'ExecutionTime_GMT',
adt.MessageName,sts.LastExecutionResult
from v_ClientAdvertisementStatus sts
join v_AdvertisementStatusInformation adt on adt.MessageID=sts.LastStatusMessageID
join v_Advertisement adv on adv.AdvertisementID=sts.AdvertisementID
join v_R_System sys on sys.ResourceID=sts.ResourceID
where ((sts.LastAcceptanceStatusTime < @olddate) or (sts.LastStatusTime < @olddate))
and adv.AdvertisementName like '%Adobe_reader_Eng%' and sts.LastExecutionResult like '1603'
order by sys.Name0
Change the number of days and Advertisement Name.
Note: Please change the Quotes ,blog converts them as fancy here.
