Enhance the efficiency of Glide queries in deletion strategy scripts that target the 'Comment' field - Support and Troubleshooting

Enhance the efficiency of Glide queries in deletion strategy scripts that target the 'Comment' field Overview Some of the Azure and AWS deletion strategy scripts are taking long time which are using the comment field in Glide queries and causing slow query execution. Addressed slow query issue in below scripts:

# Pattern CI Deletion Strategy Script 1 Amazon AWS - Executable Template (LP) cmdb_ci_os_template ExecutableTemplateAwsGetCi 2 Amazon AWS - Owned Template (LP) cmdb_ci_os_template OwnedTemplateAwsGetCi 3 Amazon AWS - Virtual Server (LP) cmdb_ci_os_template VirtualServerAwsGetCi 4 Amazon AWS - Virtual Server (LP) cmdb_ci_dns_name VirtualServerAwsGetCi 5 Amazon AWS - Classic LB (LP) cmdb_ci_dns_name ClassicLbAwsGetCi 6 Amazon AWS - Classic LB (LP) cmdb_ci_cloud_load_balancer ClassicLbAwsGetCi 7 Amazon AWS - Classic LB (LP) cmdb_ci_cloud_lb_ipaddress ClassicLbAwsGetCi 8 Amazon AWS - Classic LB (LP) cmdb_ci_lb_pool ClassicLbAwsGetCi 9 Amazon AWS - Classic LB (LP) cmdb_ci_lb_service ClassicLbAwsGetCi 10 Amazon AWS - Application and Network LB (LP) cmdb_ci_dns_name AppAndNetLbAwsGetCi 11 Amazon AWS - Application and Network LB (LP) cmdb_ci_cloud_load_balancer AppAndNetLbAwsGetCi 12 Amazon AWS - LB Pool (LP) cmdb_ci_lb_pool PoolAppAndNetLbAwsGetCi 13 Amazon AWS - LB Service (LP) cmdb_ci_lb_service ServiceAppAndNetLbAwsGetCi 14 Amazon AWS - NIC (LP) cmdb_ci_cloud_lb_ipaddress NicAwsGetCi 15 Azure - Virtual Machine (LP) cmdb_ci_os_template VmAzureGetCi 16 Azure - Virtual Machine (LP) cmdb_ci_storage_volume VmAzureGetCi 17 Azure - Storage Blobs(LP) cmdb_ci_storage_volume BlobsAzureGetCi 18 Azure - Storage Volume (LP) cmdb_ci_storage_volume StorageAzureGetCi 19 Azure - Image (LP) cmdb_ci_os_template ImageAzureGetCi 20 Azure - Classic LB (LP) cmdb_ci_cloud_load_balancer ClassicLbAzureGetCi 21 Azure - Classic LB (LP) cmdb_ci_lb_service ClassicLbAzureGetCi 22 Azure - Classic LB (LP) cmdb_ci_lb_pool ClassicLbAzureGetCi 23 Azure - Classic LB (LP) cmdb_ci_cloud_lb_ipaddress ClassicLbAzureGetCi 24 Azure - Route Table (LP) cmdb_ci_route_table RouteTableAzureGetCi 25 Azure - Local Network Gateway (LP) cmdb_ci_virtual_pvt_gateway LocalGatewayAzureGetCi 26 Azure - Private Gateway (LP) cmdb_ci_virtual_pvt_gateway PrivateGatewayAzureGetCi

Release This feature is available in the following release.

" Discovery and Service Mapping Patterns ( sn_itom_pattern)" version 1.13.0 - May 2024 or later

Implementation Before Fix Executing the following Glide query within Deletion strategy scripts on a resource class (for example: cmdb_ci_cloud_load_balancer). var relGr = new GlideRecord(ciClass); relGr.addQuery('comments', comment); relGr.addQuery('sys_updated_on', ' In the above query inputs : ciClass resource table name scheduleStartTime Discovery schedules start time comment Construct comment filed value this._deleteLayerExcludes() ['2','7'] this._deleteLayerSources() ['ServiceNow'] Output : CiSysId List of CI Sys Ids potentially applicable for Deletion Strategy Details about query Resource table Class has indexes on 'sys_class_name' , 'sys_updated_on' columns. Initially, the Query Optimizer filter records based on these indexed fields. However Index fields, filter out only Cis that are updated in current discovery. For instance, If table contains 1M CIs, after applying index field queries(assume updated CIs in current discovery are 1K ), the table subset still contains approximately 1M CIs (Total number of CIs in the table minus updated Cis in the current discovery). After applying the index field conditions, the table subset still contains ~ 1M records to which the remaining query conditions(Non-Index fields) must be applied. This process is inefficient and can lead to performance issues. After Fix Executing the following Glide query within Deletion strategy scripts on a CI Relationship (cmdb_rel_ci) class. var relGr = new GlideRecord('cmdb_rel_ci'); relGr.addQuery('child', 'IN', childSysIds); relGr.addQuery('parent.sys_class_name', this.ciClass); relGr.addQuery('parent.sys_updated_on', '<=', this.scheduleStartTime); relGr.addQuery('parent.operational_status', 'NOT IN', this.deleteLayerExcludes); relGr.addQuery('parent.discovery_source', 'IN', this.deleteLayerSources); if (this.isCommentIncludeInQuery) { relGr.addQuery('parent.comments', 'IN', comments); } relGr.setLimit(this._limit); relGr.query(); while (relGr.next()) { ciSysId.push(relGr.parent.sys_id + ''); } In the above query inputs : childSysIds Input datacenters this.ciClass resource table name this.scheduleStartTime Discovery schedules start time comment Construct comment filed value this.deleteLayerExcludes ['2','7'] this.deleteLayerSources ['ServiceNow'] Output : CiSysId List of CI Sys Ids potentially applicable for Deletion Strategy Details about query The CI Relationship Table has indexes on 'child' , 'parent' columns. Initially, the Query Optimizer filter records based on these indexed fields. However Index fields, filter out CIs where the Child is in the input datacenter, the Parent class is a resource Class and updated in current discovery. For instance, If table contains 1M CIs, after applying index field queries(assume input datacenter has 10K with resource class and current discovery 1K records are updated in current discovery), the table subset contains approximately 9K CIs (Total number of resource types CIs having a relationship with input datacenters minus updated Cis in the current discovery). After applying the index field conditions, the table subset contains 9K records to which the remaining query conditions(Non-Index fields) will be applied. This process is efficient and resolves the performance issues.

**** END of TOI ****