Name
global.SMMLTableUtils
Description
Table Utility Functions For Service Mapping ML Use Cases.
Script
var SMMLTableUtils = Class.create();
SMMLTableUtils.prototype = {
initialize: function() {
this.saHashTable = "sa_hash";
},
getFirstCreatedInTable: function(table, property, properties) {
var ga = new GlideAggregate(table);
if (!JSUtil.nil(properties)) {
try {
var propertiesJSON = JSON.parse(properties);
for (var key in propertiesJSON)
ga.addQuery(key, propertiesJSON[key]);
} catch (e) {
gs.warn("Error Parsing Properties " + properties);
}
}
ga.setGroup(false);
ga.addAggregate("MIN", property);
ga.query();
if (ga.next())
return ga.getAggregate("MIN", property);
return null;
},
getSaHashGR: function(name) {
var saHash = new GlideRecord(this.saHashTable);
saHash.addQuery("name", name);
saHash.query();
if (saHash.next()) {
return saHash;
} else {
return null;
}
},
deleteSaHash: function(name) {
var saHash = new GlideRecord(this.saHashTable);
saHash.addQuery("name", name);
saHash.query();
if (saHash.next()) {
saHash.deleteRecord();
}
},
updateSaHash: function(key, value) {
if (value) {
var saHash = new GlideRecord(this.saHashTable);
saHash.addQuery("name", key);
saHash.query();
if (!saHash.next()) {
var saHashGR = new GlideRecord(this.saHashTable);
saHashGR.initialize();
saHashGR.setValue("name", key);
saHashGR.setValue("hash", value);
saHashGR.insert();
} else {
saHash.setValue("hash", value);
saHash.update();
}
}
},
getTableFilter: function(table, field, fromDate, toDate, firstTime, priorFilter, useAggregate) {
var filter;
var dateFilter = field + ">" + fromDate;
if (firstTime)
dateFilter = field + ">=" + fromDate;
if (toDate) {
dateFilter = dateFilter + "^" + field + "<=" + toDate;
}
if (priorFilter)
filter = priorFilter + dateFilter;
else
filter = dateFilter;
try {
var ciCount;
if (useAggregate)
ciCount = this.getRowCount(table, filter);
else
ciCount = this.getCICountForFilter(table, filter);
gs.debug("SMMLTableUtils: " + ciCount + " rows will be updated to solution using filter " + filter + " for table " + table);
} catch (e) {
gs.error("SMMLTableUtils: Failed to count CIs with filter " + filter + " for table " + table);
}
return filter;
},
getRowCount: function(table, filter) {
var gr = new GlideAggregate(table);
gr.addAggregate('COUNT');
if (filter) {
gr.addQuery(filter);
}
gr.query();
if (gr.next()) {
return gr.getAggregate('COUNT');
} else {
return 0;
}
},
getCICountForFilter: function(table, filter) {
var gr = new GlideRecord(table);
if (filter)
gr.addQuery(filter);
gr.query();
return gr.getRowCount();
},
getToDate: function(table, field, fromDate, maxProcesses, priorFilter, useAggregate) {
var filter;
if (priorFilter)
filter = priorFilter + field + ">" + fromDate;
else
filter = field + ">" + fromDate;
var gr = new GlideRecord(table);
gr.addQuery(filter);
gr.query();
var rowsLeftInTable;
if (useAggregate)
rowsLeftInTable = this.getRowCount(table, filter);
else
rowsLeftInTable = this.getCICountForFilter(table, filter);
if (rowsLeftInTable == 0) {
return null;
}
var maxWindowSize = maxProcesses;
if (rowsLeftInTable < maxProcesses) {
maxWindowSize = rowsLeftInTable;
}
// if the number of rows that left is bigger then max, use max size, otherwise use the number of rows left in the table
var gr = new GlideRecord(table);
gr.addQuery(filter);
gr.orderBy(field);
// add start date
gr.chooseWindow(maxWindowSize - 1, maxWindowSize); // the from number can be less than 100k if remaining records is < 100k
gr.query();
var date = null;
if (gr.next()) {
date = gr.getValue(field);
}
gs.debug("SMMLTableUtils: Calculated date 'until' limit to be " + date);
return date;
},
isUpdateComplete: function(solutionVersion, trainingCompleteKey) {
// if training is not done yet, make sure that training state is in "solution_complete" otherwise, update status should be "Update Complete"
var isTraining_complete = this.getSaHashGR(trainingCompleteKey);
if (isTraining_complete) {
// we are in update flow
gs.debug("SMMLTableUtils: found " + trainingCompleteKey + " we are in update flow");
var updateStatus = solutionVersion.getUpdateStatus();
if (updateStatus == "Update Complete") {
return true;
}
} else {
// we are in training flow
gs.debug("SMMLTableUtils: did not found " + trainingCompleteKey + " we are in initial training flow");
var trainingStatusJson = JSON.parse(solutionVersion.getStatus());
var state = trainingStatusJson['state'];
if (state == "solution_complete") {
return true;
}
}
return false;
},
updateFromDateOnSuccess: function(preLastRunKey, lastRunKey) {
var preForDate = this.getSaHashGR(preLastRunKey);
if (preForDate) {
gs.debug("SMMLTableUtils: update from on last success key:" + lastRunKey + " to " + preForDate.getValue("hash"));
var fromDate = preForDate.getValue("hash");
this.updateSaHash(lastRunKey, fromDate);
gs.debug("SMMLTableUtils: " + lastRunKey + " was updated to " + fromDate);
}
},
getSolutionName: function(solutionNameKey) {
var solutionNameGr = this.getSaHashGR(solutionNameKey);
if (solutionNameGr) {
var solutionName = solutionNameGr.getValue("hash");
gs.debug("SMMLTableUtils: Found solution name saved in sa_hash " + solutionName);
return solutionName;
} else {
return undefined;
}
},
getActiveSolution: function(solutionName, solutionStore) {
if (solutionName) {
var mlSolution;
try {
mlSolution = solutionStore.get(solutionName);
} catch (e) {
gs.debug("Could not find solution " + solutionName);
}
if (mlSolution) {
var solutionVersion;
try {
solutionVersion = mlSolution.getActiveVersion();
} catch (e) {
gs.error("SMMLTableUtils: Failed to get active version for solution " + solutionName + ", " + e);
}
return solutionVersion;
}
}
return null;
},
isSolutionCreated: function(solutionNameKey, solutionSuffix) {
var solutionNameGr = this.getSaHashGR(solutionNameKey);
if (solutionNameGr) {
var solutionName = solutionNameGr.getValue("hash");
gs.debug("SMMLTableUtils: Found solution name saved in sa_hash " + solutionName);
return true;
} else {
gs.debug("SMMLTableUtils: could not find solution name saved in sa_hash, returning " + solutionSuffix);
return false;
}
},
trainSolution: function(solutionName, solutionStore) {
gs.debug("SMMLTableUtils: starting to train solution " + solutionName);
var mySolution = solutionStore.get(solutionName);
if (mySolution) {
var solutionVersion = mySolution.submitTrainingJob();
var trainingStatus = JSON.parse(solutionVersion.getStatus());
gs.debug("SMMLTableUtils: success on training solution " + solutionName + ", state " + trainingStatus['state']);
} else {
gs.error("SMMLTableUtils: Failed to train solution " + solutionName + ", solution was not found!!!");
}
},
getSolutionProperty: function(activeSolution, solutionName, property) {
var gr = new GlideRecord("ml_solution");
gr.addQuery("solution_name", solutionName);
gr.addQuery("active", true);
gr.addQuery("version", activeSolution.getVersionNumber());
gr.orderBy("sys_created_on", true);
gr.query();
if (gr.next()) {
return gr.getValue(property);
}
return null;
},
getSolutionUpdateTime : function(solutionSysID) {
var gr = new GlideRecord('ml_solution');
gr.addQuery("sys_id", solutionSysID);
gr.query();
if (gr.next()) {
var value = gr.getValue('update_config');
var timeStamp;
if (value != null) {
var json = JSON.parse(value);
timeStamp = json.update_timestamp;
var dt = new GlideDateTime();
dt.setNumericValue(timeStamp);
return dt;
}
else {
timeStamp = gr.getValue('sys_updated_on');
var gdt = new GlideDateTime(timeStamp);
return gdt;
}
}
return null;
},
type: 'SMMLTableUtils'
};Sys ID
8853e7f8c366201039fc14cb3c40dd00