Incorrect SQL query used in Assets SCCM integration to populate the Application object type

Summary

As per Insight SCCM integration - Used SQL statements, we use 3 SQL queries to populate the Application object type.

SELECT [ResourceID], [TimeStamp], [DisplayName0], [InstallDate0], [Publisher0], [Version0] FROM [v_GS_ADD_REMOVE_PROGRAMS_64]
 
SELECT [ResourceID], [TimeStamp], [DisplayName0], [InstallDate0], [Publisher0], [Version0] FROM [v_GS_ADD_REMOVE_PROGRAMS]
 
SELECT [ResourceID] ,[TimeStamp], [NormalizedName] AS [DisplayName0], [InstallDate0], [NormalizedPublisher] AS [Publisher0], [NormalizedVersion] AS [Version0], [InstalledLocation0] FROM [v_GS_INSTALLED_SOFTWARE_CATEGORIZED]

In the last query, we get the normalized value of an application publisher and version, which could be different from the publisher and version value from the first 2 queries. This results in duplicated objects to be created.

For example, the first 2 queries would return the result of Publisher0 and Version0 as follows.

The third query would return the result of Publisher0 and Version0 as follows.

Expected result

Disclaimer: The queries are returning results correctly as per the queries used, but it's causing duplicated objects to be created as the normalized columns are used in the third query, which is different than the other 2 queries
Imported data is not normalized

Actual result

Imported data is normalized as we can see that the third query (below) uses the normalized columns (NormalizedName, NormalizedPublisher and NormalizedVersion)

Suggestion

• Change the last query to map the non-normalized values of application name, publisher and application version, and have additional data for the normalized values.
• Example query

  SELECT [ResourceID] ,
  [TimeStamp], 
  [ProductName0] AS [DisplayName0], -- actual value of application name
  [NormalizedName] AS [NormalizedName0], -- normalized value of application name
  [InstallDate0], 
  [Publisher0] AS [Publisher0], -- actual value of Publisher
  [ProductVersion0] AS [Version0],  -- actual value of Version
  [NormalizedPublisher] AS [NormalizedPublisher0], -- normalized value of Publisher
  [NormalizedVersion] AS [NormalizedVersion0], -- normalized value of Application Version
  [InstalledLocation0] 
  FROM [v_GS_INSTALLED_SOFTWARE_CATEGORIZED]
  

• Allow SQL queries to be modified in the app

Workaround

Currently there is no known workaround for this behavior. A workaround will be added here when available