Thursday, October 28, 2010

Query to get detailed summary of all the advertisments

SELECT a.Netbios_name0 as 'Host Name',
site.sms_installed_sites0 as 'Sitecode',
a.Client0,
a.Obsolete0,
adv.AdvertisementName,
adv.AdvertisementID,
adv.Comment as 'Description',
pkg.Name AS 'Package Name',
adv.ProgramName,
col.Name as 'Collection Name',
adv.CollectionID,
advstate.LastAcceptanceMessageIDname,
advstate.LastAcceptanceStateName,
advstate.LastAcceptanceStatusTime,
advstate.LastStatusmessageIDName,
advstate.LaststateName,
advstate.LastStatusTime,
advstate.LastExecutionResult,
advstate.LastExecutionContext
FROM v_Advertisement adv
INNER JOIN v_Package pkg ON adv.PackageID = pkg.PackageID
INNER JOIN V_COLLECTION col ON adv.CollectionID=col.CollectionID
INNER JOIN v_ClientAdvertisementStatus advstate on adv.AdvertisementID=advstate.AdvertisementID
INNER JOIN V_R_SYSTEM a ON a.Resourceid=advstate.resourceid
INNER JOIN v_GS_WORKSTATION_STATUS HW ON a.resourceid=hw.resourceid
INNER JOIN v_GS_LastSoftwareScan sw ON a.resourceid=sw.resourceid
LEFT OUTER JOIN v_RA_System_SMSInstalledSites site ON a.resourceid=site.resourceid
WHERE adv.AdvertisementID LIKE '%'
order by adv.AdvertisementName

3 Comments:

Blogger Phil Reynolds said...

This would be interesting to take the results and compare to All Workstations collection and show what PC's aren't getting any ad's.

November 17, 2010 at 10:40 AM  
Blogger Jaison Jose said...

OK...will make it once i have some time..may be tomorrow...

November 17, 2010 at 10:48 AM  
Blogger Leslie Lim said...


I have found your blogs to be friendly and welcoming. Thanks for making this one. I really enjoy reading and surfing it. Try to visit my site @ www.imarksweb.org

Zea

January 11, 2015 at 11:07 PM  

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home