SCCM report to list 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

You can apply the above report to SMS 2003 environment also :

This is similar to the above report but few things gets changed.

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 in order to get the list of systems where the status is failed.