I have written a fix script to get some details from sys_audit for almost 1lakh data

[Ap_1]

I have written a fix script to fetch some info from Computer table, also fetch data from sys_audit table for each computer records in an excel.

Now the fix script is working for 1month data (2k data) but while I am trying to run it for 1lakh data, the transaction is getting canceled with message "Available memory is almost depleted"

Script I have attached.

Can anyone please help me on this.

[-O-]

I would use GlideAggregate, max and min functions to get the two date extremes, would query sys_audit only with join to cmdb_ci_computer.

Thus you would make sure that most of what now happens in the app server would actually happen in the DB - as it should whenever possible.

It would also actually make it possible to calculate time spend vs. how where it will always be 0.

[Ap_1]

Hi,

Are you suggesting to use, GlideAggregate for the Count and max and min function for getting the first and last data creation time?

[-O-]

Yes, to get count, max and min, but also all the other fields.

[-O-]

I wonder if this would work (this attaches the result to the current user's record):

;
(function () {
	var $cache = {},
		$gr = new GlideAggregate('sys_audit'),
		$gqc = $gr.addJoinQuery('cmdb_ci_computer', 'documentkey', 'sys_id');

	$gqc.addCondition('sys_created_on', '>', 'javascript:gs.yearsAgo(4)');

	$gr.addQuery('fieldname', 'assigned_to');

	gs.debug($gr.getEncodedQuery());

	$gr.addAggregate('count');
	$gr.addAggregate('min', 'sys_created_on');
	$gr.addAggregate('max', 'sys_created_on');

	$gr.groupBy('documentkey');

	$gr._query();

	while ($gr._next()) {
		$cache['' + $gr.documentkey] = { 'count': +$gr.getAggregate('count'), 'max': $gr.getAggregate('max', 'sys_created_on'), 'min': $gr.getAggregate('min', 'sys_created_on'), };
	}

	var $cmdb_ci_computer = new GlideRecord('cmdb_ci_computer');

	$cmdb_ci_computer.addQuery('sys_created_on', '>', 'javascript:gs.yearsAgo(4)');

	gs.debug($cmdb_ci_computer.getEncodedQuery());

	$cmdb_ci_computer._query();

	var csvRecords = ['"Name","Serial Number","Unique ID","Support Group","Managed by","Managed by Group","Count","Time Spent"'];

	while ($cmdb_ci_computer._next()) {
		var uniqueValue = $cmdb_ci_computer.getUniqueValue(),
			summary = $cache[uniqueValue] || { 'count': '#N/A', '': '#N/A', },
			csvFields = ['name', 'serial_number', 'u_unique_id', 'support_group', 'managed_by', 'managed_by_group']
			.map(toCSVField($cmdb_ci_computer))
			.concat([summary.count, gs.dateDiff(summary.min, summary.max, true)]);

		csvRecords.push(csvFields.join(','));
	}

	var $gsa = new GlideSysAttachment();

	$gsa.write(getUserRecord(), 'cmdb_ci_computer.' + gs.nowNoTZ() + '.csv', 'text/csv', csvRecords.join('\n'));

	gs.debug(csvRecords.join('\n'));

	function toCSVField ($gr) {
		return function (fieldName) {
			var element = $gr.getElement(fieldName);
			return !$gr.isValidField(fieldName) || element.nil() ? '' : ('"' + element.getDisplayValue().replace(/"/g, '""') + '"');
		};
	}

	function getUserRecord () {
		var $gr = new GlideRecord('sys_user');
		return $gr.get(gs.getUserID()) ? $gr : undefined;
	}
})();