Simple Report showing all computer who has a failed advertisement

[wmmayms]

This report shows all clients which have a advertisement which failed.

It is also linked to report 108 (“Advertisement status messages for a particular client and advertisement”) meaning that you can access this report by clicking the link in the first column.

 

This is how it could look:

 

To create this report follow these instructions:

1. Open SMS Console

2. Expand the reporting node

3. Rightclick reports --> New --> Report

4. Enter a name (for example: "All Clients which has a failed advertisement")

5. Enter a category

6. Enter a description

7. Choose "Edit SQL Statement"

 

Insert this code:

SELECT 
a.Name0, 
b.AdvertisementID, 
c.AdvertisementName,
b.LastStateName,
d.Name,
c.ProgramName

FROM
v_R_System a, 
v_ClientAdvertisementStatus b,
v_Advertisement c,
v_Package d

WHERE a.ResourceID = b.ResourceID
AND b.AdvertisementID = c.AdvertisementID
AND c.PackageID = d.PackageID
AND b.LastStateName = 'failed'

 

8. Press OK

9. Choose the tab "Links"

10. Configure according to this picture (reportID: 108)

 

11. Press OK

12. Rightclick on your report. Choose All tasks --> Run --> <SMSREPORTINGPOINT>

 

You are all done.