see
https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0870045
also the following is helpful:
(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
Post a Comment