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