Query for client does not receive advertisement

This will list all clients that never get advertisement.

Log on to SQL Server Management Studio and run this query against SCCM database

select distinct count(v_ClientAdvertisementStatus.resourceid) as [Number of Adverts],sys.Active0,sys.Client0,sys.Client_Version0,sys.Netbios_Name0,
sys.Operating_System_Name_and0,sys.Obsolete0,
sys.Resource_Domain_OR_Workgr0,sys.User_Domain0,
sys.User_Name0 from v_R_System sys right join v_ClientAdvertisementStatus on sys.resourceid=v_ClientAdvertisementStatus.resourceid where sys.ResourceID not in (select distinct ResourceID from v_ClientAdvertisementStatus where v_ClientAdvertisementStatus.LastStateName = 'Succeeded') group by sys.Active0,sys.Client0,sys.Client_Version0,
sys.Netbios_Name0,sys.Operating_System_Name_and0,
sys.Obsolete0,sys.Resource_Domain_OR_Workgr0,
sys.User_Domain0,sys.User_Name0