IT Analytics Patch Management Cube is not processing

Products

• IT Analytics
• IT Management Suite

Issue/Introduction

Noticed that "Applicable Patches" and "Patch Vulnerabilities" measure groups failed to process for the "Patch Management" Cube in ITA (IT Analytics). Those have been timing out. The other measure groups ran just fine.

Tried increasing the default time outs (as suggested under "Cube processing timeout error prior to job completion" KB 162315 ) but still not improvements.

Then tried running the following query for example against the main view used by  "Applicable Patches" measure group:

SELECT TOP (1000) [ResourceId]
      ,[ResourceGuid]
      ,[SWUGuid]
      ,[ReleaseDate]
  FROM [Symantec_CMDB].[dbo].[vITAnalytics_CMDB_ApplicablePatchFact]

As far as he can see, this query took more than 12 minutes to return top 1000 from "vITAnalytics_CMDB_ApplicablePatchFact" in their environment.

Environment

ITA 8.7

Cause

Known Issue. There were some functions and views used by Patch Management cube that needed optimization:

Fixed Performance Issues for SQL-functions:
 - fnITAnalytics_CMDB_VulnerablePatches
 - fnITAnalytics_CMDB_ApplicablePatches
and some views

Resolution

This issue has been fixed in the ITA 8.7.1 release.

In the meantime, you can try the following workaround:

1. Download the attached "Drop1.sql" fixed drop-in.
2. Apply the sql file using SQL Management studio and execute them for "Symantec_CMDB" database:
   
   Open SQL Management studio and open new query for "Symantec_CMDB" database > execute "Drop1.sql" 
   
   
3. Download attached "Drop2.sql"
4. Open SQL Management studio and open new query for "Symantec_CMDB" database > execute "Drop2.sql" 
   
   Note:
   These messages are expected and there is not a problem
   "The module 'spITAnalytics_CSMP_CreateViews' depends on the missing object 'dbo.spITAnalytics_CMDB_GetFullyQualifiedDataSource'. The module will still be created; however, it cannot run successfully until the object exists."
   
   
5. Then, run the patch management cube and wait for the process to finish as expected.