Eswar Koneti's Blog

I was looking at client health percentage other day and found many systems were reported as not healthy though I fixed some of the machines with sccm client installed and able to receive policies.

Looked at Database for healthy computers and see double entries with different ResourceID ,client0 and HardwareID0 is NULL.

So what next ? I created collection with filter computers with sccm client installed and should be in computers with no sccm client.

Collection:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.Client from SMS_R_System where
SMS_R_System.Name in (select name  from  SMS_R_System where SMS_R_System.Client is not null) and
SMS_R_System.Client is null

Report:

select name0 from v_R_System where client0 is null
and Name0 in( select Name0 from v_R_System where client0 =1)
order by name0

I see 100+ computers which are duplicate with same hostname but different value for ResourceID,client0 and hardwareID0 etc.

Special delete will remove them completely from database.

Full Details :http://social.technet.microsoft.com/Forums/en-US/configmgrgeneral/thread/7b4d3810-a262-4c63-8dd6-7cc906b85941

select sys.name0,sys.AD_Site_Name0,FCM.SiteCode,ARP.DisplayName0,ARP.Version0,ARP.InstallDate0 from V_R_system sys
inner join v_Add_Remove_Programs ARP on arp.resourceID=sys.ResourceID
inner join v_FullCollectionMembership FCM on FCM.ResourceID=sys.ResourceID
and arp.DisplayName0 like ‘%project reader%’ and ARP.Version0 < ’4%’

For collection ,follow the post here on http://eskonr.com/2012/05/sccm-collection-computers-with-lower-version-application/

Full Details http://social.technet.microsoft.com/Forums/en-US/configmgrgeneral/thread/7b4d3810-a262-4c63-8dd6-7cc906b85941

This collection is created using sub selected queries http://eskonr.com/2011/12/sccm-collection-sub-selected-quiries/

select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from  SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID not in (select SMS_R_SYSTEM.ResourceID from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId where (SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%project reader%" or SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "%project reader%") and (SMS_G_System_ADD_REMOVE_PROGRAMS.Version > "4%" or SMS_G_System_ADD_REMOVE_PROGRAMS_64.Version > "4%"))