SCCM report to list all the advertisement that are currently assigned to computer

When you assign any advertisement (either application or Task sequence) to collection(Group of computers) ,all the advertisement will be tracked(logged) in SCCM Database with its status and other details.The advertisement will run on the computer and gives the status of it which can be find Via Default reports “All advertisement for specific computer(Report ID:126)”.

What ,if you move the advertisement to different different collection where the computers(which you advertised above) are not part of this collection and you want to track all the advertisements that are currently assigned to specific computer.

You can get this currently assigned advertisements by simply Right click on computer--->properties--->Advertisements Tab (its list only currently assigned ones) but how about the status and what is the package name and program name etc. details.

Do you use the default report(126) ? No,because it will gives you all the advertisements that are assigned to computer but not current advertisements assigned to it (computer) since it uses View called V_advertisement

Below is the report to fetch the list of current advertisements assigned to computer with other information added to it.You can still apply the same to collection instead of specific computer as well.

SELECT distinct dv.AdvertisementName,stat.LastStateName,adv.AdvertisementID,pkg.PackageID,adv.ProgramName from v_Advertisement adv
    inner join v_FullCollectionMembership FCM ON FCM.CollectionID=adv.CollectionID
    Inner join v_CurrentAdvertisementAssignments CADV ON CADV.AdvertisementID=adv.AdvertisementID
    inner join v_Package pkg ON pkg.PackageID=adv.PackageID
    JOIN v_ClientAdvertisementStatus stat ON stat.AdvertisementID = adv.AdvertisementID
    where FCM.Name=@computer

Prompt for computer: Select name from V_R_system

If you use the prompt value for computer,it might take some time to display all the computer from Database if you have more than 1000 computers instead,you can specify the computer name directly in Query before you run the report.

Hope it helps you  Smile

4 Responses to "SCCM report to list all the advertisement that are currently assigned to computer"

  1. Cool report.

    I have a question, whats an sql statement to report how many machines have the same Advertisement?
    For example I created Adobe Writer Package and assigned the policy or advertisement through AD Group Security Membership.

    1 Month from now I want to run a report to show how many machines have this AD Security Advertisement membership.



Leave a Reply