Microsoft KB Archive/939872

= Reports that use the v_Add_Remove_Programs view stop responding and cause high CPU use in SMS 2003 SP3 =

Article ID: 939872

Article Last Modified on 11/16/2007

-

APPLIES TO


 * Microsoft Systems Management Server 2003

-



SYMPTOMS
After you upgrade to Microsoft Systems Management Server (SMS) 2003 Service Pack 3 (SP3), you run a report that is based on the v_Add_Remove_Programs view. However, the report stops responding, and the process uses 100 percent of the CPU resources. For example, reports such as the &quot;Computers with specific software registered in Add Remove Programs&quot; report stop responding, and the process uses 100 percent of the CPU resources.



CAUSE
This problem occurs when 32-bit SMS 2003 SP3 advanced clients incorrectly report 32-bit data as 64-bit data. This problem occurs when the v_Add_Remove_Programs view in SMS 2003 SP3 joins data from the Add_Remove_Programs_DATA table together with data from the Add_Remove_Programs_DATA_64 table. Therefore, the same data is duplicated in both data tables.



Hotfix information
A supported hotfix is now available from Microsoft. However, it is intended to correct only the problem that is described in this article. Apply it only to systems that are experiencing this specific problem. This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next SMS 2003 service pack that contains this hotfix.

To resolve this problem immediately, contact Microsoft Customer Support Services to obtain the hotfix. For a complete list of Microsoft Customer Support Services telephone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/contactus/?ws=support

Note In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The usual support costs will apply to additional support questions and issues that do not qualify for the specific update in question.

Prerequisites
To apply this hotfix, you must have SMS 2003 SP3 installed.

Restart requirement
You do not have to restart the computer after you apply this hotfix.

Hotfix replacement information
This hotfix does not replace any other software updates.

File information
The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time item in Control Panel.

Hotfix installation information
Because of new architecture in SMS 2003, you must create a new package and a new program to advertise this hotfix to the advanced client computers. We recommend that you let the software installation wizard do this automatically. You have to create the package, the program, the collection, and the advertisement on the site at the top of the hierarchy.

After the hotfix is installed on an 32-bit SMS 2003 SP3 advanced client, during its next hardware inventory cycle, the advanced client will no longer report data for the Win32Reg_AddRemovePrograms64 class. Therefore, the previously reported data will be removed from the associated table in the SMS database.

To install this hotfix, follow these steps:  Download the SMS2003-SP3-KB939872-X86-ENU.exe hotfix file. Copy the SMS2003-SP3-KB939872-X86-ENU.exe file to a folder on the SMS primary site server. On the SMS primary site server, click Start, point to All Programs, point to Systems Management Server, and then click SMS Administrator Console. Double-click Site Database. Right-click Collections, point to New, and then click Collection. In the Collection Properties dialog box, type Advanced Clients that need KB939872 in the Name box. Click the Membership Rules tab, and then click the yellow button.</li> Type a name for the query, and then click Edit Query Statement.</li> Click the General tab, and then click Show Query Language.</li> In the Query statement section, type the following query, and then click OK three times:

select SMS_R_System.Name, SMS_R_System.NetbiosName, SMS_G_System_SMS_ADVANCED_CLIENT_STATE.DisplayName, SMS_G_System_SMS_ADVANCED_CLIENT_STATE.Version from SMS_R_System inner join SMS_G_System_SMS_ADVANCED_CLIENT_STATE on SMS_G_System_SMS_ADVANCED_CLIENT_STATE.ResourceID = SMS_R_System.ResourceId where SMS_R_System.ClientType = 1 and SMS_G_System_SMS_ADVANCED_CLIENT_STATE.Name = &quot;SmsInventory&quot; and SMS_G_System_SMS_ADVANCED_CLIENT_STATE.Version < &quot;2.50.4253.3106&quot;

Note To use this query, you must have at least one advanced client computer in your inventory to create the database classes. This query identifies advanced client computers that do not have this hotfix installed.</li> On the SMS site server computer, double-click SMS2003-SP3-KB939872-X86-ENU.exe to start the installation wizard.</li> Follow the steps in the installation wizard. After the installation files are copied, click Yes, I want to create a package and program now, and then click Next.</li> Click Next to accept the default package name and the default program name.</li> Click Next to accept the default package source location.</li> On the details page, click Next, and then click Finish.</li> In the SMS Administrator Console, double-click Packages.</li> Right-click the KB939872 - Advanced Client Hotfix package, point to All Tasks, and then click Distribute Software.</li> On the Welcome to the Distribute Software Wizard page, click Next.</li> Click to select the check box that is next to the distribution points that you want to use, and then click Next three times.</li> On the Advertisement Target page, click Advertise this program to an existing collection, and then click Browse.</li> On the Browse Collection page, click Advanced Clients that need KB939872, and then click OK.</li> Click Next two times.</li> <li>On the Advertise to Subcollections page, click Advertise the program only to members of the specified collection, and then click Next.</li> <li>Complete the Software Distribution Wizard.</li></ol>

To perform a silent installation of this hotfix, follow these steps.

Note Silent installs do not create the SMS package and programs. See the \Logs\KB939872\ACReadme.txt file for instructions that you can use to manually update the SMS advanced clients.
 * 1) Click Start, click Run, type cmd, and then click OK.
 * 2) At the command prompt, switch to the folder that contains the SMS2003-SP3-KB939872-X86-ENU.exe file.
 * 3) At the command prompt, type SMS2003-SP3-KB939872-X86-ENU.exe /s, and then press ENTER.

To remove this hotfix, follow these steps on each SMS advanced client computer that has the hotfix applied.

Note You must have Windows Installer version 3.0 or a later version. <ol> <li>Click Start, click Run, type cmd, and then click OK.</li> <li>At the command prompt, type the following command:

msiexec /package  /uninstall   /REINSTALL=All /REINSTALLMODE=maus

In this step, replace  with the GUID of the SMS advanced client software that is installed on the client. Also, replace  with the GUID for this specific SMS advanced client update.

The product code for the English version of SMS 2003 SP3 advanced client is {4A39A27F-005B-407E-8CF5-F4D8065658E4}.The PatchGUID for the English version of SMS 2003 SP3 advanced client is {AB136A5F-6EB5-46A3-9283-13616362BC31}.</li></ol>

To verify the product code for the version of the SMS advanced client that you have installed, follow these steps: <ol> <li> Paste the following text into Notepad: <pre class="fixed_text">On Error Resume Next Dim installer : Set installer = Nothing Set installer = Wscript.CreateObject(&quot;WindowsInstaller.Installer&quot;) : CheckError Dim productName:productName = &quot;SMS Advanced Client&quot; Dim productCode, property, value, message For Each productCode In installer.Products : CheckError If LCase(installer.ProductInfo(productCode, &quot;ProductName&quot;)) = LCase(productName) Then Exit For Next If IsEmpty(productCode) Then Wscript.Echo &quot;Product is not registered: &quot; & productName : Wscript.Quit 2 WScript.Echo &quot;ProductCode = &quot; & productCode </li> <li>In Notepad, click File, click Save As.</li> <li>In the Save As dialog box, type GetACProdCode.vbs .</li> <li>At the command prompt of the SMS advanced client computer, type the following command:

Cscript GetACProdCode.vbs

</li></ol>

Additional hotfix installation information
After you apply this hotfix, you can manually update the v_Add_Remove_Programs view to increase performance. To do this, run the following statement against the SMS database by using SQL Query Analyzer:

<pre class="fixed_text">if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_Add_Remove_Programs]') and OBJECTPROPERTY(id, N'IsView') = 1)

drop view [dbo].[v_Add_Remove_Programs]

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE VIEW dbo.v_Add_Remove_Programs

AS

SELECT    MachineID AS 'ResourceID', InstanceKey AS 'GroupID', RevisionID, AgentID, TimeKey AS 'TimeStamp', ProdID00 AS 'ProdID0',

DisplayName00 AS 'DisplayName0', InstallDate00 AS 'InstallDate0', Publisher00 AS 'Publisher0', Version00 AS 'Version0'

FROM        dbo.Add_Remove_Programs_DATA

UNION ALL

(SELECT    arp64.MachineID, arp64.InstanceKey, arp64.RevisionID, arp64.AgentID, arp64.TimeKey, arp64.ProdID00, arp64.DisplayName00, arp64.InstallDate00,

arp64.Publisher00, arp64.Version00

FROM        Add_Remove_Programs_64_DATA arp64 LEFT JOIN

Add_Remove_Programs_DATA arp32 ON arp64.ProdID00 = arp32.ProdID00 AND arp64.MachineID = arp32.MachineID

WHERE    arp32.ProdID00 IS NULL)

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO Grant select on v_Add_Remove_Programs to smsschm_users

Go

Grant select on v_Add_Remove_Programs to webreport_approle

Go

<div class="workaround_section">

WORKAROUND
To work around this problem, use one of the following methods.

Method 1: Import the SMS 2003 SP2 report version
This method imports two report versions from SMS 2003 SP2. These report versions use an existing view that will not target the 64-bit Address Resolution Protocol (ARP) data. To import these report versions, copy the text from step 1 into a .mof file. Then, import this file to the SMS 2003 SP3 site. For identification, the two reports in this .mof file are renamed. &quot;TEMP_SP2&quot; is added to the beginning of the names of the reports. When the reports are renamed in this manner, the reports do not replace the existing SMS 2003 SP3 version of the reports. The data in that these renamed reports will not include 64-bit programs that are listed in ARP. <ol> <li> Copy the following text into a text editor. Then, save the file by using the .mof file name extension. <pre class="fixed_text">// *********************************************************************************

//

//                            Created by the SMS Export Object Wizard

//

//                            Created Friday, June 29, 2007

//

//                            File Name: Arp_reports.mof

//

// Comments :

//

// Export of two Add_Remove_Programs reports from a SMS 2003 SP2 site.

// For temporary use until post-SP3 issues with these same reports are

// resolved. Will not contain x64 Add_Remove Programs data.

//

// *********************************************************************************

// ***** Class : SMS_Report *****

[SecurityVerbs(140551)]

instance of SMS_Report

{

Category = &quot;Software - Companies and Products&quot;;

Comment = &quot;Displays a summary of all computers that have specific software registered in Add Remove Programs&quot;;

DrillThroughColumns = {};

GraphXCol = 1;

GraphYCol = 2;

MachineDetail = FALSE;

MachineSource = FALSE;

Name = &quot;TEMP_SP2 - Computers that have specific software registered in Add Remove Programs&quot;;

NumPrompts = 2;

RefreshInterval = 0;

ReportParams = {

instance of SMS_ReportParameter

{

AllowEmpty = FALSE;

DefaultValue = &quot;&quot;;

PromptText = &quot;Software Title&quot;;

SampleValueSQL = &quot;begin

\n if (@__filterwildcard = '')

\n Select DISTINCT DisplayName0 FROM v_GS_ADD_REMOVE_PROGRAMS order by DisplayName0

\n else

\n Select DISTINCT DisplayName0 FROM v_GS_ADD_REMOVE_PROGRAMS

\n WHERE DisplayName0 like @__filterwildcard

\n order by DisplayName0

\nend&quot;;

VariableName = &quot;displayname&quot;;

},

instance of SMS_ReportParameter

{

AllowEmpty = FALSE;

DefaultValue = &quot;&quot;;

PromptText = &quot;Collection ID&quot;;

SampleValueSQL = &quot;begin

\n if (@__filterwildcard = '')

\n select CollectionID, Name from v_Collection order by Name

\n else

\n select CollectionID, Name from v_Collection

\n WHERE CollectionID like @__filterwildcard

\n order by Name

\nend&quot;;

VariableName = &quot;CollID&quot;;

}};

SecurityKey = &quot;&quot;;

SQLQuery = &quot;Select sys.Netbios_Name0, fcm.SiteCode, sys.User_Domain0, sys.User_Name0, sys.Operating_System_Name_and0, arp.DisplayName0

\nFROM v_R_System sys

\nJOIN v_GS_ADD_REMOVE_PROGRAMS arp ON sys.ResourceID = arp.ResourceID

\nJOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID

\nWHERE DisplayName0 = @displayname and fcm.CollectionID=@CollID&quot;;

StatusMessageDetailSource = FALSE;

};

// ***** End *****

// ***** Class : SMS_Report *****

[SecurityVerbs(140551)]

instance of SMS_Report

{

Category = &quot;Software - Companies and Products&quot;;

Comment = &quot;Displays a summary of the software installed on a specific computer that is registered in Add Remove Programs&quot;;

GraphCaption = &quot;&quot;;

GraphXCol = 1;

GraphYCol = 2;

MachineDetail = TRUE;

MachineSource = FALSE;

Name = &quot;TEMP_SP2 - Software registered in Add Remove Programs on a specific computer&quot;;

NumPrompts = 1;

RefreshInterval = 0;

ReportParams = {

instance of SMS_ReportParameter

{

AllowEmpty = FALSE;

DefaultValue = &quot;&quot;;

PromptText = &quot;Computer Name&quot;;

SampleValueSQL = &quot;begin

\n if (@__filterwildcard = '')

\n SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS WHERE SYS.Client0=1 ORDER By SYS.Netbios_Name0

\n else

\n SELECT DISTINCT SYS.Netbios_Name0 from v_R_System SYS WHERE SYS.Client0=1

\n and SYS.Netbios_Name0 like @__filterwildcard

\n ORDER By SYS.Netbios_Name0

\nend&quot;;

VariableName = &quot;computername&quot;;

}};

SecurityKey = &quot;&quot;;

SQLQuery = &quot;Select v_GS_ADD_REMOVE_PROGRAMS.DisplayName0, v_GS_ADD_REMOVE_PROGRAMS.Publisher0, v_GS_ADD_REMOVE_PROGRAMS.Version0

\nFROM v_GS_ADD_REMOVE_PROGRAMS

\nJOIN v_R_System ON v_GS_ADD_REMOVE_PROGRAMS.ResourceID = v_R_System.ResourceID

\nWHERE v_R_System.Netbios_Name0 = @computername&quot;;

StatusMessageDetailSource = FALSE;

};

// ***** End ***** </li> <li>Import the reporting .mof file from in the SMS administrative console. To do this, follow these steps: <ol style="list-style-type: lower-alpha;"> <li>Open the SMS Administrator Console, and then expand the Reports node.</li> <li>Right-click the Reports node, click All Tasks, and then click Import Objects to start the Import Object Wizard.</li> <li>Click Next.</li> <li>Type the path of the .mof file that you saved in step 1, and then click Next.</li> <li>Click Next to confirm the name of the two reports that this step will import.</li> <li>In the Comments Review window, click Next.</li> <li>Click Finish to complete the wizard.</li></ol> </li> <li>Refresh the SMS Administrator Console to view and then use the two imported reports.</li></ol>

Method 2: Disable the reporting of the Win32Reg_AddRemovePrograms64 class
If there are no 64-bit clients in the environment, disable the reporting of the Win32Reg_AddRemovePrograms64 class in the Sms_def.mof file. Change the SMS_Report value for the class to FALSE. To do this, follow these steps: <ol> <li>Make a backup copy of the Sms_def.mof file that is located on the SMS site server in the following folder:

\SMS\Inboxes\Clifiles.src\Hinv

</li> <li>Use a text editor to open the original Sms_def.mof file.</li> <li> In the file, locate the following section: <pre class="fixed_text">[ SMS_Report    (TRUE), SMS_Group_Name (&quot;Add Remove Programs (64)&quot;), SMS_Class_ID  (&quot;MICROSOFT|ADD_REMOVE_PROGRAMS_64|1.0&quot;), Namespace     (&quot;\\\\\\\\localhost\\\\root\\\\cimv2&quot;), SMS_Context_1 (&quot;__ProviderArchitecture=64|uint32&quot;), SMS_Context_2 (&quot;__RequiredArchitecture=true|boolean&quot;) ]

class Win32Reg_AddRemovePrograms64 : SMS_Class_Template {   [SMS_Report (TRUE), key ] string ProdID; [SMS_Report (TRUE)     ] string DisplayName; [SMS_Report (TRUE)     ] string InstallDate; [SMS_Report (TRUE)     ] string Publisher; [SMS_Report (TRUE)     ] string Version; }; </li> <li> Use the following text to replace the text that is listed in step 3.

Note The only change is in the first line of text.

<pre class="fixed_text">[ SMS_Report    (FALSE), SMS_Group_Name (&quot;Add Remove Programs (64)&quot;), SMS_Class_ID  (&quot;MICROSOFT|ADD_REMOVE_PROGRAMS_64|1.0&quot;), Namespace     (&quot;\\\\\\\\localhost\\\\root\\\\cimv2&quot;), SMS_Context_1 (&quot;__ProviderArchitecture=64|uint32&quot;), SMS_Context_2 (&quot;__RequiredArchitecture=true|boolean&quot;) ]

class Win32Reg_AddRemovePrograms64 : SMS_Class_Template {   [SMS_Report (TRUE), key ] string ProdID; [SMS_Report (TRUE)     ] string DisplayName; [SMS_Report (TRUE)     ] string InstallDate; [SMS_Report (TRUE)     ] string Publisher; [SMS_Report (TRUE)     ] string Version; }; </li> <li>Save the file to the \SMS\Inboxes\Clifiles.src\Hinv folder. Use the file name &quot;Sms_def.mof.&quot;</li> <li>Open the :\SMS\Logs\Dataldr.log file, and then look for text that resembles the following text:

Warning: could not verify/remove hardware inventory data item record (DataItem id {3688ef6e-b9fe-4e45-be29-f5b211850c37}) during MOF class enumeration - 0x80004005

</li> <li>On the SMS SQL Server database, run the following statement:

Delete From DataItemContext where DataItemId = '{3688ef6e-b9fe-4e45-be29-f5b211850c37}'

Note Replace the GUID by using the GUID that is found in the Dataldr.log file.</li></ol>

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.

<div class="moreinformation_section">

MORE INFORMATION
For more information about software update terminology, click the following article number to view the article in the Microsoft Knowledge Base:

824684 Description of the standard terminology that is used to describe Microsoft software updates

Keywords: kbtshoot kbprb KB939872

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.