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 
4 Comments
when you say "You can still apply the same to collection instead of specific computer as well." How can it be accomplished>
you will have replace the collection ID prompt info with system info.
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.
Thanks,
Chris
to get report for month or so to see how many computers has ,take a look at this report http://eskonr.com/2011/09/sla-report-for-sccm-software-distribution/