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 

I've tried that as well and I put some debug logs as well, the script inserts a record 9 times in to the staging table. But I couldn't see this behavior in PDI.

[Ankur Bawiskar]

Not very sure then.

Please raise HI ticket with ServiceNow.

[Ankur Bawiskar]

@Siva Kedari Veerla 

Any update to this?

Regards
Ankur