Configuration item dynamic filter

[Nitin Panchal]

Configuration item field is frequently used field around all ITSM module. Now what if organization has millions of records in CMDB ? Would you let your user select configuration item field through reference field and let them wait for few minutes to load whole configuration items. 

Here I would like to share a technique which will let user select their configuration item field faster and better. 

1) Create reference table in which you define what cmdb classes you would like your user to see. Something like below

 

2) Override reference qualifier in configuration item field on change 

javascript: new RefQualUtilChangeCiClassFilter().getCiFilterForChange(current)

3) Make sure that overriden attributes has order by column: ref_ac_order_by=name

4) Create client callable script include 

var RefQualUtilChangeCiClassFilter = Class.create();
RefQualUtilChangeCiClassFilter.prototype = Object.extendsObject(AbstractAjaxProcessor, {
	getCiFilterForChange: function (current) {
		var allCIData = '';
		
		//Get the category field from change field. For example server OR database etc. 
		// Make sure that this category and its CI classes are defined in reference table u_change_category_cmdb_lookup
		var categoryListing=current.category;

		//Returns only active servers OR use any other query to join filtered query 
		var encodedqueryBase='^operational_status!=6^operational_status!=2^nameISNOTEMPTY';
		
		if(current.category != ''){
			var cmdbLookupMap = new GlideRecord('u_change_category_cmdb_lookup');
			cmdbLookupMap.addQuery('u_change_ci_category', categoryListing);
			cmdbLookupMap.query();
			//gs.addInfoMessage('category type:- '+categoryListing);
			while (cmdbLookupMap.next()) {
				var dynamicQuery='';
				if(cmdbLookupMap.u_change_ci_lookup_table !=''){
					dynamicQuery += this.getAllCIInstanceOf(cmdbLookupMap.u_change_ci_lookup_table);
					allCIData +=dynamicQuery + '^OR';
				}
				
			}
			
			if (allCIData != '') {
				allCIData = allCIData.slice(0, -3); //Removing last three character ^OR. 
				gs.addInfoMessage('change query --> '+ allCIData+encodedqueryBase);
				return allCIData+encodedqueryBase;
			}
		}

    },
    
    //Function will return string like - sys_class_pathSTARTSWITH/!!/!2/!(/!!/!)/!#^
    getAllCIInstanceOf: function (cmdbinstanceof) {
		
        var returnedCI = '';
        var CMDBTableInfo = new GlideRecord("sys_db_object");
        CMDBTableInfo.get("name", cmdbinstanceof);
        var cmdbSysClassPath = CMDBTableInfo.getValue("sys_class_path");
        if (cmdbSysClassPath != '') {
            returnedCI = "sys_class_pathSTARTSWITH" + cmdbSysClassPath;
        }
        return returnedCI;
	},
	type: 'RefQualUtilChangeCiClassFilter'
});

Above function getAllCIInstanceOf will return string of sys_class_path like below for server records cmdb_ci_aix_server , cmdb_ci_app_server_java and cmdb_ci_server

 sys_class_pathSTARTSWITH/!!/!2/!(/!!/!)/!#^ORsys_class_pathSTARTSWITH/!!/!(/!)/!2^ORsys_class_pathSTARTSWITH/!!/!2/!(/!!

Now you could query CI class tables and combine sys_ids but it will take a lot more time to load configuration item records.  This method of filtering CMDB CI classes has been proved to be a lot of faster than combining sys_ids of all CMDB_CI classes. 

Also in future if you decide to add more classes in your filter then all you have to do is

1) Add choice field in change category field. If not exist. 

2) Add a record in reference table u_change_category_cmdb_lookup. For example if new database class needs to be added then add a field Change CI category database and Change CI lookup table : cmdb_ci_db_sql_catalog in table u_change_category_cmdb_lookup 

Thanks for reading. Please feel free to comment and suggestions .