Name
sn_cmdb_ws.CMDBWsInsightUtil
Description
No description available
Script
var CMDBWsInsightUtil = Class.create();
CMDBWsInsightUtil.prototype = Object.extendsObject(CMDBWorkspaceUtil, {
initialize: function() {
this.DC_PROVIDER_MAPPING = {};
},
QB_STATUS: {
COMPLETE: 'COMPLETE',
MAX_LIMIT: 'MAX_LIMIT',
TIME_OUT: 'TIME_OUT',
DISABLED: 'DISABLED'
},
DATACENTER_TYPE: {
CLOUD: 'cloud',
NONCLOUD: 'non_cloud'
},
NONCLOUD_AS_PROVIDER: "Non-cloud",
DC_CLASS_FIELD: "u_logical_datacenter_1.sys_class_name",
CLOUD_SERVICE_ACC_DC_TYPE_FIELD: "u_cloud_service_account_1.datacenter_type",
CLOUD_SERVICE_ACC_CATEGORY: "439485f577b3111078521605bc5a99ae",
COUNT: "COUNT",
/**
* Returns a string of comma separated cloud datacenters
* @param {Boolean} checkAcl whether or not not check the caller's ACL
* @return {String}
*/
getCloudFilters: function(checkAcl) {
return this.getDataCentersOfType(this.DATACENTER_TYPE.CLOUD, checkAcl).join(",");
},
/**
* Returns a string of comma separated non-cloud datacenters
* @param {Boolean} checkAcl whether or not not check the caller's ACL
* @return {String}
*/
getNonCloudFilters: function(checkAcl) {
return this.getDataCentersOfType(this.DATACENTER_TYPE.NONCLOUD, checkAcl).join(",");
},
/**
* Returns an array of datacenters configured for given type
* @param {String} type type of datacenter
* @param {Boolean} checkAcl whether or not not check the caller's ACL
* @return {array}
*/
getDataCentersOfType: function(type, checkAcl) {
var gr;
if (checkAcl) {
gr = new GlideRecordSecure(this.TABLES.SN_CMDB_WS_DATACENTER_TYPE);
} else {
gr = new GlideRecord(this.TABLES.SN_CMDB_WS_DATACENTER_TYPE);
}
gr.addQuery(this.COLS.TYPE, type);
gr.query();
var datacenters = [];
while (gr.next()) {
datacenters.push(gr.getValue(this.COLS.DATACENTER_CLASS));
}
return datacenters;
},
hasCloudDataCenterData: function() {
if (gs.tableExists(this.TABLES.CLOUD_SERVICE_ACCOUNT)) {
var gr = new GlideRecord(this.TABLES.CMDB_CI_LOGICAL_DATACENTER);
gr.addQuery(this.COLS.SYS_CLASS_NAME, 'IN', this.getCloudFilters(false));
gr.setLimit(1);
gr.query();
return gr.hasNext();
} else
return false;
},
/**
* Helper function to check if Cloud vs Noncloud asset tab should be included on landing page
* @return {Boolean}
*/
showCloudVsNonCloudAsset: function() {
var hasCloudData = this.hasCloudDataCenterData();
return hasCloudData;
},
/**
* Helper function to check if Cloud vs Noncloud status container should be shown
* @return {Boolean}
*/
showCloudVsNonCloudStatusContainer: function() {
var count = -1;
var gr = new GlideAggregate(this.TABLES.SN_CMDB_WS_PRODUCT_INSIGHT_DATA);
gr.addAggregate(this.COLS.COUNT);
gr.query();
if (gr.next()) {
count = gr.getAggregate(this.COLS.COUNT);
}
return count > 0 ? true : false;
},
getQueryLatestExecutionData: function(savedQueryId) {
//Originally this function was supposed to be a replica of the same function in QueryBuilderReport (because QBR was not open to all scopes), however
//we need more data than just latest executionId, we also need, last updated on, status and result table name
var status = [this.QB_STATUS.COMPLETE, this.QB_STATUS.MAX_LIMIT, this.QB_STATUS.TIME_OUT];
var result = {
executionId: null,
status: null,
updated: null,
result_table: null
};
var glideSavedQuery = new GlideRecord(this.TABLES.QB_SAVED_QUERY);
if (glideSavedQuery.get(savedQueryId) && glideSavedQuery.canRead()) {
result.result_table = glideSavedQuery.getValue(this.COLS.RESULT_TABLE);
}
var glideQbStatus = new GlideRecord(this.TABLES.QB_QUERY_STATUS);
glideQbStatus.addQuery(this.COLS.TABLE_NAME, result.result_table);
glideQbStatus.addQuery(this.COLS.STATUS, 'IN', status);
glideQbStatus.orderByDesc(this.COLS.UPDATED);
glideQbStatus.setLimit(1);
glideQbStatus.query();
if (glideQbStatus.next() && glideQbStatus.canRead()) {
result.executionId = glideQbStatus.getUniqueValue();
result.status = glideQbStatus.getValue(this.COLS.STATUS);
result.updated = glideQbStatus.getValue(this.COLS.UPDATED);
}
return result;
},
calculateAggregates: function() {
//before doing any agregates, check for cloud data existence, and only proceed if cloud data exists on the instance
if (this.hasCloudDataCenterData()) {
var gr = new GlideRecord(this.TABLES.SN_CMDB_WS_QUERY_CATEGORY);
gr.addQuery(this.COLS.ACTIVE, true);
gr.query();
while (gr.next()) { //for each of the type and ci class combo, i.e, "Cloud Application", "non-cloud server"
var ciClass = gr.getValue(this.COLS.CI_CLASS);
var type = gr.getValue(this.COLS.TYPE);
var qbSavedQuery = gr.getValue(this.COLS.QUERY);
var lastRefreshed = gr.getValue(this.COLS.LAST_REFRESHED);
var ciCategorySysId = gr.getUniqueValue();
gs.debug("processing " + type + " " + ciClass + " query, last refreshed " + lastRefreshed);
var lastExecution = this.getQueryLatestExecutionData(qbSavedQuery);
if (gs.nil(lastRefreshed) || new GlideDateTime(lastExecution.updated).after(new GlideDateTime(lastRefreshed))) {
//make sure results table exists first - no point in aggregating if result table does not exist
if (gs.tableExists(lastExecution.result_table)) {
//get new aggregate for this ciClass and type combo - only if last execution status is "COMPLETE"
if (lastExecution.status == this.QB_STATUS.COMPLETE) {
if (type == this.DATACENTER_TYPE.CLOUD) {
this.calculateCloudAggregate(ciCategorySysId, lastExecution.executionId, lastExecution.result_table);
} else {
this.calculateNonCloudAggregate(ciCategorySysId, lastExecution.executionId, lastExecution.result_table);
}
// reset the failure count
gr.setValue(this.COLS.FAILURE_COUNT, 0);
} else {
//mark agg data for this ci class & type as "retired"
gs.debug("The last execution of " + type + " " + ciClass + " did not complete. It was " + lastExecution.status);
this._updateState(this.STATES.READY, this.STATES.RETIRED, ciCategorySysId);
// +1 the current failure count
gr.setValue(this.COLS.FAILURE_COUNT, parseInt(gr.getValue(this.COLS.FAILURE_COUNT)) + 1);
}
//update the status and lastRefresh date on the query_category table
gr.setValue(this.COLS.LAST_REFRESHED, lastExecution.updated);
gr.setValue(this.COLS.STATUS, lastExecution.status);
gr.update();
} else {
gs.debug("result table for " + type + " " + ciClass + " does not exist yet");
}
} else {
gs.debug("last execution query's updated time for " + type + " " + ciClass + " has not changed. No new data");
// if the underlying job has been disabled, mark the category as disabled and retire the aggregate data
var sysAutoGr = new GlideRecord(this.TABLES.SYSAUTO_QUERY_BUILDER);
sysAutoGr.addQuery(this.COLS.QUERY, qbSavedQuery);
sysAutoGr.query();
if (sysAutoGr.next()) {
var isJobActive = sysAutoGr.getDisplayValue(this.COLS.ACTIVE) === 'true' ? true : false;
if (!isJobActive) {
this._updateState(this.STATES.READY, this.STATES.RETIRED, ciCategorySysId);
gr.setValue(this.COLS.STATUS, this.QB_STATUS.DISABLED);
gr.update();
}
}
}
}
}
},
updateQBJobActive: function(queryId, active) {
var sysAutoGr = new GlideRecord(this.TABLES.SYSAUTO_QUERY_BUILDER);
sysAutoGr.addQuery(this.COLS.QUERY, queryId);
sysAutoGr.query();
if (sysAutoGr.next()) {
sysAutoGr.setValue(this.COLS.ACTIVE, active);
sysAutoGr.update();
}
},
getProvider: function(dcClass) {
var providerName = null;
if (this.DC_PROVIDER_MAPPING[dcClass]) {
gs.debug("dc provider mapping exists for " + dcClass + ". It is " + this.DC_PROVIDER_MAPPING[dcClass]);
return this.DC_PROVIDER_MAPPING[dcClass];
} else {
gs.debug("dc provider mapping does not exist for " + dcClass + ". Looking up");
//given a logical datacenter class, return the shortened provider name
var gr = new GlideRecord(this.TABLES.SN_CMDB_WS_DATACENTER_TYPE);
gr.addQuery(this.COLS.DATACENTER_CLASS, dcClass);
gr.query();
if (gr.next()) {
providerName = gr.getValue(this.COLS.CLOUD_PROVIDER);
//add to dc provider mapping
this.DC_PROVIDER_MAPPING[dcClass] = providerName;
}
}
return providerName;
},
calculateCloudAggregate: function(ciCategory, executionId, resultTable) {
this._updateState(this.STATES.DRAFT, this.STATES.RETIRED, ciCategory);
//needs a different group by for cloud service account, since it needs to be grouped by datacenter_type field
var groupByField = ciCategory === this.CLOUD_SERVICE_ACC_CATEGORY ? this.CLOUD_SERVICE_ACC_DC_TYPE_FIELD : this.DC_CLASS_FIELD;
var agg = new GlideAggregate(resultTable);
agg.addQuery(this.COLS.QUERY, executionId);
agg.addAggregate(this.COUNT);
agg.groupBy(groupByField);
agg.query();
while (agg.next()) {
var dcClass = agg.getValue(groupByField);
var count = agg.getAggregate(this.COUNT);
var provider = this.getProvider(dcClass); //convert the dcClass into a provider
//insert into the agg table if count is a positive number
if (count > 0) {
gs.debug("Inserting record into agg table for cloud " + ciCategory + " provider " + provider + " count " + count);
this._populateCloudInsightAggData(this.CATEGORY.CLOUD_AND_NONCLOUD_CIS, ciCategory, provider, count, this.STATES.DRAFT);
}
}
// Update the existing records in "Ready" state to "Retired"
this._updateState(this.STATES.READY, this.STATES.RETIRED, ciCategory);
// Update the existing records in "Draft" state to "Ready"
this._updateState(this.STATES.DRAFT, this.STATES.READY, ciCategory);
},
calculateNonCloudAggregate: function(ciCategory, executionId, resultTable) {
var aggCount = -1;
//if there are any agg records in "draft" state, for this ciCategory, mark them as "retired"
this._updateState(this.STATES.DRAFT, this.STATES.RETIRED, ciCategory);
var agg = new GlideAggregate(resultTable);
agg.addQuery(this.COLS.QUERY, executionId); //query=4228348f41e31110f8774131f61c4c85
agg.addAggregate(this.COUNT);
agg.query();
if (agg.next()) {
aggCount = agg.getAggregate(this.COUNT);
}
if (aggCount > 0) { //only insert when agg count is a positive number
gs.debug("Inserting record into agg table for non-cloud " + ciCategory + " count " + aggCount);
this._populateCloudInsightAggData(this.CATEGORY.CLOUD_AND_NONCLOUD_CIS, ciCategory, this.NONCLOUD_AS_PROVIDER, aggCount, this.STATES.DRAFT);
}
// Update the existing records in "Ready" state to "Retired"
this._updateState(this.STATES.READY, this.STATES.RETIRED, ciCategory);
// Update the existing records in "Draft" state to "Ready"
this._updateState(this.STATES.DRAFT, this.STATES.READY, ciCategory);
},
_updateState: function(sourceState, targetState, ciCategory) {
var gr = new GlideRecord(this.TABLES.SN_CMDB_WS_PRODUCT_INSIGHT_DATA);
if (ciCategory)
gr.addQuery(this.COLS.CI_CATEGORY, ciCategory);
gr.addQuery(this.COLS.STATE, sourceState);
gr.setValue(this.COLS.STATE, targetState);
gr.updateMultiple();
},
_populateCloudInsightAggData: function(chart, ciCategory, cloudProvider, count, state) {
var gr = new GlideRecord(this.TABLES.SN_CMDB_WS_PRODUCT_INSIGHT_DATA);
gr.initialize();
gr.setValue(this.COLS.CHART, chart);
gr.setValue(this.COLS.CI_CATEGORY, ciCategory);
gr.setValue(this.COLS.CLOUD_PROVIDER, cloudProvider);
gr.setValue(this.COLS.COUNT, count);
gr.setValue(this.COLS.STATE, state);
gr.insert();
},
getCIClasses: function() {
var ciClassesArr = [];
var ga = new GlideAggregate(this.TABLES.SN_CMDB_WS_PRODUCT_INSIGHT_DATA);
ga.addQuery(this.COLS.STATE, this.STATES.READY);
ga.groupBy(this.COLS.CI_CATEGORY + this.COLS.DOT + this.COLS.CI_CLASS);
ga.orderBy(this.COLS.CI_CATEGORY + this.COLS.DOT + this.COLS.CI_CLASS);
ga.query();
while (ga.next()) {
ciClassesArr.push(ga.getValue(this.COLS.CI_CATEGORY + this.COLS.DOT + this.COLS.CI_CLASS));
}
return ciClassesArr;
},
getDataCenterTypeOfCloudProvider: function(cloudProvider) {
var gr = new GlideRecord(this.TABLES.SN_CMDB_WS_DATACENTER_TYPE);
gr.addQuery(this.COLS.CLOUD_PROVIDER, cloudProvider);
gr.query();
return gr;
},
isValidRecord: function(table, query) {
var gr = new GlideRecord(table);
gr.addEncodedQuery(query);
gr.setLimit(1);
gr.query();
return gr.hasNext();
},
migrateInsightDataRecords: function() {
var insightDataGr = new GlideRecord(this.TABLES.SN_CMDB_WS_INSIGHT_DATA);
insightDataGr.query();
while (insightDataGr.next()) {
var gr = new GlideRecord(this.TABLES.SN_CMDB_WS_PRODUCT_INSIGHT_DATA);
gr.initialize();
gr.setValue(this.COLS.CHART, this.CATEGORY.CLOUD_AND_NONCLOUD_CIS);
gr.setValue(this.COLS.CI_CATEGORY, insightDataGr.getValue(this.COLS.CI_CATEGORY));
gr.setValue(this.COLS.CLOUD_PROVIDER, insightDataGr.getValue(this.COLS.CLOUD_PROVIDER));
gr.setValue(this.COLS.COUNT, insightDataGr.getValue(this.COLS.COUNT));
gr.setValue(this.COLS.STATE, insightDataGr.getValue(this.COLS.STATE));
gr.insert();
}
},
type: 'CMDBWsInsightUtil'
});Sys ID
dbfdf10eeb13111094bbb5d5d852280d