Fix Script to import Data from JSON file to Import Set table behaves differently in PDI and Organizational Instance

[Siva Kedari Vee]

Hi All,

I got a requirement from my customer to import data from a JSON file to ServiceNow Database instance tables. As the JSON is not formatted properly, I used the attachment API in a fix script to parse the data from the JSON file and insert that in to a staging table in ServiceNow. This works fine till last week but all of a sudden the fix script behaves differently in my organizational instance(If the JSON has 222 records the script is inserting 1998 records in to the staging table which is 9 times of 222 and this behavior is observed recently ) and if I use the same in my PDI it works perfectly and it inserts 222 records only. I'm not sure why this is happening. Any help would be appreciated.

I'm attaching the fix script and sample JSON record Here.

/* Sample JSON
{
"results": [
    {
      "accountId": "008755636378",
      "configuration": {
        "engineVersion": "5.3.mysql_aurora.2.09.5",
        "dBInstanceIdentifier": "amsql-ftore-00ppf01",
        "endpoint": {
          "hostedZoneId": "PPQRT56789",
          "address": "amsql-ftore-00ppf01.rds.amazonaws.com",
          "port": 3308
        },
        "dBName": "aamsqlftoreppf",
        "engine": "aurora-mysql",
        "dBClusterIdentifier": "aamsql-ftore-ppf0001",
        "storageEncrypted": true
      },
      "arn": "arn:aws:rds:us-east-2:00897654657:db:aamsql-ftore-ppf0001",
      "configurationItemCaptureTime": "2021-08-18T13:07:51.264Z",
      "resourceCreationTime": "2021-08-18T08:32:56.313Z",
      "tags": [
        {
          "tag": "business-division=DIG"
        },
        {
          "tag": "workspace=ftore-db-ppf"
        },
        {
          "tag": "Patch Group=Default"
        },
        {
          "tag": "db-patch-time-window=Fri9pmEST"
        },
        {
          "tag": "database-platform=A-MySql"
        },
        {
          "tag": "db-patch-schedule=Q2M2W2"
        },
        {
          "tag": "app-servicenow-group=Snow Data science support"
        },
        {
          "tag": "primaries=arn:aws:rds:us-east-1:000988765445:cluster:aamsql-ftore-ppfl01arn:aws:rds:us-east-1:005600987225:db:aamsql-ftore-00ppf01"
        },
        {
          "tag": "Patch Window=Default"
        },
        {
          "tag": "financial-internal-data=N"
        },
        {
          "tag": "company=FTORQ"
        },
        {
          "tag": "snow-support-dl=dl-featurestorecloud@ftorq.com"
        },
        {
          "tag": "costcenter=0099877655"
        },
        {
          "tag": "barometer-it=QQapm567435"
        },
        {
          "tag": "application-name=ftore"
        },
        {
          "tag": "schedule-window=alwayson"
        },
        {
          "tag": "application_tag5=NULL"
        },
        {
          "tag": "application_tag4=ftore-plat"
        },
        {
          "tag": "application_tag3=PPF"
        },
        {
          "tag": "financial-regulatory-data=N"
        },
        {
          "tag": "application_tag2=pr-aifs-airflow"
        },
        {
          "tag": "application_tag1=pr-aifs-kubeflow"
        },
        {
          "tag": "environment=PPF"
        },
        {
          "tag": "database-state=Active"
        },
        {
          "tag": "owner-department=digital"
        },
        {
          "tag": "compliance=NULL"
        },
        {
          "tag": "privacy-data=N"
        },
        {
          "tag": "resource-type=docdb"
        },
        {
          "tag": "legal-data=N"
        }
      ]
    }
]
}

*/
var dsId = 'podg9da24769c1101b92b37c346d8765';

try {
	var attachment = new GlideSysAttachment();
	var attachGr = attachment.getAttachments('sys_data_source', dsId);
	if (attachGr.hasNext()) {
		attachGr.next();
		gs.info('AWS Import: Got file ' + attachGr.getValue('file_name'));
		gs.info('AWS Import: Got file ' + attachGr.getValue('sys_id'));
		var contents = attachment.getContent(attachGr);
		var jsonContents = JSON.parse(contents);
		gs.info('AWS Import: Got JSON with ' + jsonContents.count + ' results');

		var results = jsonContents.results;
		if (results != null && results.length > 0) {

			var impSetGr = new GlideRecord('sys_import_set');
			impSetGr.initialize();
			impSetGr.setValue('mode', 'synchronous');
			impSetGr.setValue('table_name', 'x_xxp2_cld_db_imp_aws_legacy');
			impSetGr.setValue('state', 'loading');
			impSetGr.insert();


			for (var i = 0; i < results.length; i++) {
				
				gs.info('AWS Import: Importing Record ' + i);

				var importGr = new GlideRecord('x_xxp2_cld_db_imp_aws_legacy');
				importGr.setValue('sys_import_set', impSetGr.getValue('sys_id'));
				importGr.setValue('account_id', results[i].accountId);
				importGr.setValue('address', results[i].configuration.endpoint.address);
				importGr.setValue('db_cluster_identifier', results[i].configuration.dBClusterIdentifier);
				importGr.setValue('db_instance_identifier', results[i].configuration.dBInstanceIdentifier);
				importGr.setValue('db_name', results[i].configuration.dBName);
				importGr.setValue('engine', results[i].configuration.engine);
				importGr.setValue('engine_version', results[i].configuration.engineVersion);
				importGr.setValue('storage_encrypted', results[i].configuration.storageEncrypted);
				importGr.setValue('tcp_port', results[i].configuration.endpoint.port);
				

				var tags = results[i].tags;
				if(tags != null){
				for (var j = 0; j < tags.length; j++) {
					if (tags[j].tag.startsWith('Company')) {
						importGr.setValue('company', tagValue(tags[j].tag));
					}
					if (tags[j].tag.startsWith('BarometerIT')) {
						importGr.setValue('apm_number', tagValue(tags[j].tag));
					}
					if (tags[j].tag.startsWith('privacy-data')) {
						importGr.setValue('privacy_data', tagValue(tags[j].tag));
					}
					if (tags[j].tag.startsWith('financial-regulatory-data')) {
						importGr.setValue('financial_regulatory_data', tagValue(tags[j].tag));
					}
					if (tags[j].tag.startsWith('legal-data')) {
						importGr.setValue('legal_data', tagValue(tags[j].tag));
					}
					if (tags[j].tag.startsWith('financial-internal-data')) {
						importGr.setValue('financial_internal_data', tagValue(tags[j].tag));
					}
					if (tags[j].tag.startsWith('Environment')) {
						importGr.setValue('environment', tagValue(tags[j].tag));
					}			
								
									
				}
				} else {
					gs.info('AWS Import: Does not have any tags for this iteration ' + i);
				}

				importGr.insert();
			}

			impSetGr.state = 'loaded';
			impSetGr.update();


		} else {
			gs.info('AWS Import: No results in file');
		}

	} else {
		gs.info('AWS Import: No attachment found');
	}
} catch (ex) {
	gs.error('AWS Import: ' + ex.message);
}

	function tagValue(tag) {
		if (tag != null && tag.length > 0) {
			return tag.substring(tag.indexOf('=') + 1);
		} else {
			return '';
		}
	}

[Siva Kedari Vee]

Hi @Ankur Bawiskar 

Any help on the above question

[Ankur Bawiskar]

Hi,

I would suggest to debug if the script you are using is the same and also if same json file is used

Regards
Ankur

[Siva Kedari Vee]

Hi @Ankur Bawiskar 
I've captured this data load in an update set and same file is used and no code change as well. I've deployed the changes in my PDI it works there perfectly but in my enterprise environment it is failing not sure what is the root cause of it.

[Ankur Bawiskar]

Unless you use small set of json file and debug you won't know.