Jimmy Martin put together some awesome reports that can be used in an SCCM dashboard. This dashboard allows you to see what a site has been doing for the last 4 months.
Download: SCCM Site Monthly Totals
This series of reports can be used to create a report dashboard to show the last three months of creation, edit, and deletion activity for collections, packages, programs, and advertisements
Download: Advertisement Success Rate Site Wide
These two reports will show a count of the advertisement results for the entire site. One is for month to date and the other is last month’s data.
In addition to this i created a drill though for the Advertisement Success rate reports. This will allow you to see exactly what Advertisement is causing the problem and will allow you to clean up your status.
Simply create a new report with the code below then link it to the reports above.
you can link it to each of the advertisement report. you will need to create a new one for each report you link.
Make sure that you change this code
GETDATE()) = 0) to match the GETDATE()) = 0) from the first report
So if it is 0 then make it 0 if it is 1 then make it 1 ETC…
Here is the code:
SELECT v_R_System.Name0 AS ‘Machine Name’
, CASE WHEN v_R_System.Client0 = ’1′ THEN ‘yes’ WHEN v_R_System.Client0 = ’0′ THEN ‘no’ ELSE ‘no’ END AS ‘Client’
, CASE WHEN v_R_System.Obsolete0 = ’1′ THEN ‘yes’ WHEN v_R_System.Obsolete0 = ’0′ THEN ‘no’ ELSE ‘no’ END AS ‘Obsolete’
, v_ClientAdvertisementStatus.LastStateName AS Status
, v_AdvertisementInfo.AdvertisementName As ‘AD Name’
, v_ClientAdvertisementStatus.AdvertisementID AS ‘Ad ID’
FROM v_ClientAdvertisementStatus INNER JOIN
v_R_System ON v_ClientAdvertisementStatus.ResourceID = v_R_System.ResourceID INNER JOIN
v_AdvertisementInfo ON v_ClientAdvertisementStatus.AdvertisementID = v_AdvertisementInfo.AdvertisementID
WHERE (DATEDIFF(m, v_ClientAdvertisementStatus.LastStatusTime, GETDATE()) = 0)
AND (v_ClientAdvertisementStatus.LastStateName = @Status )
ORDER BY ‘Ad ID’, ‘Machine Name’
Awesome reports Jimmy 
