SCCM Report for All Advertisements with Specific distribution status

All advertisements with specific status :

This report gives you all percentage count with specific status for all advertisement in your environment. In order to get the list of machines where it is advertised with specific status,this report has to be linked to default report i.e 106.It works with SCCM , if you want to run the report in SMS 2003 ,see below after this code.

SELECT
AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number AS 'Number of clients with this Status',
ROUND(100.0*AdvState.number/SUM(CASE AdvTotal.LastState WHEN 0 THEN 0 ELSE 1 END),1) AS 'Percent with this Status',
SUM(CASE AdvTotal.LastState WHEN 0 THEN 0 ELSE 1 END) AS 'Total # Clients with Accepted Status'

FROM v_ClientAdvertisementStatus AdvTotal, v_Advertisement AdvName,
(SELECT a.AdvertisementID,a.LastStateName, count(*) as 'number'
FROM v_ClientAdvertisementStatus a, v_AdvertisementStatusInformation b
WHERE a.LastStatusMessageID = b.MessageID
AND b.MessageStateName = @status
GROUP BY a.AdvertisementID, a.LastStateName
) AS AdvState

WHERE AdvState.AdvertisementID = AdvTotal.AdvertisementID
AND AdvState.AdvertisementID = AdvName.AdvertisementID

GROUP BY AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number
ORDER BY AdvName.AdvertisementName

Prompt for Status:

SELECT DISTINCT
a.MessageStateName

FROM
v_AdvertisementStatusInformation a,
v_ClientAdvertisementStatus b

WHERE a.MessageID = b.LastStatusMessageID

ORDER BY MessageStateName

The same report will still work in SMS 2003 environement but it requires some modifications listed below :

SELECT
AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number AS 'Number of clients with this Status',
ROUND(100.0*AdvState.number/SUM(CASE AdvTotal.LastState WHEN 0 THEN 0 ELSE 1 END),1) AS 'Percent with this Status',
SUM(CASE AdvTotal.LastState WHEN 0 THEN 0 ELSE 1 END) AS 'Total # Clients with Accepted Status'

FROM v_ClientAdvertisementStatus AdvTotal, v_Advertisement AdvName,
(SELECT a.AdvertisementID,a.LastStateName, count(*) as 'number'
FROM v_ClientAdvertisementStatus a, v_AdvertisementStatusInformation b
WHERE a.LastStatusMessageID = b.MessageID
AND b.MessageStateName = @status
GROUP BY a.AdvertisementID, a.LastStateName
) AS AdvState

WHERE AdvState.AdvertisementID = AdvTotal.AdvertisementID
AND AdvState.AdvertisementID = AdvName.AdvertisementID

GROUP BY AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number
ORDER BY AdvName.AdvertisementName

The prompt condition for status will be same as above ,use above query and once you do this ,go the report properties and link it to the default report ID i.e 110 inorder to get the list of systems where the status is failed.