SG-SCCM staging table Computer Identity - onBefore script

[Kenneth3]

Hi,

We have the SG-SCCM connection in place, but before I want to do the transform I want to execute a business rule/script (like the onBefore script in SCCM 2016) per record. For this I have to compare the serial number of each record in the staging table with the serial number in the Computers (cmdb_ci_computer) table. If there is a match according to the condition in the business rule/script I want to update the Computers (cmdb_ci_computer) table.

My question is, is the staging table Computer Identity (sn_sccm_integrate_sccm_2019_computer_id) the right table for doing this?

Because the Robust Transform Engine (RTE) and the robust import set transformer is fairly new to me, I like to confirm this and maybe some people already have tackle this kind of issues. And have some good pointers.

Kind regards,
Kenneth

[#2]

Could you expand upon what you are trying to update on the computer?

SG-SCCM goes through the IRE so if the serial number(s) (could be multiple on cmdb_serial_number) match then the IRE will update the computer with the values mapped.  If you have modified the OOTB IRE rules for computer you may have inadvertantly changed this behavior.  The IRE is there to identify if a record exists and if so it will be updated otherwise a new record will be created.  If there is a specific field you want to update that isn't part of the current mapping you could use IH-ETL to add the field and value and it will be passed along for insert/update.

[Kenneth3]

Some assets (laptops) are created via scanning the barcode with a prefix S before the serial number. Besides the asset also a CI (the device) is created with a serial number with a prefix S. In the old situation (with SCCM 2016) we had an onBefore script that was comparing those records from the staging table (Conputer Identity) with the Computers table and updated the records (in the Computers table) with a prefix S according to the serial numbers (without prefix S) of the data source SCCM. This way no duplicates where created.

Everything now with SG-SCCM is OOB, but we have to remediate this issue. That's why I am asking the former question. Hope this clarifies the issue more.

[#4 · Accepted solution]

Interesting use case.  Here are a couple of ideas.

1. You could modify the business rule/script include on alm_asset that creates the CI ("Create CI on insert" and "AssetandCI" likewise) so that it removes the S prefix from the scanned barcode.  This would be the most effecient way of handling this and would also fix the issue of incorrect data in the CMDB.  The issue right now can be worked around for SCCM but if you go to a multisource CMDB you would have to tackle this problem with every data source as the serial numbers in the CMDB are incorrect.
2. You could handle it using and a business rule on sys_import_set to check when an import on sn_sccm_integrate_sccm_2019_computer_id is updated to the loaded state.  From there you could run a script to update the serial numbers in the cmdb from those in the import set.  This would be more efficient than a BR on the sn_sccm_integrate_sccm_2019_computer_id table as it would allow batching instead of running for each individual record created.

[Kenneth3]

Thanks, I like your second idea the most when this is less difficult to apply I think. For this second idea, should you create one business rule:

1- with the appropriate filters on the tab 'When to run' (see screenshot 1)?

2 - with a script on the tab 'Advanced' (see screenshot 2)?

 

Script:

(function executeRule(current, previous /*null when async*/) {

	var sSerialNo = source.u_biosserialnumber; 

	if ((!gs.nil(sSerialNo)) && (sInstalledDate >= gs.beginningOfLast90Days())) { 	//SerialNo Not Nil and its Client Installed Date in Last 90 days
		var grComputer = new GlideRecord('cmdb_ci_computer');
			grComputer.addNullQuery('correlation_id'); 	//New Added by Discovery Team
			grComputer.addQuery('serial_number', 'DOES NOT CONTAIN', 'vmware');
			grComputer.addQuery('serial_number', 'DOES NOT CONTAIN', ':');
			grComputer.addQuery('serial_number', 'ENDSWITH', sSerialNo);  //Hier doe je de vergelijking met het serial number!!
			grComputer.query();

	while (grComputer.next()){ 
		if (sSerialNo.toString() == grComputer.serial_number.toString()) 
		{	//No Discrepancy in Serial Number		
     //} else if ((sSerialNo.toString().length >= 6) && (grComputer.getRowCount() == 1) && (sSerialNo.toString() != grComputer.serial_number.toString())) {	     //Discrepancy in Serial Number
		}
		else if ((sSerialNo.toString().length >= 6) && (sSerialNo.toString() != grComputer.serial_number.toString())) 
		{	//Discrepancy in Serial Number
				grComputer.comments = 'SCCM Update -- Old SerialNo[' + grComputer.serial_number + ']' ; 
				grComputer.serial_number = sSerialNo; 
				grComputer.update();

		}
		else
		{
			gs.info('No discripency found');
		}
	}
}

ignore = true;

})(current, previous);