Query to compare two SMS collections

Collection1 with 1000 systems and Collection2 with 400. This query lists the systems that are  in collection1, but not in Collection2.

In the following query replace XYZ00001 with the collection ID of Collection2 and replace SMS00001 with collection ID of Collection1.

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_R_System.ResourceId not in (select sys.resourceid from sms_cm_res_coll_XYZ00001 AS coll, sms_r_system as sys where sys.resourceid = coll.resourceid) and SMS_G_System_SYSTEM.ResourceID in (select sys.resourceid from sms_cm_res_coll_SMS00001 AS coll, sms_r_system as sys where sys.resourceid = coll.resourceid)

How to find clients with no Maintenance Window defined

Use the following query in ConfigMgr Report to find the list of clients that do not have any Maintenance Window defined.

SELECT sys.Name0 'Name', v_R_System.Operating_System_Name_and0 'Operating System'
FROM v_ServiceWindow AS sw
INNER JOIN v_FullCollectionMembership AS fcm ON sw.CollectionID = fcm.CollectionID
RIGHT JOIN v_R_System AS sys ON fcm.ResourceID = sys.ResourceID
WHERE sw.Name is NULL AND sys.Client0 = 1
ORDER BY sys.Name0

SMS_SITE_BACKUP thread don't get restarted after successful backup

If you notice an intermittent issue whereby the component threads don't get restarted after a backup, in service manager they show as starting.

Resolution: Apply the hotfix:  http://support.microsoft.com/kb/982399

Where can Site Repair Wizard log be found.

When the Site Repair Wizard is run, check your repairwizard.log, it logs site repair information, which is written to the RepairWizard.log log file. This file is created in the <Configuration Manager 2007 program files>\AdminUI\AdminUILog directory when the Site Repair Wizard is started.

Hardware Inventory not being processed by SMS/ConfigMgr servers | Hardware Inventory not working

I noticed some systems mostly Windows 2000 Servers hardware inventory did not report back to SMS and their hardware inventory MIF files were being moved to \inboxes\dataldr.box\BADMIFS\ directory. This problem seem to get resolved by uninstalling and re-installing the SMS/ConfigMgr client, but this will reappear again after few days.
 In the DataLDR.log the following error will be report for the effected clients.

File size exceeds defined maximum.

SMS creates a BadMifs directory only if a bad IDMIF or NOIDMIF is detected at your site server by the Inventory data loader. By default, the MIF file size is set to 5,000,000 bytes (4.7MB) that SMS collects from a client during its normal hardware inventory. If a MIF file exceeds the limit the MIF is rejected and placed into BADMIFs folder.
 You can increase the maximum MIF file size value from the registry on your SMS/ConfigMgr Site Server by modifying the following registry key:

On a 32-bit server:

 On a 64-bit server:

However if you increase this value your disk space on the site server may grow considerably because of the larger the MIF file size and your resources may diminish because it takes longer for the Inventory Data Loader to parse and process them.


"Approved" and "Targeted" in SCCM Reports

Today, I noticed an interesting thing in SCCM reports. If you use 'Targeted' column name in any SCCM report it always be displayed as 'Approved'.
If you run the follwoing report, insted of displaying the 'Targeted' column name it displays the column name as "Approved"

Select Name0 as Targeted from v_r_system.

How to run a program under Local System account

If you using sms/sccm then you may require to test a program under the LocalSystem accounts. Here is an easy procedure to run a program under Local System account.
How to run a program under LocalSystem account:
 PSExec.exe -i -s -d cmd.exe
  • In the command prompt window start any program that you want to run under Local System account.

Where can we find SCCM/SMS client logs

Client logs location:
  • On 32-bit system, the client logs are located in the %WINDIR%\System32\CCM\Logs folder. 
  • On 64-bit system, the client logs are located in the %WINDIR%\SysWOW64\CCM\Logs
  • On SMS/SCCM servers the client logs are located in the <INSTALLDRIVE>\SMS_CCM , here <INSTALLDRIVE>  is the drive where sms/sccm servers is installed.
Client installation logs location(ccmsetup.log,client.msi.log etc):
  • On 32-bit system, the client installation log(ccmsetup.log,client.msi.log etc) are located in the %WINDIR%\System32\ccmsetup\ folder.
  • On 64-bit system, the client installation logs(ccmsetup.log,client.msi.log etc) are located in the %WINDIR%