Uploaded image for project: 'Jira Service Management Data Center'
  1. Jira Service Management Data Center
  2. JSDSERVER-11890

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





      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.

      Publisher0 Version0
      Google Inc 103.0.5060.114
      Google Inc 90.0.4430.72

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

      Publisher0 Version0
      Google 103.0
      Google 90.0

      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)


      • 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] ,
        [ProductName0] AS [DisplayName0], -- actual value of application name
        [NormalizedName] AS [NormalizedName0], -- normalized value of application name
        [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
      • Allow SQL queries to be modified in the app


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


        Issue Links



              Unassigned Unassigned
              michin Michelle Chin
              0 Vote for this issue
              5 Start watching this issue



                Backbone Issue Sync