ServiceNow import set issues, e.g. import rows stuck in 'pending'

 see 

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0870045

 

also the following is helpful:

 https://community.servicenow.com/community?id=community_article&sys_id=b3d642ed1b493050fc3233bc1d4bcb58

(ServiceNow ) 


diagnostic script to run:


var iset = "22a4122a1b9891100c1d2131b24bcbe5"; //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<1 ) { 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 < scheduledimport_names.length;i++){ var transaction = new GlideRecord("syslog_transaction"); transaction.addQuery("sys_created_on",">=",last_record_transformed_in_importset); transaction.addQuery("sys_created_on","<=",last_record_transformed_in_importset_plus); 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 < scheduledimport_names.length;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 calculateRate(recordCount, duration) { //seperate duration into days, hours, minutes, seconds var days_hms = duration.getDurationValue().split(" "); var days = ""; var hms = ""; if( days_hms.length==1 ) { hms = days_hms[0]; } else { days = days_hms[0]; hms = days_hms[1]; } //separate hms into hours, minutes, and seconds var hrs_mins_secs = hms.split(":"); var hrs = ""; var mins = ""; var secs = ""; if( hrs_mins_secs.length==3 ) { hrs = hrs_mins_secs[0]; mins = hrs_mins_secs[1]; secs = hrs_mins_secs[2]; } secs = secs * 1; //convert to int var mins_in_secs = mins*60; var hrs_in_secs = hrs*60*60; var days_in_secs = days*24*60*60; var second_count = days_in_secs+hrs_in_secs+mins_in_secs+secs; return (recordCount/second_count).toFixed(2) + " recs/sec"; } 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; }

Comments

Popular posts from this blog

GlideRecord setValue

URL link in addInfoMessage