Expensive aggregate query in CMDB Get Well Metric Collection job - Known Error

Expensive aggregate query in CMDB Get Well Metric Collection job Description Inefficient queries from daily job: "CMDB Get Well Metric Collection" causing DB CPU to spike

Steps to Reproduce

On customer's instance (~1 billion records)

Issue Definition: - Issue 1 - EXPENSIVE aggregate query on sys_object_source table where target table are children of CMDB_CI_VM_INSTANCE and CMDB_CI_HARDWARE which ran for more than 4 hours. (Update: Aug 2022, this query has been optimized in v2.0 version of the dashboard, which was released to the store on August 5, 2022. Please consider upgrading to this version) - Issue 2 - EXPENSIVE aggregate query on cmdb_rel_ci table to find orphaned rels ran for 1hr 17 min.

- Issue 3 - EXPENSIVE aggregate query on cmdb_ci_hardware table

Steps to Reproduce: Run the Job "CMDB Get Well Metric Collection"

Workaround This defect is fixed in version 2.3.5 of CMDB and CSDM Data Foundations Dashboards store app. Please update to this version for a permanent fix.

If you are unable to upgrade the store app, pleas check the following workarounds:

If you have version 2.2 or higher of the store app "CMDB and CSDM Data Foundations Dashboards", a new feature to turn off the specific metric job by setting the relevant record to active = false in CMDB/CSDM Get Well Metric Table(sn_getwell_metric). Users with ITIL Admin and Asset roles have write access to the table. The PA Job Indicator column describes which dashboard component will be affected.

1. Go to sn_getwell_metric table: https:// .service-now.com/sn_getwell_metric_list.do

2. Locate the specific metric or any other metrics that's causing performance issues.

3. Set active field to false.

Lower versions can go directly to the scheduled job definition and comment out some lines from the script field:

1. For " CMDB Get Well Metric Collection " scheduled job

Go to the following scheduled job: https:// .service-now.com/nav_to.do?uri=sysauto_script.do?sys_id=d4a09b6653b8501032b7ddeeff7b12c7

Comment out the following lines:

new sn_getwell.CMDBGetWellCalculateOrphanedCIrelationships().calculateOrphanedCIrelationships(); new sn_getwell.CMDBGetWellCalculateCIsMissingSN().calculateCIsMissingSN(); new sn_getwell.CMDBGetWellCalculateCIsNotProcessViaIRE().calculateCIsNotProcessViaIRE(); Deactivating these lines will impact dashboard contents for "Orphan CI Relationships", "Hardware CIs Missing Serial Numbers" and "CIs not Processed by IRE" 2. For " CSDM Get Well Metric Collection " scheduled job

Go to the scheduled job: https:// .service-now.com/nav_to.do?uri=sysauto.do?sys_id=a0b393e653b8501032b7ddeeff7b1269

Comment out the following lines:

new sn_getwell.CSDMGetWellCalculateBizAppsMetrics().calculateBizAppsMetrics(); new sn_getwell.CSDMGetWellCalculateBizOffMetrics().calculateBizOffMetrics(); Deactivating these will impact dashboard components for Business Services metrics.