KNOWN ISSUE: Drilldown from "Count of Distinct Add/Remove Program Applications" report doesn't work for unicode rows.

Article:TECH172269  |  Created: 2011-10-20  |  Updated: 2011-10-20  |  Article URL http://www.symantec.com/docs/TECH172269
NOTE: If you are experiencing this particular known issue, we recommend that you Subscribe to receive email notification each time this article is updated. Subscribers will be the first to learn about any releases, status changes, workarounds or decisions made.
Article Type
Technical Solution

Environment

Subject

Issue



When the "Reports > Asset & Inventory  > Windows > Software/Applications > Add/Remove Programs > Count of Distinct Add/Remove Program Application" report is run, and a row containing unicode characters is selected for the "View Computers by Application and Version" drilldown, no results are returned.  If this drilldown is ran for a row not containing unicode data it does return results.

 

Findings by using a snippet of the reports query:

SELECT DISTINCT   
 i.[Name] [Name],  
 car.[Name] [Application Name],  
 car.[Version],  
 car.[Publisher],
 a1.[_ResourceGuid] [ResourceGuid]  
FROM  dbo.vComputer i    
 JOIN dbo.Inv_AeX_OS_Add_Remove_Programs_spt a1  
  ON a1.[_ResourceGuid] = i.Guid  
 JOIN dbo.Cmn_OS_Add_Remove_Programs_Common car  
  ON car.[_KeyHash] = a1.[_KeyHash] 
WHERE car.Name = 'Yahoo!ツールバ'

Running the above query in SQL did not yield any results. 


SELECT DISTINCT   
 i.[Name] [Name],  
 car.[Name] [Application Name],  
 car.[Version],  
 car.[Publisher],
 a1.[_ResourceGuid] [ResourceGuid]  
FROM  dbo.vComputer i    
 JOIN dbo.Inv_AeX_OS_Add_Remove_Programs_spt a1  
  ON a1.[_ResourceGuid] = i.Guid  
 JOIN dbo.Cmn_OS_Add_Remove_Programs_Common car  
  ON car.[_KeyHash] = a1.[_KeyHash] 
WHERE car.Name LIKE 'Yahoo%'

Using the above where clause returned all applications like ‘Yahoo%’ including Japanese names. Also, using below WHERE clause returned all rows with Yahoo!ツールバー as expected.
WHERE car.Name = N'Yahoo!ツールバー'


Environment



Inventory Solution 6.1.1081 (Service Pack 3)

Notification Server 6.0 SP3 Rollup 11


Cause



Although the columns in the tables that the report uses have the datatype nvarchar, the reports SQL uses varchar.  Also, when dealing with unicode strings, the string value must be preceded with a N (National Language) which the reports SQL did not do.


Solution



The report has now been modified as follows:

Level 0 -

DECLARE & SET datatypes changed from varchar to nvarchar (nvarchar is required for unicode)

Level 1 -

DECLARE datatypes changed from varchar to nvarchar (nvarchar is required for unicode)

SET = values preceded with a N (required when dealing with unicode strings - http://support.microsoft.com/kb/239530)

 

 

Download and extract the attached "TECH172269.zip" file, and then right-cick the "Reports > Asset & Inventory  > Windows > Software/Applications > Add/Remove Programs" folder, choose Import and then browse to the "Count of Distinct Add_Remove Program Applications report modified.xml" file.  The import action will overwrite the existing report.

The zip file also contains the original report xml file for rollback purposes.


Attachments

Original and modified report xml files.
TECH172269.zip (8 kBytes)


Article URL http://www.symantec.com/docs/TECH172269


Terms of use for this information are found in Legal Notices