Import Transform Summary Tool - Support and Troubleshooting

Import Transform Summary Tool Details

This is a tool specifically for troubleshooting import and/or transform issues. Many platform components are involved when importing and transforming data into an instance. It is critical to have a bird's-eye view of all these components when troubleshooting import/transform issues. The Import Transform Summary Tool is a script that runs in scripts–background and it gathers all of the components and data for a particular import set. This is a manual task that TSEs perform when working import/transform issues. This manual task takes minutes to perform. This tool will perform the same task in a few seconds.

The script output is made up of three sections: Definition, Runtime, and Transaction. The definition section shows data regarding the import configuration. The runtime data is information that is usually found in the import set records or data created when the import and transform actually run. Finally, the transaction part of the output gives information regarding the transaction that ran the import/transform job.

The following sections present a sample of the output along with detailed descriptions. Let's start with the Definition section:

[DEFINITION]: [*] Scheduled Import: Test Concurrent Import (scheduled_import_set.do?sys_id=07015df4db10a41075cfe1191396192d) ... Active=true ... Run as: admin ... Run type: daily ... Run time: 12:00:00 ..[*] Data Source: 129k_users (sys_data_source.do?sys_id=97309db4db10a41075cfe1191396199e) ..... Import Set table: u_129k_users ..... Type: File ..... File Retrieval: Attachment ....[*] Transform Map: simple user transform map, Order=100 (sys_transform_map.do?sys_id=5f311138db10a41075cfe1191396192e) ....... Active: true ....... Import Set table: u_129k_users ....... Target table: sys_user ....... .. WARNING: This transform is setup to run business rules on target. ....... .. Number of non-async business rules to run on target: 28 ....... .. May cause slow transforms on large data sets. ....... Run script (Explicit Transform Map Script): false ....... Field Maps: ....... .. - u_last_name > last_name ....... .. - [Script] > name ....... .. - u_email > email ....... .. - u_name > first_name ....... .. - u_user_name > user_name [COALESCE] ....... Transform Event Scripts: ....... .. - onBefore, Order=100, Active=true (WARNING: runs on every row, may cause slow transform on large data sets) ....... .. - onAfter, Order=100, Active=true (WARNING: runs on every row, may cause slow transform on large data sets) ....... .. - onStart, Order=100, Active=true ....[*] Transform Map: test location transform map, Order=100 (sys_transform_map.do?sys_id=6efeabe9dbd0681075cfe11913961906) ....... Active: true ....... Import Set table: u_129k_users ....... Target table: cmn_location ....... .. WARNING: This transform is setup to run business rules on target. ....... .. Number of non-async business rules to run on target: 2 ....... .. May cause slow transforms on large data sets. ....... Run script (Explicit Transform Map Script): false ....... Field Maps: ....... .. - u_user_name > city ....... .. - u_email > name [COALESCE]

The first block of the Definition section gives out information regarding the scheduled import if this import is executed via a scheduled job. Notice that links will be provided for any instance record – this is the information you'll find in parenthesis following the record's name. Other scheduled job information such as the frequency the job runs and the user that the job runs as can also be found here.

The next two blocks of data contain information regarding the Data Source and Transform. Again, links to each of these records are outputted as part of the script for convenience. In order to keep the output clean, none of the scripts will be part of this output. If you need to see the script, then it is recommended you open the record.

In the transform map section, you will get warnings if the transform map is configured in a way that may cause performance issues (especially on large data sets):

If the transform map is setup to run business rules on target table and the target table has at least one non-async business rule If the transform map contains onBefore or onAfter scripts. These scripts run on every row.

The second section in this tool's output is the Runtime information. Again, this is the data that actually gets imported and transformed.

[RUNTIME]: [*] Import Set: ISET0010270 (sys_import_set.do?sys_id=28747ea8db0df45075cfe1191396193e) ... State: processed - import and transform are completed. ... Import Started: 2021-06-26 12:00:02 ... Import Completed: 2021-06-26 12:05:45 ... Import Run Time: 5 Minutes ..[*] Transform: simple user transform map (sys_transform_map.do?sys_id=5f311138db10a41075cfe1191396192e) .... Import Set Run (Created): 2021-06-26 12:05:45 .... Import Set Run (Completed): 2021-06-26 12:24:26 .... Import Set Run (Run time): 18 Minutes .... Import Set Run (State): Complete .... Import Set Run (Total): 129380 .... Import Set Run (Inserts): 0 .... Import Set Run (Updates): 0 .... Import Set Run (Ignored): 129378 .... Import Set Run (Skipped): 2 .... Import Set Run (Error): 0 .... Import Set Row Information: .... .... Total of Records in Import Set Row table: 258760 .... .... Total of Records in Import Set Row table for this transform map: 129380 .... .... Total of Records in Import Set Row table without Transform Map (yet to be transformed): 0 .... .... First record imported: 2021-06-26 12:00:02 .... .... Last record imported: 2021-06-26 12:30:58 .... .... Import Duration: 30 Minutes .... .... Import Rate: 69.71 recs/sec .... .... First record transformed: 2021-06-26 12:05:46 .... .... Last record transformed: 2021-06-26 12:24:26 (Row:129321) .... .... Transform Duration: 18 Minutes .... .... Transform Rate: 115.52 recs/sec ..[*] Transform: test location transform map (sys_transform_map.do?sys_id=6efeabe9dbd0681075cfe11913961906) .... Import Set Run (Created): 2021-06-26 12:05:45 .... Import Set Run (Completed): 2021-06-26 12:40:46 .... Import Set Run (Run time): 35 Minutes .... Import Set Run (State): Complete .... Import Set Run (Total): 129380 .... Import Set Run (Inserts): 0 .... Import Set Run (Updates): 0 .... Import Set Run (Ignored): 129371 .... Import Set Run (Skipped): 9 .... Import Set Run (Error): 0 .... Import Set Row Information: .... .... Total of Records in Import Set Row table: 258760 .... .... Total of Records in Import Set Row table for this transform map: 129380 .... .... Total of Records in Import Set Row table without Transform Map (yet to be transformed): 0 .... .... First record imported: 2021-06-26 12:24:29 .... .... Last record imported: 2021-06-26 12:30:58 .... .... Import Duration: 6 Minutes .... .... Import Rate: 332.60 recs/sec .... .... First record transformed: 2021-06-26 12:30:59 .... .... Last record transformed: 2021-06-26 12:40:46 (Row:129306) .... .... Transform Duration: 9 Minutes .... .... Transform Rate: 220.41 recs/sec ..[*] Total of Records in Import Set Row table w/out Transform Map (records yet to be transformed): 0

This information tells us the state of the import and transform. For example, is the import still running? Is the import complete and the transform is still running? Is the transform complete? It also gives us date and time when the import and transform started and ended, the number of records imported, and the rate in which records are being imported and transformed. This information is especially useful for import/transform performance issues. Within seconds, you can find out not only if there is an import/transform performance issue, but whether the import, transform, or both are performing slow. Keep in mind that there is no official document that specifies what constitutes a fast, acceptable, or slow rate of import/transform. It is difficult to provide such a document because every import and instance is different. What I can tell you is that import/transform rates in the hundreds per second is acceptable and import/transform rates in the single digits per second is slow. This section will also let you know if the transform process never completed.

The last section in this tool's output is the Transaction.

[TRANSACTION]: [*] Transaction: syslog_transaction.do?sys_id=94747ea8db0df45075cfe1191396193c ... Active: false ... Transaction ID: 94747ea8db0d ... Start time: 2021-06-26 12:00:02 ... System ID (Node): app137012.ycg3.service-now.com:empcsandoval011 ... Session (Worker): glide.scheduler.worker.4 ... Response time (includes Import & Transform): 0 days 0 hrs 40 mins 44 secs ... Splunk Query: instance=empcsandoval sourcetype=appnode_localhost_log txid=94747ea8db0d | sort _time

The transaction information is important to dig deeper into the localhost logs. Instead of spending minutes looking for the transaction, this script simply makes it part of the output when the information is available. For convenience, the output includes the time the transaction started, the node and worker that handled the job, and the response time. Finally, a handy Splunk query is also supplied in case support wants to search for this transaction in the localhost logs.

To use this script simply

1. copy and paste this script into the Scripts-Background area 2. replace the value of "iset" with the desired Import Set (ISET) number

Script:

var iset = ""; //ISET number OR sys_import_set.sys_id

var isetfound = true; var gr_iset = new GlideRecord("sys_import_set"); gr_iset.addQuery("number",iset); gr_iset.query(); if(!gr_iset.next()){ gr_iset.initialize(); gr_iset.addQuery("sys_id",iset); gr_iset.query(); if(!gr_iset.next()){ gs.log("Import Set (ISET) not found."); isetfound = false; } }

var resultString = "\n\n\nOutput from Import Transform Summary Tool : https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0870045 "; if( isetfound ) resultString = resultString+"\n\n[DEFINITION]:\n";

//if we are here, we have a valid GlideRecord for Import Set. var gr_datasrc_found = false; var gr_datasrc = new GlideRecord("sys_data_source"); gr_datasrc_found = gr_datasrc.get(gr_iset.data_source);

//print scheduled job data, if this Data Source part of any scheduled imports var scheduledimport_names = []; var gr_scheduledimport = new GlideRecord("scheduled_import_set"); gr_scheduledimport.addQuery("data_source", gr_iset.data_source); gr_scheduledimport.query(); while(gr_scheduledimport.next()) { scheduledimport_names.push(gr_scheduledimport.name); resultString=resultString+"[*] Scheduled Import: " + gr_scheduledimport.name + " (" + gr_scheduledimport.getLink(true) + ")\n"; resultString=resultString+"... Active=" + gr_scheduledimport.active + "\n"; var gr_runas = new GlideRecord("sys_user"); gr_runas.get(gr_scheduledimport.run_as); resultString=resultString+"... Run as: " + gr_runas.user_name +"\n"; resultString=resultString+"... Run type: " + gr_scheduledimport.run_type+"\n"; if("daily"==gr_scheduledimport.run_type){ resultString=resultString+"... Run time: " + gr_scheduledimport.run_time.getDisplayValue()+"\n"; } else if("weekly"==gr_scheduledimport.run_type){ resultString=resultString+"... Run day: " + gr_scheduledimport.run_dayofweek.getDisplayValue()+"\n"; resultString=resultString+"... Run time: " + gr_scheduledimport.run_time.getDisplayValue()+"\n"; } else if("monthly"==gr_scheduledimport.run_type){ resultString=resultString+"... Run day: " + gr_scheduledimport.run_dayofmonth.getDisplayValue()+"\n"; resultString=resultString+"... Run time: " + gr_scheduledimport.run_time.getDisplayValue()+"\n"; } else if("periodically"==gr_scheduledimport.run_type){ resultString=resultString+"... Run period: " + gr_scheduledimport.run_period.getDisplayValue()+"\n"; resultString=resultString+"... Run start: " + gr_scheduledimport.run_start.getDisplayValue()+"\n"; } else if("parent"==gr_scheduledimport.run_type){ resultString=resultString+"... Run time: " + gr_scheduledimport.run_time.getDisplayValue()+"\n"; resultString=resultString+"... Run after parent runs: " + gr_scheduledimport.parent.getDisplayValue()+"\n"; } } if( scheduledimport_names.length resultString=resultString+"[*] Scheduled Import: none\n"; }

//print Data Source info: if( isetfound && gr_datasrc_found ) { resultString=resultString+"..[*] Data Source: " + gr_datasrc.name + " (" + gr_datasrc.getLink(true) + ")\n"; resultString=resultString+"..... Import Set table: " + gr_datasrc.import_set_table_name + "\n"; resultString=resultString+"..... Type: " + gr_datasrc.type + "\n";

if("File"==gr_datasrc.type){ resultString=resultString+"..... File Retrieval: " + gr_datasrc.file_retrieval_method.getDisplayValue() + "\n"; } else if("JDBC"==gr_datasrc.type){ var gr_mid = new GlideRecord("ecc_agent"); gr_mid.addQuery("sys_id",gr_datasrc.mid_server); gr_mid.query(); if( gr_mid.next() ) { resultString=resultString+"..... MID Server: " + gr_mid.name + " (" + gr_mid.getLink(true) + ")\n"; } } else if("LDAP"==gr_datasrc.type){ var gr_ldap_mid = new GlideRecord("ecc_agent"); gr_ldap_mid.addQuery("sys_id",gr_datasrc.mid_server); gr_ldap_mid.query(); if( gr_ldap_mid.next() ) { resultString=resultString+"..... MID Server: " + gr_ldap_mid.name + " (" + gr_ldap_mid.getLink(true) + ")\n"; } var gr_ldapou = new GlideRecord("ldap_ou_config"); gr_ldapou.addQuery("sys_id",gr_datasrc.ldap_target); gr_ldapou.query(); if( gr_ldapou.next() ){ resultString=resultString+"..... LDAP OU: " + gr_ldapou.name + " (" + gr_ldapou.getLink(true) + "\n"; var gr_ldapserver = new GlideRecord("ldap_server_config"); gr_ldapserver.addQuery("sys_id",gr_ldapou.server); gr_ldapserver.query(); if( gr_ldapserver.next() ) { resultString=resultString+"..... LDAP Server: " + gr_ldapserver.name + " (" + gr_ldapserver.getLink(true) + "\n"; } } } } // iset and data source found if( !gr_datasrc_found ) { resultString=resultString+"..[*] Data Source: not found (this is OK for web service import sets)\n"; }

//print Transform Map info: var gr_transmaps = new GlideRecord("sys_transform_map"); //gr_transmaps.addQuery("source_table",gr_datasrc.import_set_table_name); gr_transmaps.addQuery("source_table", gr_iset.table_name); gr_transmaps.orderBy("order"); gr_transmaps.query(); while(gr_transmaps.next()){ //check if there are any before/after business rules on target table. These may cause performance issues. var run_business_rules_on_target = false; var business_rule_count_on_target = 0; if( gr_transmaps.run_business_rules ) { run_business_rules_on_target = true; var target_brs = new GlideAggregate("sys_script"); target_brs.addQuery("active", true); target_brs.addQuery("collection", gr_transmaps.target_table); target_brs.addQuery("when","before").addOrCondition("when","after"); target_brs.addAggregate("COUNT"); target_brs.query(); if( target_brs.next() ) { business_rule_count_on_target = target_brs.getAggregate("COUNT"); } } resultString=resultString+"....[*] Transform Map: " + gr_transmaps.name + ", Order=" + gr_transmaps.order + " (" + gr_transmaps.getLink(true) + ")\n"; resultString=resultString + "....... Active: " + gr_transmaps.active + "\n"; resultString=resultString + "....... Import Set table: " + gr_transmaps.source_table + "\n"; resultString=resultString + "....... Target table: " + gr_transmaps.target_table + "\n"; if( run_business_rules_on_target && (business_rule_count_on_target>0)) { resultString=resultString + "....... .. WARNING: This transform is setup to run business rules on target.\n"; resultString=resultString + "....... .. Number of non-async business rules to run on target: " + business_rule_count_on_target + "\n"; resultString=resultString + "....... .. May cause slow transforms on large data sets.\n"; } resultString=resultString + "....... Run script (Explicit Transform Map Script): " + gr_transmaps.run_script + "\n";

//Field Map records var gr_fieldmaps = new GlideRecord("sys_transform_entry"); gr_fieldmaps.addQuery("map", gr_transmaps.sys_id); gr_fieldmaps.query(); if( gr_fieldmaps.getRowCount()>0 ) { resultString=resultString + "....... Field Maps:\n"; while(gr_fieldmaps.next()){ resultString=resultString + "....... .. - " + gr_fieldmaps.source_field + " > " + gr_fieldmaps.target_field; if( gr_fieldmaps.coalesce ) { resultString=resultString + " [COALESCE]"; } resultString=resultString + "\n"; } } //Field Map records

//Transformaion Event Scripts var gr_transfeventscripts = new GlideRecord("sys_transform_script"); gr_transfeventscripts.addQuery("map", gr_transmaps.sys_id); gr_transfeventscripts.orderBy("order"); gr_transfeventscripts.query(); if( gr_transfeventscripts.getRowCount()>0 ) { resultString=resultString + "....... Transform Event Scripts:\n"; while( gr_transfeventscripts.next() ){ resultString=resultString + "....... .. - " + gr_transfeventscripts.when + ", Order=" + gr_transfeventscripts.order + ", Active=" + gr_transfeventscripts.active; if( gr_transfeventscripts.active && ("onBefore"==gr_transfeventscripts.when || "onAfter"==gr_transfeventscripts.when ) ) { resultString=resultString + " (WARNING: runs on every row, may cause slow transform on large data sets)"; } resultString=resultString+"\n"; } } // end Transformation Event Scripts

} // end Transform Maps

if( isetfound ) resultString=resultString + "\n\n\n[RUNTIME]:\n";

//Import Set Info: if( isetfound ) { resultString=resultString + "[*] Import Set: " + gr_iset.number + " (" + gr_iset.getLink(true) + ")\n"; resultString=resultString + "... State: " + gr_iset.state; //add a helpful description to each state if( "loading"==gr_iset.state ) { resultString=resultString + " - import is currently taking place."; } else if( "loaded"==gr_iset.state) { resultString=resultString + " - import is complete, transform is not complete."; } else if( "processed"==gr_iset.state) { resultString=resultString + " - import and transform are completed."; } else if( "cancelled"==gr_iset.state ) { resultString=resultString + " - import and/or transform cancelled."; } resultString=resultString + "\n"; resultString=resultString + "... Import Started: " + gr_iset.sys_created_on.getDisplayValue() + "\n"; resultString=resultString + "... Import Completed: " + gr_iset.load_completed.getDisplayValue() + "\n"; resultString=resultString + "... Import Run Time: " + gr_iset.load_run_time.getDisplayValue() + "\n"; }

//get import set run information var at_least_one_iset_run = false; var last_record_transformed_in_importset; //first, get total count (independent of transform map) var total_iset_row_count = 0; var gr_impsetrow_all = new GlideAggregate("sys_import_set_row"); gr_impsetrow_all.addQuery("sys_import_set", gr_iset.sys_id); gr_impsetrow_all.addAggregate("COUNT"); gr_impsetrow_all.query(); if( gr_impsetrow_all.next() ) { total_iset_row_count = gr_impsetrow_all.getAggregate("COUNT"); }

//get total of import set row with (empty) transform map value var iset_row_wout_map_count = 0; var gr_impsetrow_empty_map = new GlideAggregate("sys_import_set_row"); gr_impsetrow_empty_map.addQuery("sys_import_set", gr_iset.sys_id); gr_impsetrow_empty_map.addNullQuery("sys_transform_map"); gr_impsetrow_empty_map.addAggregate("COUNT"); gr_impsetrow_empty_map.query(); if( gr_impsetrow_empty_map.next() ){ iset_row_wout_map_count = gr_impsetrow_empty_map.getAggregate("COUNT"); }

//next, get counts per transform map var gr_transmaps2 = new GlideRecord("sys_transform_map"); gr_transmaps2.addQuery("source_table", gr_iset.table_name); gr_transmaps2.orderBy("order"); gr_transmaps2.query(); while(gr_transmaps2.next()){ var gr_impsetrun = new GlideRecord("sys_import_set_run"); gr_impsetrun.addQuery("set", gr_iset.sys_id); gr_impsetrun.addQuery("sys_transform_map", gr_transmaps2.sys_id); gr_impsetrun.orderByDesc("sys_created_on"); gr_impsetrun.query(); if( gr_impsetrun.next() ) { at_least_one_iset_run = true; resultString=resultString+"..[*] Transform: " + gr_transmaps2.name + " (" + gr_transmaps2.getLink(true) + ")\n"; resultString=resultString+".... Import Set Run (Created): " + gr_impsetrun.sys_created_on.getDisplayValue() + "\n"; resultString=resultString+".... Import Set Run (Completed): " + gr_impsetrun.completed.getDisplayValue() + "\n"; resultString=resultString+".... Import Set Run (Run time): " + gr_impsetrun.run_time.getDisplayValue() + "\n"; resultString=resultString+".... Import Set Run (State): " + gr_impsetrun.state.getDisplayValue() + "\n"; resultString=resultString+".... Import Set Run (Total): " + gr_impsetrun.total + "\n"; resultString=resultString+".... Import Set Run (Inserts): " + gr_impsetrun.inserts + "\n"; resultString=resultString+".... Import Set Run (Updates): " + gr_impsetrun.updates + "\n"; resultString=resultString+".... Import Set Run (Ignored): " + gr_impsetrun.ignored + "\n"; resultString=resultString+".... Import Set Run (Skipped): " + gr_impsetrun.skipped + "\n"; resultString=resultString+".... Import Set Run (Error): " + gr_impsetrun.errors + "\n"; //check if there is more than one import_set_run if( gr_impsetrun.next() ) { resultString=resultString+".... NOTE: more than one Import Set Run found, the above values are for the latest run\n"; } resultString=resultString+".... Import Set Row Information:\n";

//get import_set_row (staging table) information if( gs.getProperty("com.glide.importset.multiple_transform.new_rows", true) ) { var gr_impsetrow = new GlideAggregate("sys_import_set_row"); gr_impsetrow.addQuery("sys_import_set", gr_iset.sys_id); gr_impsetrow.addQuery("sys_transform_map", gr_transmaps2.sys_id); gr_impsetrow.addAggregate("COUNT"); gr_impsetrow.query(); if( gr_impsetrow.next() ) { resultString=resultString+".... .... Total of Records in Import Set Row table: " + total_iset_row_count + "\n"; resultString=resultString+".... .... Total of Records in Import Set Row table for this transform map: " + gr_impsetrow.getAggregate("COUNT") + "\n"; resultString=resultString+".... .... Total of Records in Import Set Row table without Transform Map (yet to be transformed): " + iset_row_wout_map_count + "\n"; //get "crated on" and "updated on" for first rec and last: var gr_impsetrow_first_imported = new GlideRecord("sys_import_set_row"); gr_impsetrow_first_imported.addQuery("sys_import_set", gr_iset.sys_id); gr_impsetrow_first_imported.addQuery("sys_transform_map", gr_transmaps2.sys_id); gr_impsetrow_first_imported.orderBy("sys_created_on"); gr_impsetrow_first_imported.setLimit(1); gr_impsetrow_first_imported.query(); if(gr_impsetrow_first_imported.next()){ resultString=resultString+".... .... First record imported: " + gr_impsetrow_first_imported.sys_created_on.getDisplayValue() + "\n"; } var gr_impsetrow_last_imported = new GlideRecord("sys_import_set_row"); gr_impsetrow_last_imported.addQuery("sys_import_set", gr_iset.sys_id); gr_impsetrow_last_imported.orderByDesc("sys_created_on"); gr_impsetrow_last_imported.setLimit(1); gr_impsetrow_last_imported.query(); if(gr_impsetrow_last_imported.next()){ resultString=resultString+".... .... Last record imported: " + gr_impsetrow_last_imported.sys_created_on.getDisplayValue() + "\n"; } //calculate import rate: var import_start_datetime = new GlideDateTime(gr_impsetrow_first_imported.sys_created_on); var import_end_datetime = new GlideDateTime(gr_impsetrow_last_imported.sys_created_on); var import_duration = GlideDateTime.subtract(import_start_datetime, import_end_datetime); resultString=resultString+".... .... Import Duration: " + import_duration.getDisplayValue() + "\n"; resultString=resultString+".... .... Import Rate: " + calculateRate(gr_impsetrow.getAggregate("COUNT"), import_duration) + "\n";

var gr_impsetrow_first_transf = new GlideRecord("sys_import_set_row"); gr_impsetrow_first_transf.addQuery("sys_import_set", gr_iset.sys_id); gr_impsetrow_first_transf.addQuery("sys_transform_map", gr_transmaps2.sys_id); gr_impsetrow_first_transf.orderBy("sys_updated_on"); gr_impsetrow_first_transf.setLimit(1); gr_impsetrow_first_transf.query(); if(gr_impsetrow_first_transf.next()){ resultString=resultString+".... .... First record transformed: " + gr_impsetrow_first_transf.sys_updated_on.getDisplayValue() + "\n"; } var gr_impsetrow_last_transf = new GlideRecord("sys_import_set_row"); gr_impsetrow_last_transf.addQuery("sys_import_set", gr_iset.sys_id); gr_impsetrow_last_transf.addQuery("sys_transform_map", gr_transmaps2.sys_id); gr_impsetrow_last_transf.orderByDesc("sys_updated_on"); gr_impsetrow_last_transf.setLimit(1); gr_impsetrow_last_transf.query(); if(gr_impsetrow_last_transf.next()){ resultString=resultString+".... .... Last record transformed: " + gr_impsetrow_last_transf.sys_updated_on.getDisplayValue() + " (Row:" + gr_impsetrow_last_transf.sys_import_row + ")\n"; } last_record_transformed_in_importset = gr_impsetrow_last_transf.sys_updated_on; //calculate transform rate var transf_start_datetime = new GlideDateTime(gr_impsetrow_first_transf.sys_updated_on); var transf_end_datetime = new GlideDateTime(gr_impsetrow_last_transf.sys_updated_on); var transform_duration = GlideDateTime.subtract(transf_start_datetime, transf_end_datetime); resultString=resultString+".... .... Transform Duration: " + transform_duration.getDisplayValue() + "\n"; resultString=resultString+".... .... Transform Rate: " + calculateRate(gr_impsetrow.getAggregate("COUNT"), transform_duration) + "\n";

//if transform is not complete and its been a while with no activity, then there may have been an error that stopped the transform: var now = new GlideDateTime(); if( gr_impsetrow.getAggregate("COUNT")>0 && iset_row_wout_map_count>0 ) { var now_minus_1hr = new GlideDateTime(); now_minus_1hr.add(-3600000); if( now_minus_1hr.onOrAfter( transf_end_datetime ) ) { resultString=resultString+".... .... WARNING: There are still records to be transformed for this transform map and\n"; resultString=resultString+".... .... .... ....its been more than 1hr since last record was transformed. Check logs\n"; resultString=resultString+".... .... .... ....around time the last record was transformed for any errors that may have\n"; resultString=resultString+".... .... .... ....caused transform to stop.\n"; } }

} // END Import Set Row } else { resultString=resultString+".... .... NOTE: com.glide.importset.multiple_transform.new_rows property is set to false, set it to true " + "to faciliate troubleshooting\n"; } } } // END iterating through transform maps

//any import_set_row records with empty transform map? if( at_least_one_iset_run ) { var gr_impsetrow_nomap = new GlideAggregate("sys_import_set_row"); gr_impsetrow_nomap.addQuery("sys_import_set", gr_iset.sys_id); gr_impsetrow_nomap.addNullQuery("sys_transform_map"); gr_impsetrow_nomap.addAggregate("COUNT"); gr_impsetrow_nomap.query(); if( gr_impsetrow_nomap.next() ) { resultString=resultString+"..[*] Total of Records in Import Set Row table w/out Transform Map (records yet to be transformed): " + gr_impsetrow_nomap.getAggregate("COUNT") + "\n"; } } else { resultString=resultString+"..[*] No Import Set Runs found. Make sure the data source contains data.\n"; }

//Get Transaction, Session, Node information if( isetfound && last_record_transformed_in_importset ) { //this is a bit sloppy because there is no relationship between import/transform and transaction logs. //this logic should work most of the time: // any transaction record within 5 seconds of the last transformed record AND // the same name as the scheduled import var addSeconds = new GlideTime(); addSeconds.setValue("00:00:05"); // 5 seconds var last_record_transformed_in_importset_plus = new GlideDateTime(last_record_transformed_in_importset); last_record_transformed_in_importset_plus.add(addSeconds.getNumericValue());

//note: there may be more than one scheduled jobs for the same data source resultString=resultString + "\n\n\n[TRANSACTION]:\n"; var transactionfound = false; for(var i = 0;i var transaction = new GlideRecord("syslog_transaction"); transaction.addQuery("sys_created_on",">=",last_record_transformed_in_importset); transaction.addQuery("sys_created_on"," transaction.addQuery("url","JOB: "+scheduledimport_names[i]); transaction.query(); if( transaction.next() ) { resultString=resultString+"[*] Transaction: " + transaction.getLink(true) + "\n"; resultString=resultString + "... Active: false\n"; resultString=resultString + "... Transaction ID: " + transaction.sys_id.substring(0,12) + "\n"; resultString=resultString + "... Start time: " + transaction.start_process_at.getDisplayValue() + "\n"; resultString=resultString + "... System ID (Node): " + transaction.system_id.getDisplayValue() + "\n"; resultString=resultString + "... Session (Worker): " + transaction.session.getDisplayValue() + "\n"; resultString=resultString + "... Response time (includes Import & Transform): " + msToDaysHrsMinsSecs(transaction.response_time) + "\n";

//Splunk Query: var splunkquery = "instance=" + gs.getProperty("instance_name") + " sourcetype=appnode_localhost_log "; splunkquery = splunkquery+ "txid=" + transaction.sys_id.substring(0,12) + " | sort _time"; resultString=resultString + "... Splunk Query: " + splunkquery+"\n";

transactionfound = true; break; } } if( transactionfound==false ) { //transaction not found in transaction log, this means that the transaction may still be active and running. var activetrans = new GlideRecord("v_cluster_transaction"); activetrans.query(); while(activetrans.next()){ for(var j = 0;j if(activetrans.url==scheduledimport_names[j]){ resultString=resultString+"[*] Transaction: " + activetrans.getLink(true) + "\n"; resultString=resultString + "... Active: true\n"; resultString=resultString + "... URL: " + activetrans.url + "\n"; resultString=resultString + "... Start time: " + activetrans.start.getDisplayValue() + "\n"; resultString=resultString + "... System ID (Node): " + activetrans.node_id + "\n"; resultString=resultString + "... Session (Worker): " + activetrans.session_id + "\n"; resultString=resultString + "... Age (includes Import & Transform): " + activetrans.age.getDisplayValue() + "\n"; transactionfound = true; break; } } } } if( transactionfound==false ) { //if we are here, then no transaction was found in transaction logs or active transactions resultString=resultString+"[*] Transaction: none found. If it was not found, the import/transform was either launched manually or is a web service import set.\n"; } }

gs.debug(resultString);

function msToDaysHrsMinsSecs(valueInMs) { var returnval = ""; var seconds = valueInMs / 1000 >> 0; var minutes = seconds / 60 >> 0; var hours = minutes / 60 >> 0; var days = hours / 24 >> 0; returnval = returnval + days + " days "; hours = hours % 24; returnval = returnval + hours + " hrs "; minutes = minutes % 60; returnval = returnval + minutes + " mins "; seconds = seconds % 60; returnval = returnval + seconds + " secs "; return returnval; }

Note that this is tool is for troubleshooting purposes and it is not a part of the ServiceNow platform or any ServiceNow-supported applications. Therefore, ServiceNow support will not be able to assist on any questions or help regarding this tool.

Cesar Sandoval

Technical Support Architect