SMS 2003 - Reports - Status of Advertisements which have NOT Succeeded
There was a request on the forums today for a report which would yield some information about advertisements which had not succeeded. I responded with the following: (MOF file is attached.)
Report Name - Status of Advertisements which have NOT Succeeded
SELECT vAI.CollectionID, vAI.AdvertisementName, vRS.Netbios_Name0, vCAS.LastStatusMessageIDName, vCAS.LastStatusTime
FROM v_ClientAdvertisementStatus AS vCAS INNER JOIN
v_R_System AS vRS ON vCAS.ResourceID = vRS.ResourceID INNER JOIN
v_AdvertisementInfo AS vAI ON vCAS.AdvertisementID = vAI.AdvertisementID
WHERE (vCAS.LastStatusMessageID <> 10009) AND (vCAS.LastStatusMessageID <> 10008) AND vAI.CollectionID = @CollID
Prompt: Name = CollID
begin
if (@__filterwildcard = '')
SELECT DISTINCT CollectionID, Name FROM v_Collection ORDER BY Name
else
SELECT DISTINCT CollectionID, Name FROM v_Collection
WHERE CollectionID like @__filterwildcard
ORDER BY Name
end