Run two queries and do something based on whether either query returns a result

[Andrew Bettcher]

Hi,

I want to run two queries and then, if either query returns any result, do something. I can't post my code here but it would be something like this:

var gr1 = new GlideRecord('table1');
gr1.query();

var gr2 = new GlideRecord('table2');
gr2.query();

if (gr1 || gr2){
gs.info('Result found');
}

This will be run as a fix script and the table is huge so it's taking hours to run and, depending on what else is happening on the instance, may self cancel with the error message "Available memory is almost depleted". Therefore, I want to make sure that I've got it more or less right before running it.

Can anyone confirm if this is the right approach?

Also, is there a way for me to output from which table the result came from?

[Ahmmed Ali]

Hello @Andrew Bettcher 

Could you please explain more about the use case? are you looping through each record in table1 and subsequently in table2 OR based on query.

as you say both tables have large data and if you are looping trough each record then it will impact the performance and may also cancel the transaction.

Thank you,

Ali

[Andrew Bettcher]

I've decided to post my entire script with some amendments to remove any recognisable references.

It's a long story but my intention is to find duplicates within a CI class and then look for the sys_ids of those duplicate CIs within a series of specific answers ('value') to questions on the sc_item_option table  and the sc_multi_row_question_answer table.

The idea is that if there is a reference I can update that reference so it points to the main CI and, if not, I can simply delete the duplicate in favour of the main CI.

// determines which devices have duplicates and groups them together in duplicate groups using GlideAggregate.

// array of device types that can be excluded from the CI Class table query		
var deviceTypes = [
//	'Standard Laptop',
// 	'Standard Virtual Desktop',
// 	'Workstation',
// 	'Standard Desktop',
// 	'High Spec Laptop',
// 	'Enhanced Virtual Desktop',
//	'High Spec Tablet (2-in-1)',
	'Apple Mac',
	'Standard Tablet (Companion)'];

// group and count devices that are in the device type categories listed above by unique ID
var prAgg = new GlideAggregate('u_cmdb_ci_specific_devices');
prAgg.addAggregate('COUNT', 'u_unique_id');
prAgg.addQuery('u_device_type', 'IN', deviceTypes);
prAgg.query();
while (prAgg.next()){

	if (prAgg.getAggregate('COUNT', 'u_unique_id') > 1) {
		// now we have a duplicate unique ID
		// get sys_id of the MainCI

		var groupedDuplicates = new GlideRecord('u_cmdb_ci_specific_devices');
		groupedDuplicates.addQuery('u_unique_id', prAgg.getValue('u_unique_id'));
		groupedDuplicates.orderByDesc('sys_created_on');
		groupedDuplicates.query();

		groupedDuplicates.next();
		var mainCI = groupedDuplicates.getValue('sys_id');
		var otherCI = [];
		while (groupedDuplicates.next()) {

			// push non-Main CIs from the duplicate groups into otherCI array
			otherCI.push(groupedDuplicates.getValue('sys_id'));

		}
		// 		gs.print('Main CI: ' + mainCI + ' ' + groupedDuplicates.getValue('name'));
		// 		gs.print('Other CI: ' + otherCI + ' ' + groupedDuplicates.getValue('name'));

		//varFields sys_ids are reference fields on the item_option_new table that refer to the device table
		var varFields = [
			'7160d8dddb726700fe7ee2e15b961998',  //machine_name from User Owned Devices variable set
			'ecdb817fdbf08014f2821bbf29961995',  //machine_name from User Owned VDI variable set
			'f8903ae11b91b850da86a609b04bcb89'   //machine_name from User Owned Tech variable set
		];

		// look up all of the answers on the mrvs answer table (for questions in varFields) that match the sys_id of otherCIs from the array

		var mrvsCheck = new GlideRecord('sc_multi_row_question_answer');
		mrvsCheck.addNotNullQuery('value');
		mrvsCheck.addQuery('item_option_new', 'IN', varFields);
		mrvsCheck.addQuery('value', 'IN', otherCI.toString());
		mrvsCheck.query();

		//scVarFields sys_ids are reference fields on the sc_item_option table that refer to the device table
		var scVarFields = [
			'7160d8dddb726700fe7ee2e15b961998',
			'a8203ceadb29ab00fe7ee2e15b9619bd',
			'ecdb817fdbf08014f2821bbf29961995',
			'f8903ae11b91b850da86a609b04bcb89',
			'755027d41b4a29d4da86a609b04bcb72',
			'5b3595e9dbb6fb00f2821bbf29961900',
			'16699840dbd76f00f2821bbf2996192e',
			'7e4a2ce2dbc80090fe7ee2e15b96198a',
			'53099d7edb752740fe7ee2e15b9619b1'
		];
		
		//look up answers to the questions referenced above on the sc_item_option table and use duplicate device sys_ids to query

		var itemOptionCheck = new GlideRecord('sc_item_option');
		itemOptionCheck.addNotNullQuery('value');
		itemOptionCheck.addQuery('item_option_new', 'IN', scVarFields);
		itemOptionCheck.addQuery('value', 'IN', otherCI.toString());
		itemOptionCheck.query();

		if (mrvsCheck.hasNext || itemOptionCheck.hasNext) { //updated following advice from Peter Bodelier
			gs.print('Match found on ' + mrvsCheck.getValue('sys_id') + ' ' + mrvsCheck.getValue('row_index'));
			gs.print('Match found on ' + itemOptionCheck.getValue('sys_id') + ' ' + itemOptionCheck.getValue('row_index'));
		}

		// script here will update MRVS answers to the main CI sys_id and then delete the otherCI records.
	}
}

[Andrew Bettcher]

Note that the array of device types is limited to a small number to reduce processing time initially.

[Peter Bodelier]

Hi @Andrew Bettcher,

I'm not sure I really understand the use case, but I think you're almost there in terms of this demo script:

var gr1 = new GlideRecord('table1');
//gr.setLimit(1);
gr1.query();

var gr2 = new GlideRecord('table2');
//gr2.setLimit(1);
gr2.query();

if (gr1.hasNext() || gr2.hasNext()){
gs.info('Result found');
}

I've added 2 commented lines, which you could uncomment for testing purposes. It stops the query when 1 record has been found, so that you can safely test.