Name
sn_cmdb_ws.MultiSourceQueryProcessor
Description
Collection of APIs powering CMDB Multisource workspace dashboard
Script
var MultiSourceQueryProcessor = Class.create();
MultiSourceQueryProcessor.prototype = {
MULTISOURCE_DATA: 'cmdb_multisource_data',
CIS_BY_NUMBER_OF_SOURCES: 'sn_cmdb_ws_ms_cis_by_number_of_sources',
CIS_WITH_SINGLE_SOURCE: 'sn_cmdb_ws_ms_cis_with_single_source',
MULTISOURCE_CLASS_METADATA: 'sn_cmdb_ws_ms_class_metadata',
MULTISOURCE_DASHBOARD_DATA: 'sn_cmdb_ws_ms_ci_dashboard_data',
DISCOVERY_SOURCES_NOT_REPORTING: 'sn_cmdb_ws_ms_discovery_sources_not_reporting',
MULTISOURCE_COLUMN_METADATA: 'cmdb_multisource_column_metadata',
CMDB_MULTISOURCE_DIFF_QUERY_VIEW: 'cmdb_multisource_diff_query_view',
CMDB_MULTISOURCE_DIFF_QUERY_VIEW_PREFIX_1: 'msd1_',
CMDB_MULTISOURCE_DIFF_QUERY_VIEW_PREFIX_2: 'msd2_',
DATA_MISMATCH_CONFIG: 'sn_cmdb_ws_ms_data_mismatch_config',
DATA_MISMATCH: 'sn_cmdb_ws_ms_data_mismatch',
MULTISOURCE_REPORT_MAX_LIMIT_PROPERTY: 'sn_cmdb_ws.ms.report_max_limit',
DISCOVERY_SOURCE_NOT_REPORTING_MAX_DAYS_PROPERTY: 'sn_cmdb_ws.ms.discovery_source_not_reporting_max_days',
GLIDE_AGGREGATES_MAX_LIMIT_PROPERTY: 'glide.db.max.aggregates',
CMDB_CI: 'cmdb_ci',
READ_REPLICA_CATEGORY: 'cmdb_health_score',
// Columns in cmdb_multisource_data
DISCOVERY_SOURCE: 'discovery_source',
CLASS_NAME: 'class',
CI: 'ci',
SYS_ID: 'sys_id',
CMDB_REFERENCE: 'cmdb_reference',
MAX_COUNT: 'max_count',
CURRENT_COUNT: 'current_count',
READ_COUNT: 'read_count',
WRITE_COUNT: 'write_count',
// Columns in sn_cmdb_ws_ms_class_metadata
TABLE: 'table',
CI_COUNT_PERCENT: 'ci_count_percent',
// Columns in sn_cmdb_ws_ms_data_mismatch_config
ATTRIBUTES: 'attributes',
CONDITION_TYPE: 'condition_type',
DISCOVERY_SOURCES: 'discovery_sources',
// Columns in sn_cmdb_ws_ms_data_mismatch
SOURCE_1: 'source_1',
SOURCE_1_VALUE: 'source_1_value',
CLASS_1: 'class_1',
SOURCE_2: 'source_2',
SOURCE_2_VALUE: 'source_2_value',
CLASS_2: 'class_2',
DIFF_FIELD: 'diff_field',
// Columns in sn_cmdb_ws_ms_ci_by_number_source
SOURCE_COUNT: 'source_count',
CI_COUNT: 'ci_count',
STATE: 'state',
DRAFT: 1,
READY: 2,
RETIRED: 3,
//Columns in sn_cmdb_ws_ms_ci_coverage
SOURCE: 'source',
// Columns in table sn_cmdb_ws_ms_ci_dashboard_data
CHART: 'chart',
KEY: 'key',
VALUE: 'value',
// Charts in table sn_cmdb_ws_ms_ci_dashboard_data
NUMBER_OF_DISCOVERY_SOURCES: 'number_of_discovery_sources',
RAW_RECORDS: 'raw_records',
RECONCILED_CIS: 'reconciled_cis',
CHART_DATA_MISMATCH: 'data_mismatch',
CHART_CIS_WITH_SINGLE_SOURCE: 'cis_with_single_source',
CHART_CIS_BY_NUMBER_OF_SOURCES: 'cis_by_number_of_sources',
SOURCE_OVERVIEW: 'source_overview',
CHART_DISCOVERY_SOURCE_NOT_REPORTING: 'discovery_source_not_reporting',
// Use this constant in dashboard data when there are no CIs discovered by single source
ZERO_CIS_WITH_A_SINGLE_SOURCE_KEY: 'zero_cis_with_a_single_source',
ZERO_CIS_WITH_A_SINGLE_SOURCE_VALUE: 0,
// Columns in sn_cmdb_ws_ms_discovery_sources_not_reporting
LAST_DISCOVERED: 'last_discovered',
// Columns in cmdb_multisource_column_metadata
FIELD: 'field',
MS_TABLE_FIELD: 'ms_table_field',
// Query constants
NSAMEAS: 'NSAMEAS',
LT_FIELD: 'LT_FIELD',
IN: 'IN',
initialize: function () {
this.classConfig = this._getMultisourceClassConfig();
this.columnMetadataMapping = this._populateColumnMapping();
this.MULTISOURCE_REPORT_MAX_LIMIT = gs.getProperty(this.MULTISOURCE_REPORT_MAX_LIMIT_PROPERTY, 100000);
this.DISCOVERY_SOURCE_NOT_REPORTING_MAX_DAYS = gs.getProperty(this.DISCOVERY_SOURCE_NOT_REPORTING_MAX_DAYS_PROPERTY, 7);
// Default value of max aggregates is from GlideAggregate.java
this.MAX_SUPPORTED_GLIDE_AGGREGATES = parseInt(gs.getProperty(this.GLIDE_AGGREGATES_MAX_LIMIT_PROPERTY, 100000));
},
populateCIsBySource: function() {
// Cleanup the existing aggregated entries
// If there any records in the aggregate table in "Draft" state, mark them as "Retired"
this._updateState(this.DRAFT, this.RETIRED, this.MULTISOURCE_DASHBOARD_DATA, this.SOURCE_OVERVIEW);
// Calculate the aggregate stats and populate in dashboard data with key as "Source_Overview"
var agg = new GlideAggregate(this.MULTISOURCE_DATA);
agg.addAggregate('COUNT', 'cmdb_ci');
agg.groupBy('discovery_source');
agg.setCategory(this.READ_REPLICA_CATEGORY);
agg.query();
while (agg.next()) {
var ciCount = agg.getAggregate('COUNT', 'cmdb_ci');
var discoverySource = agg.getValue('discovery_source');
// Insert a record in draft state
this._populateMultisourceDashboardData(this.SOURCE_OVERVIEW, discoverySource, ciCount, this.DRAFT);
}
// Update the existing records in "Ready" state to "Retired"
this._updateState(this.READY, this.RETIRED, this.MULTISOURCE_DASHBOARD_DATA, this.SOURCE_OVERVIEW);
// Update the existing records in "Draft" state to "Ready"
this._updateState(this.DRAFT, this.READY, this.MULTISOURCE_DASHBOARD_DATA, this.SOURCE_OVERVIEW);
},
populateCIsGroupedByDiscoverySource: function() {
// Max discovery source number counts i.e. 1, 2, 3, 4 and 5+
var maxSources = 5;
var totalCICount = 0;
var sourceNumberCounts = [];
// Cleanup the existing aggregated discovery source counts for CIs
// If there any records in the aggregate table in "Draft" state, mark them as "Retired"
this._updateState(this.DRAFT, this.RETIRED, this.MULTISOURCE_DASHBOARD_DATA, this.CHART_CIS_BY_NUMBER_OF_SOURCES);
// If there any records in the data drill down table in "Draft" state, mark them as "Retired"
// We need this because the new records we will create further will be in "Draft" state by default and we move them to "Ready" state.
this._updateState(this.DRAFT, this.RETIRED, this.CIS_BY_NUMBER_OF_SOURCES);
// Populate aggregate discovery source counts for CIs in 'sn_cmdb_ws_ms_ci_by_number_source'
// These counts are used to show the reports in CMDB Workspace
for (var i = 1; i <= maxSources; i++) {
var ciCount = 0;
var sourceCount = (i == maxSources) ? i + "+" : i + "";
var start = 0, end = this.MAX_SUPPORTED_GLIDE_AGGREGATES - 1;
// Platform has a limitation on number of aggregates returned using query, workaround is to call multiple times with varying offset
// If glide aggregate returns less data rows than asked (end -start), it indicates there is no more data to query
// If it returned exact rows as asked, we need to query again as there is potential of more data available
do {
var singleBatchRowCount = 0;
ga = this._getAggregateByDiscoverySource(i, maxSources, start, end);
while (ga.next()) {
singleBatchRowCount++;
ciCount++;
}
start = end + 1;
end = end + this.MAX_SUPPORTED_GLIDE_AGGREGATES;
} while (singleBatchRowCount == this.MAX_SUPPORTED_GLIDE_AGGREGATES);
// Insert a record in draft state
this._populateMultisourceDashboardData(this.CHART_CIS_BY_NUMBER_OF_SOURCES, sourceCount, ciCount, this.DRAFT);
// Increase the total CI count
totalCICount += ciCount;
// Use this to get the max limit for each source number count once aggregate data is populated
var sourceNumberCount = {
"source_count": sourceCount,
"ci_count": ciCount
};
sourceNumberCounts.push(sourceNumberCount);
}
// Populate the max limit for each source number count
this._updateSourceNumberCountMaxLimit(sourceNumberCounts, totalCICount);
// Populate CI by discovery source number data (ci, class and discovery source count)
// These are displayed when user clicks on one of the categories in the donut on the report
for (var j = 1; j <= maxSources; j++) {
var count = 0;
var ga;
var ciByNumberSourceDataGr = new GlideRecord(this.CIS_BY_NUMBER_OF_SOURCES);
/**
* Check the below conditions before inserting into 'sn_cmdb_ws_ms_ci_by_number_source_data' table
* 1. If total CI (for all discovery source number) count is less than max report limit, populate everyting.
* 2. If the class config is not defined, populate upto the max limit per discovery source number
* 3. Otherwise, iterate over the class config and follow the below steps:
* a. Calculate the max limit using the class config percent value
* b. Populate till max limit is reached and repeat the same for all classes in the config
*/
if (totalCICount < this.MULTISOURCE_REPORT_MAX_LIMIT) {
gs.debug("Total CI count: " + totalCICount + " is less than sn_cmdb_ws.ms.report_max_limit: " + this.MULTISOURCE_REPORT_MAX_LIMIT);
gs.debug("Getting all records from cmdb_multisource_data for CIs with " + j + " source(s).");
start = 0, end = this.MAX_SUPPORTED_GLIDE_AGGREGATES - 1;
// Read data in batches with varying offsets and exhaust all the data from database
do {
var singleBatchRowCount = 0;
ga = this._getAggregateByDiscoverySource(j, maxSources, start, end);
while (ga.next()) {
this._populateNumberSourceData(ciByNumberSourceDataGr, ga, j, maxSources);
singleBatchRowCount++;
}
start = end + 1;
end = end + this.MAX_SUPPORTED_GLIDE_AGGREGATES;
} while (singleBatchRowCount == this.MAX_SUPPORTED_GLIDE_AGGREGATES);
} else if (this.classConfig.length == 0) {
gs.debug("Total CI count: " + totalCICount + " is greater/equal than sn_cmdb_ws.ms.report_max_limit: " + this.MULTISOURCE_REPORT_MAX_LIMIT);
gs.debug(this.MULTISOURCE_CLASS_METADATA + " is empty. Getting max of " + sourceNumberCounts[j - 1].max_limit + " records for CIs with " + j + " source(s)");
count = 0;
start = 0, end = this.MAX_SUPPORTED_GLIDE_AGGREGATES - 1;
// Read data in batches with varying offsets and exhaust all the data from database
do {
var singleBatchRowCount = 0;
ga = this._getAggregateByDiscoverySource(j, maxSources, start, end);
while (ga.next() && count < sourceNumberCounts[j - 1].max_limit) {
this._populateNumberSourceData(ciByNumberSourceDataGr, ga, j, maxSources);
count++;
singleBatchRowCount++;
}
start = end + 1;
end = end + this.MAX_SUPPORTED_GLIDE_AGGREGATES;
} while (singleBatchRowCount == this.MAX_SUPPORTED_GLIDE_AGGREGATES);
gs.debug("Inserted " + count + " records for CIs with " + j + " source(s)");
} else {
gs.debug("Total CI count: " + totalCICount + " is greater/equal than sn_cmdb_ws.ms.report_max_limit: " + this.MULTISOURCE_REPORT_MAX_LIMIT);
gs.debug(this.MULTISOURCE_CLASS_METADATA + " is not empty");
for (k = 0; k < this.classConfig.length; k++) {
var classConfigMaxLimit = Math.round(sourceNumberCounts[j - 1].max_limit * (this.classConfig[k].ci_count_percent / 100));
count = 0;
start = 0, end = this.MAX_SUPPORTED_GLIDE_AGGREGATES - 1;
// Read data in batches with varying offsets and exhaust all the data from database
do {
var singleBatchRowCount = 0;
ga = this._getAggregateByDiscoverySource(j, maxSources, start, end, this.classConfig[k].table);
gs.debug("Getting max of " + classConfigMaxLimit + " records for " + this.classConfig[k].table + " for CIs with " + j + " source(s)");
while (ga.next() && count < classConfigMaxLimit) {
this._populateNumberSourceData(ciByNumberSourceDataGr, ga, j, maxSources);
count++;
singleBatchRowCount++;
}
start = end + 1;
end = end + this.MAX_SUPPORTED_GLIDE_AGGREGATES;
} while (singleBatchRowCount == this.MAX_SUPPORTED_GLIDE_AGGREGATES);
gs.debug("Inserted " + count + " records for " + this.classConfig[k].table + " for CIs with " + j + " source(s)");
}
}
}
// Update the existing records in aggregate dashboard table from "Ready" state to "Retired"
this._updateState(this.READY, this.RETIRED, this.MULTISOURCE_DASHBOARD_DATA, this.CHART_CIS_BY_NUMBER_OF_SOURCES);
// Mark the existing records in raw data table from "Ready" state to "Retired"
this._updateState(this.READY, this.RETIRED, this.CIS_BY_NUMBER_OF_SOURCES);
// Update the newly created aggregate records in "Draft" state to "Ready"
this._updateState(this.DRAFT, this.READY, this.MULTISOURCE_DASHBOARD_DATA, this.CHART_CIS_BY_NUMBER_OF_SOURCES);
// Mark the existing records in raw data table from "Draft" state to "Ready"
this._updateState(this.DRAFT, this.READY, this.CIS_BY_NUMBER_OF_SOURCES);
},
populateCIsCoverageForSingleSource: function () {
// Cleanup the existing aggregated entries
// If there any records in the aggregate table in "Draft" state, mark them as "Retired"
this._updateState(this.DRAFT, this.RETIRED, this.MULTISOURCE_DASHBOARD_DATA, this.CHART_CIS_WITH_SINGLE_SOURCE);
// If there any records in the data drill down table in "Draft" state, mark them as "Retired"
// We need this because the new records we will create further will be in "Draft" state by default and we move them to "Ready" state.
this._updateState(this.DRAFT, this.RETIRED, this.CIS_WITH_SINGLE_SOURCE);
// Populate number of CIs per source
var sources = {};
var totalCICount = 0;
// Read data in batches with varying offsets and exhaust all the data from database
var start = 0, end = this.MAX_SUPPORTED_GLIDE_AGGREGATES - 1;
do {
var singleBatchRowCount = 0;
var agg = this._getAggregateSingleSource(null, start, end);
while (agg.next()) {
var source = agg.getAggregate('MAX', this.DISCOVERY_SOURCE);
if (typeof sources[source] === 'undefined') {
sources[source] = 1;
} else {
sources[source] = sources[source] + 1;
}
totalCICount++;
singleBatchRowCount++;
}
start = end + 1;
end = end + this.MAX_SUPPORTED_GLIDE_AGGREGATES;
} while (singleBatchRowCount == this.MAX_SUPPORTED_GLIDE_AGGREGATES);
// Populate aggregate data by source and raw data only if there is atleast one CI with a single discovery source
if (Object.keys(sources).length > 0) {
// Insert into intermediate aggregate table
for (source in sources) {
gs.debug(source + "= " + sources[source]);
// Insert a record in draft state
this._populateMultisourceDashboardData(this.CHART_CIS_WITH_SINGLE_SOURCE, source, sources[source], this.DRAFT);
}
// Call populating raw data only if there is atleast one CI with a single discovery source
this._populateRawDataCoverageForSingleSource(totalCICount, sources);
} else {
// Found 0 CIs with single discovery source, populating an empty record for UI
gs.info("Found 0 CIs with single discovery source from multisource data, not populating raw data");
this._populateMultisourceDashboardData(this.CHART_CIS_WITH_SINGLE_SOURCE, this.ZERO_CIS_WITH_A_SINGLE_SOURCE_KEY, this.ZERO_CIS_WITH_A_SINGLE_SOURCE_VALUE, this.DRAFT);
}
// Update the existing aggregate records in dashboard data table from "Ready" state to "Retired"
this._updateState(this.READY, this.RETIRED, this.MULTISOURCE_DASHBOARD_DATA, this.CHART_CIS_WITH_SINGLE_SOURCE);
// Mark the existing raw data records in data drill down table from "Ready" state to "Retired"
this._updateState(this.READY, this.RETIRED, this.CIS_WITH_SINGLE_SOURCE);
// Update the existing aggregate records in dashboard data table from "Draft" state to "Ready"
this._updateState(this.DRAFT, this.READY, this.MULTISOURCE_DASHBOARD_DATA, this.CHART_CIS_WITH_SINGLE_SOURCE);
// Mark the existing raw data records in data drill down table from "Draft" state to "Ready"
this._updateState(this.DRAFT, this.READY, this.CIS_WITH_SINGLE_SOURCE);
},
//Populates the Number of discovery sources in the sn_cmdb_ws_ms_ci_dashboard_data table
populateNumberOfDiscoverySources: function() {
// Update the existing records in "Draft" state to "Retired"
this._updateState(this.DRAFT, this.RETIRED, this.MULTISOURCE_DASHBOARD_DATA, this.NUMBER_OF_DISCOVERY_SOURCES);
// Insert a record in draft state
var count = this._getNumberOfDiscoverySources();
this._populateMultisourceDashboardData(this.NUMBER_OF_DISCOVERY_SOURCES, this.NUMBER_OF_DISCOVERY_SOURCES, count, this.DRAFT);
// Update the existing records in "Ready" state to "Retired"
this._updateState(this.READY, this.RETIRED, this.MULTISOURCE_DASHBOARD_DATA, this.NUMBER_OF_DISCOVERY_SOURCES);
// Update the existing records in "Draft" state to "Ready"
this._updateState(this.DRAFT, this.READY, this.MULTISOURCE_DASHBOARD_DATA, this.NUMBER_OF_DISCOVERY_SOURCES);
},
//Populates the Raw records in the sn_cmdb_ws_ms_ci_dashboard_data table
populateRawRecords: function() {
// Update the existing records in "Draft" state to "Retired"
this._updateState(this.DRAFT, this.RETIRED, this.MULTISOURCE_DASHBOARD_DATA, this.RAW_RECORDS);
// Insert a record in draft state
var count = this._getRawRecords();
this._populateMultisourceDashboardData(this.RAW_RECORDS, this.RAW_RECORDS, count, this.DRAFT);
// Update the existing records in "Ready" state to "Retired"
this._updateState(this.READY, this.RETIRED, this.MULTISOURCE_DASHBOARD_DATA, this.RAW_RECORDS);
// Update the existing records in "Draft" state to "Ready"
this._updateState(this.DRAFT, this.READY, this.MULTISOURCE_DASHBOARD_DATA, this.RAW_RECORDS);
},
//Populates the Reconciled CIs in the sn_cmdb_ws_ms_ci_dashboard_data table
populateReconciledCIs: function() {
// Update the existing records in "Draft" state to "Retired"
this._updateState(this.DRAFT, this.RETIRED, this.MULTISOURCE_DASHBOARD_DATA, this.RECONCILED_CIS);
// Insert a record in draft state
var count = this._getReconciledCIs();
this._populateMultisourceDashboardData(this.RECONCILED_CIS, this.RECONCILED_CIS, count, this.DRAFT);
// Update the existing records in "Ready" state to "Retired"
this._updateState(this.READY, this.RETIRED, this.MULTISOURCE_DASHBOARD_DATA, this.RECONCILED_CIS);
// Update the existing records in "Draft" state to "Ready"
this._updateState(this.DRAFT, this.READY, this.MULTISOURCE_DASHBOARD_DATA, this.RECONCILED_CIS);
},
populateDiscoverySourcesNotReportingCIs: function() {
// If there any records in the data drill down and aggregate tables in "Draft" state, mark them as "Retired"
// We need this because the new records we will create further will be in "Draft" state by default and we move them to "Ready" state.
this._updateState(this.DRAFT, this.RETIRED, this.DISCOVERY_SOURCES_NOT_REPORTING);
this._updateState(this.DRAFT, this.RETIRED, this.MULTISOURCE_DASHBOARD_DATA, this.CHART_DISCOVERY_SOURCE_NOT_REPORTING);
// Populate the aggregate count for discovery sources not reporting
var aggCount = this._evaluateCIsNotReportedByDiscoverySources(0, true);
this._populateMultisourceDashboardData(this.CHART_DISCOVERY_SOURCE_NOT_REPORTING, this.CHART_DISCOVERY_SOURCE_NOT_REPORTING, aggCount, this.DRAFT);
/**
* Check the below conditions before inserting into 'sn_cmdb_ws_ms_discovery_sources_not_reporting' table
* 1. If the class config is not defined or aggregate count is less than max limit, populate upto the max limit defined in 'sn_cmdb_ws.ms.report_max_limit'
* 2. Otherwise, iterate over the class config and follow the below steps:
* a. Calculate the max limit using the class config percent value
* b. Populate till max limit is reached and repeat the same for all classes in the config
*/
if (aggCount < this.MULTISOURCE_REPORT_MAX_LIMIT || this.classConfig.length == 0) {
gs.debug(this.MULTISOURCE_CLASS_METADATA + " is empty or total records is less than max limit. Getting max of " + this.MULTISOURCE_REPORT_MAX_LIMIT
+ " records for CIs not reported by dicovery source in the past " + this.DISCOVERY_SOURCE_NOT_REPORTING_MAX_DAYS + " days.");
this._evaluateCIsNotReportedByDiscoverySources(this.MULTISOURCE_REPORT_MAX_LIMIT, false);
} else {
gs.debug(this.MULTISOURCE_CLASS_METADATA + " is empty. Getting max of " + this.MULTISOURCE_REPORT_MAX_LIMIT
+ " records for CIs not reported by dicovery source in the past " + this.DISCOVERY_SOURCE_NOT_REPORTING_MAX_DAYS + " days.");
for (var i = 0; i < this.classConfig.length; i++) {
var classConfigMaxLimit = Math.round(this.MULTISOURCE_REPORT_MAX_LIMIT * (this.classConfig[i].ci_count_percent / 100));
this._evaluateCIsNotReportedByDiscoverySources(classConfigMaxLimit, false, this.classConfig[i].table);
}
}
// Mark the existing raw data in "Ready" state stored for data drill down and aggregate tables as "Retired"
this._updateState(this.READY, this.RETIRED, this.DISCOVERY_SOURCES_NOT_REPORTING);
this._updateState(this.READY, this.RETIRED, this.MULTISOURCE_DASHBOARD_DATA, this.CHART_DISCOVERY_SOURCE_NOT_REPORTING);
// Mark the current raw data in "Draft" state stored for data drill down and agregate tables as "Ready"
this._updateState(this.DRAFT, this.READY, this.DISCOVERY_SOURCES_NOT_REPORTING);
this._updateState(this.DRAFT, this.READY, this.MULTISOURCE_DASHBOARD_DATA, this.CHART_DISCOVERY_SOURCE_NOT_REPORTING);
},
/**
* Populates the aggregate count of data mismatch records in sn_cmdb_ws_ms_ci_dashboard_data table with key as data_mismatch
* Populates the raw data mismatch records based on data mismatch settings in sn_cmdb_ws_ms_data_mismatch
*/
populateDataMismatch: function () {
// If there any records in the data drill down table in "Draft" state, mark them as "Retired"
// We need this because the new records we will create further will be in "Draft" state by default and we move them to "Ready" state.
this._updateState(this.DRAFT, this.RETIRED, this.MULTISOURCE_DASHBOARD_DATA, this.CHART_DATA_MISMATCH);
this._updateState(this.DRAFT, this.RETIRED, this.DATA_MISMATCH);
// Calculate aggregate data mismatch count and insert with draft state
var count = this._calculateDataMismatchAggregateCount();
this._populateMultisourceDashboardData(this.CHART_DATA_MISMATCH, this.CHART_DATA_MISMATCH, count, this.DRAFT);
// Populates raw data mismatch records in sn_cmdb_ws_ms_data_mismatch table by leveraging sn_cmdb_ws_ms_data_mismatch_config
this._populateDataMismatchRawData(count);
// Update the existing records in "Ready" state to "Retired" for both aggregated and raw data tables
this._updateState(this.READY, this.RETIRED, this.MULTISOURCE_DASHBOARD_DATA, this.CHART_DATA_MISMATCH);
this._updateState(this.READY, this.RETIRED, this.DATA_MISMATCH);
// Update the existing records in "Draft" state to "Ready" for both aggregated and raw data tables
this._updateState(this.DRAFT, this.READY, this.MULTISOURCE_DASHBOARD_DATA, this.CHART_DATA_MISMATCH);
this._updateState(this.DRAFT, this.READY, this.DATA_MISMATCH);
},
/**
* Populate raw data for CIs with a single discovery source
* @param {*} totalCICount total number of CIs which are discovered by single discovery source
* @param {*} sources map of source to number of CIs discovered by single discovery source
* Example of sources= {'ServiceNow'-> 10, 'ServiceWatch' -> 20}
*/
_populateRawDataCoverageForSingleSource: function (totalCICount, sources) {
// Logic for populating raw data starts here
var rawGr = new GlideRecord(this.CIS_WITH_SINGLE_SOURCE);
/**
* Check the below conditions before inserting into 'sn_cmdb_ws_ms_ci_coverage_data' table
* 1. If total CI count is less than max report limit, populate everyting.
* 2. If the class config is not defined, populate upto the max limit per discovery source number
* 3. Otherwise, iterate over the class config and follow the below steps:
* a. Calculate the max limit using the class config percent value
* b. Populate till max limit is reached and repeat the same for all classes in the config
*/
if (totalCICount < this.MULTISOURCE_REPORT_MAX_LIMIT) {
gs.debug("Total CI count: " + totalCICount + " is less than sn_cmdb_ws.ms.report_max_limit: " + this.MULTISOURCE_REPORT_MAX_LIMIT);
// Read data in batches with varying offsets and exhaust all the data from database
var start =0, end = this.MAX_SUPPORTED_GLIDE_AGGREGATES -1 ;
do {
var singleBatchRowCount = 0;
var agg = this._getAggregateSingleSource(null, start, end);
while (agg.next()) {
this._populateRawCIsCoverageBySingleSource(rawGr, agg);
singleBatchRowCount++;
}
start = end + 1;
end = end + this.MAX_SUPPORTED_GLIDE_AGGREGATES;
} while (singleBatchRowCount == this.MAX_SUPPORTED_GLIDE_AGGREGATES);
}
else if (this.classConfig.length == 0) {
gs.debug("Total CI count: " + totalCICount + " is greater than sn_cmdb_ws.ms.report_max_limit: " + this.MULTISOURCE_REPORT_MAX_LIMIT);
gs.debug(this.MULTISOURCE_CLASS_METADATA + " is empty.");
// Max CIs to be inserted per source
var sourceMaxCounts = {};
this._updateSourceMaxCountsSingleSource(totalCICount, this.MULTISOURCE_REPORT_MAX_LIMIT, sources, sourceMaxCounts);
// Read data in batches with varying offsets and exhaust all the data from database
var readWriteCounts = {};
start = 0, end = this.MAX_SUPPORTED_GLIDE_AGGREGATES -1;
do {
var agg = this._getAggregateSingleSource(null, start, end);
readWriteCounts = this._populateRawDataSingleSource(agg, rawGr, sourceMaxCounts, this.MULTISOURCE_REPORT_MAX_LIMIT, readWriteCounts);
start = end + 1;
end = end + this.MAX_SUPPORTED_GLIDE_AGGREGATES;
} while (readWriteCounts[this.READ_COUNT] == this.MAX_SUPPORTED_GLIDE_AGGREGATES);
}
else {
gs.debug("Total CI count: " + totalCICount + " is greater than sn_cmdb_ws.ms.report_max_limit: " + this.MULTISOURCE_REPORT_MAX_LIMIT);
gs.debug(this.MULTISOURCE_CLASS_METADATA + " is not empty.");
for (i = 0; i < this.classConfig.length; i++) {
// Max CIs to be inserted per source
var sourceMaxCounts = {};
// Calculate total CIs to be inserted in raw data table for a class using the percent field defined
var classMaxLimit = this.MULTISOURCE_REPORT_MAX_LIMIT * this.classConfig[i].ci_count_percent * 0.01;
this._updateSourceMaxCountsSingleSource(totalCICount, classMaxLimit, sources, sourceMaxCounts);
var maxRecordsToInsert = this._getMaxRecordsToInsertSingleSource(sourceMaxCounts);
// Read data in batches with varying offsets and exhaust all the data from database
start = 0, end = this.MAX_SUPPORTED_GLIDE_AGGREGATES - 1;
var readWriteCounts = {};
do {
var agg = this._getAggregateSingleSource(this.classConfig[i].table, start, end);
readWriteCounts = this._populateRawDataSingleSource(agg, rawGr, sourceMaxCounts, maxRecordsToInsert, readWriteCounts);
start = end + 1;
end = end + this.MAX_SUPPORTED_GLIDE_AGGREGATES;
} while (readWriteCounts[this.READ_COUNT] == this.MAX_SUPPORTED_GLIDE_AGGREGATES);
}
}
},
/**
* Populate the sn_cmdb_ws_ms_ci_coverage_data table
* sourceMaxCounts: Max CI count for each source
* maxRecordsToInsert: max records to be inserted in the sn_cmdb_ws_ms_ci_coverage_data table
* If number of records reaches to the max records to be inserted, exit the while loop
* When aggreagte query returns 1000 records
* And maxRecordsToInsert is 100
* Then exit while loop when currCount reaches 100
* Populate number of records, read using this glide aggregate query, records written to raw table in readWriteCounts
*/
_populateRawDataSingleSource: function(agg, rawGr, sourceMaxCounts, maxRecordsToInsert, readWriteCounts) {
var batchRowCount = 0;
var totalWriteCount = 0;
if (readWriteCounts && readWriteCounts[this.WRITE_COUNT]) {
totalWriteCount = readWriteCounts[this.WRITE_COUNT];
} else {
readWriteCounts = {};
}
while (agg.next()) {
var source = agg.getAggregate('MAX', this.DISCOVERY_SOURCE);
batchRowCount++;
if (sourceMaxCounts[source].current_count < sourceMaxCounts[source].max_count) {
this._populateRawCIsCoverageBySingleSource(rawGr, agg);
totalWriteCount++;
sourceMaxCounts[source].current_count++;
}
if (totalWriteCount === maxRecordsToInsert)
break;
}
readWriteCounts[this.READ_COUNT] = batchRowCount;
readWriteCounts[this.WRITE_COUNT] = totalWriteCount;
return readWriteCounts;
},
/**
* Returns max records to be inserted in sn_cmdb_ws_ms_ci_coverage_data table
*/
_getMaxRecordsToInsertSingleSource: function(sourceCis) {
var totalRawCount = 0;
for (var source in sourceCis) {
totalRawCount += sourceCis[source].max_count;
}
return totalRawCount;
},
/**
* Updates the max CI count per source and initialize the current count
* If there are 200 total CIs in which 150 are from ServiceNow and 50 are from ServiceWatch
* If the MULTISOURCE_REPORT_MAX_LIMIT is set to 100, this function will return the below object:
* {
* "ServiceNow": { "max_count": "75", "current_count": "0" }",
* "ServiceWatch": { "max_count": "25", "current_count": "0" }"
* }
*/
_updateSourceMaxCountsSingleSource: function(totalCount, totalCis, sources, sourceMaxCounts) {
for (var source in sources) {
var ciCount = {};
var sourcePercent = (sources[source]/totalCount) * 100;
ciCount[this.MAX_COUNT] = Math.round(totalCis * sourcePercent * 0.01);
ciCount[this.CURRENT_COUNT] = 0;
sourceMaxCounts[source] = ciCount;
}
},
/**
* Returns the aggregate query result
*/
_getAggregateSingleSource: function(className, start, end) {
var agg = new GlideAggregate(this.MULTISOURCE_DATA);
if (className) {
agg.addQuery(this.CLASS_NAME, 'INSTANCEOF', className);
}
agg.addAggregate('MAX', this.DISCOVERY_SOURCE);
agg.groupBy(this.CI);
agg.addHaving('COUNT', this.DISCOVERY_SOURCE, '=', '1');
agg.chooseWindow(start, end);
agg.setCategory(this.READ_REPLICA_CATEGORY);
agg.query();
return agg;
},
/**
* Insert a record into the sn_cmdb_ws_ms_ci_coverage_data table
*/
_populateRawCIsCoverageBySingleSource: function(rawGr, agg) {
rawGr.initialize();
rawGr.setValue(this.SOURCE, agg.getAggregate('MAX', this.DISCOVERY_SOURCE));
rawGr.setValue(this.CLASS_NAME, agg.getValue(this.CLASS_NAME));
rawGr.setValue(this.CI, agg.getValue(this.CI));
/**
* Group by CI (Document Id) is currently not working in Multisource dashboard,
* we need to remove this once that defect is fixed
* Populating non-cmdb Sys Id into the cmdb_reference field for grouping purpose
* And also to optimize the check for hierarchy at every row
* UI will have cmdb_reference column instead of Document Id until the defect is fixed
*/
rawGr.setValue(this.CMDB_REFERENCE, agg.getValue(this.CI));
rawGr.insert();
},
/**
* Gets the aggregate object for multisource records grouped by CI for each discovery source count
* Adds a query for class name if passed as a parameter to the function
* Add window for the query results (start, end), it will return a maximum rows of (end -start)
* If glide aggregate returns rows equal to the size(end -start), it indicates more potential data
*/
_getAggregateByDiscoverySource: function(sourceCount, maxSourceCount, start, end, className) {
var agg = new GlideAggregate(this.MULTISOURCE_DATA);
if (className)
agg.addQuery(this.CLASS_NAME, 'INSTANCEOF', className);
agg.groupBy(this.CI);
/**
* maxSourceCount is the maximum number of discovery sources we have for this report i.e. 5
* sourceCount is the current number of discovery sources we want to query i.e. one of 1, 2, 3, 4 or 5+
*
* For CIs with sourceCount 1, 2, 3 and 4, check for exact match e.g. HAVING COUNT(discovery_source) = 2
* For CIs with sourceCount 5+, check for count greater than 4 e.g. HAVING COUNT(discovery_source) > 4
*/
agg.addHaving('COUNT', this.DISCOVERY_SOURCE, sourceCount == maxSourceCount ? '>' : '=', sourceCount == maxSourceCount ? (maxSourceCount - 1) : sourceCount);
agg.chooseWindow(start, end);
agg.setCategory(this.READ_REPLICA_CATEGORY);
agg.query();
return agg;
},
/**
* Populate the sn_cmdb_ws_ms_ci_by_number_source_data table for passed source count i.e. 1, 2, 3, 4, and 5+
*/
_populateNumberSourceData: function(ciByNumberSourceDataGr, ga, sourceCount, maxSources) {
ciByNumberSourceDataGr.initialize();
ciByNumberSourceDataGr.setValue(this.SOURCE_COUNT, sourceCount == maxSources ? sourceCount + "+" : sourceCount + "");
ciByNumberSourceDataGr.setValue(this.CLASS_NAME, ga.getValue(this.CLASS_NAME));
ciByNumberSourceDataGr.setValue(this.CI, ga.getValue(this.CI));
/**
* Group by CI (Document Id) is currently not working in Multisource dashboard,
* we need to remove this once that defect is fixed
* Populating non-cmdb Sys Id into the cmdb_reference field for grouping purpose
* And also to optimize the check for hierarchy at every row
* UI will have cmdb_reference column instead of Document Id until the defect is fixed
*/
ciByNumberSourceDataGr.setValue(this.CMDB_REFERENCE, ga.getValue(this.CI));
ciByNumberSourceDataGr.insert();
},
/**
* Update the max counts for each source number count using the MULTISOURCE_REPORT_MAX_LIMIT property
* Updates an array of objects where each object contains a source_count, ci_count and max_limit
*
* If there are 100 total CIs in which 70 are from 3 discovery sources and 30 are from 5+ discovery sources
* If the MULTISOURCE_REPORT_MAX_LIMIT is set to 50, this function will return the below array:
* [
* {
* "source_count": "3",
* "ci_count": "70",
* "max_limit": "35",
* },
* {
* "source_count": "5+",
* "ci_count": "30",
* "max_limit": "15",
* }
* ]
*/
_updateSourceNumberCountMaxLimit: function(sourceNumberCounts, totalCICount) {
for (var i = 0; i < sourceNumberCounts.length; i++) {
var percent = Math.round((sourceNumberCounts[i].ci_count * 100) / totalCICount);
sourceNumberCounts[i].max_limit = Math.round(this.MULTISOURCE_REPORT_MAX_LIMIT * (percent / 100));
}
},
/**
* Creates an array of objects to store the class metadata for multisource reports
* Each object contains the table name and its ci_count_percent (percentage of CIs to be included)
*/
_getMultisourceClassConfig: function() {
var configs = [];
var gr = new GlideRecord(this.MULTISOURCE_CLASS_METADATA);
gr.query();
while (gr.next()) {
var config = {
"table": gr.getValue(this.TABLE),
"ci_count_percent": gr.getValue(this.CI_COUNT_PERCENT)
};
configs.push(config);
}
return configs;
},
_updateState: function(sourceState, targetState, table, chart) {
var gr = new GlideRecord(table);
if(chart)
gr.addQuery(this.CHART, chart);
gr.addQuery(this.STATE, sourceState);
gr.setValue(this.STATE, targetState);
gr.updateMultiple();
},
//Returns the number of Raw records
_getRawRecords: function() {
var agg = new GlideAggregate(this.MULTISOURCE_DATA);
agg.addAggregate('COUNT');
agg.setCategory(this.READ_REPLICA_CATEGORY);
agg.query();
var records = 0;
if (agg.next())
records = agg.getAggregate('COUNT');
return records;
},
//Returns the number of Reconciled CIs
_getReconciledCIs: function() {
var agg = new GlideAggregate(this.MULTISOURCE_DATA);
agg.addAggregate('COUNT(DISTINCT', this.CI);
agg.setGroup(false);
agg.setCategory(this.READ_REPLICA_CATEGORY);
agg.query();
var records = 0;
if (agg.next())
records = agg.getAggregate('COUNT(DISTINCT', this.CI);
return records;
},
//Returns the number of Discovery sources
_getNumberOfDiscoverySources: function() {
var agg = new GlideAggregate(this.MULTISOURCE_DATA);
agg.addAggregate('COUNT(DISTINCT', this.DISCOVERY_SOURCE);
agg.setGroup(false);
agg.setCategory(this.READ_REPLICA_CATEGORY);
agg.query();
var records = 0;
if (agg.next())
records = agg.getAggregate('COUNT(DISTINCT', this.DISCOVERY_SOURCE);
return records;
},
/**
* Populate the sn_cmdb_ws_ms_ci_dashboard_data table
* chart: Chart on the Multisource Dashboard
* key: Key in the chart
* value: Value in the chart
* state: State in the record i.e. Draft, Ready, Retired
* Example
* chart: Number of discovery sources
* key: number_of_discovery_sources
* value: 15
* state: Draft
*/
_populateMultisourceDashboardData: function(chart, key, value, state) {
var gr = new GlideRecord(this.MULTISOURCE_DASHBOARD_DATA);
gr.initialize();
gr.setValue(this.CHART, chart);
gr.setValue(this.KEY, key);
gr.setValue(this.VALUE, value);
gr.setValue(this.STATE, state);
gr.insert();
},
/**
* Evaluate if a discovery source is not reporting a CI for 'n' number of days
*
* @param limit The total number of records to compute the discovery sources not reporting (0 for aggreagte count)
* @param isCountOnly Flag to indicate this method is called for data count or populating actual data
* @param className Class name to compute the discovery sources not reporting
*/
_evaluateCIsNotReportedByDiscoverySources: function(limit, isCountOnly, className) {
var count = 0;
var ci, lastDiscovered, maxLastDiscovered;
// Get the multisource column mapping for 'last_discovered' in 'cmdb_multisource_column_metadata'
var lastDiscoveredColumnMapping = this._getIndexedColumn(this.CMDB_CI, this.LAST_DISCOVERED);
/**
* Query the 'cmdb_multisource_data' and sort by ci and last_discovered (in descending order).
* Compare last_discovered for all other rows for of that CI with the max last_discovered.
* Populate the 'sn_cmdb_ws_ms_discovery_sources_not_reporting' table where the source is not reporting.
*/
var gr = this._getCIsNotReportedByDiscoverySources(className != null ? className : this.CMDB_CI, lastDiscoveredColumnMapping);
while (gr.next()) {
// Break if we do not want the count of all records and have reached the limit
if (count >= limit && !isCountOnly)
break;
// Check if the current CI is same as the previos CI.
// If not, it means we have to calculate for a different CI. Reset the ci and maxLastDiscovered values
if (ci != gr.getValue(this.CI)) {
ci = gr.getValue(this.CI);
maxLastDiscovered = gr.getValue(lastDiscoveredColumnMapping);
continue;
}
// If the lastDiscovered is older than maxLastDiscovered, populate the 'sn_cmdb_ws_ms_discovery_sources_not_reporting' table
lastDiscovered = gr.getValue(lastDiscoveredColumnMapping);
if (this._isLastDiscoveredOlderThanMaxDays(lastDiscovered, maxLastDiscovered)) {
// Populate the 'sn_cmdb_ws_ms_discovery_sources_not_reporting' only when isCountOnly flag is false
if (count < limit && !isCountOnly)
this._populateDiscoverySourceNotReportingData(ci, gr.getValue(this.CLASS_NAME), gr.getValue(this.DISCOVERY_SOURCE), lastDiscovered, className);
count++;
}
}
return count;
},
/**
* Get the potential list of CIs not reported by discovery sources
* Sort by the last_discovered in descending order so that max value is displayed first
*/
_getCIsNotReportedByDiscoverySources: function(className, lastDiscoveredColumnMapping) {
var gr = new GlideRecord(this.MULTISOURCE_DATA);
gr.addQuery(this.CLASS_NAME, 'INSTANCEOF', className);
gr.addNotNullQuery(lastDiscoveredColumnMapping);
gr.orderBy(this.CI);
gr.orderByDesc(lastDiscoveredColumnMapping);
gr.setCategory(this.READ_REPLICA_CATEGORY);
gr.query();
return gr;
},
/**
* Compare if the last_discovered is older than the max last_discovered by 'n' number of days
*/
_isLastDiscoveredOlderThanMaxDays: function(lastDiscovered, maxLastDiscovered) {
var gd1 = new GlideDateTime(lastDiscovered);
var gd2 = new GlideDateTime(maxLastDiscovered);
var dur = GlideDateTime.subtract(gd1, gd2);
return dur.getRoundedDayPart() > this.DISCOVERY_SOURCE_NOT_REPORTING_MAX_DAYS;
},
/**
* Insert a record into the 'sn_cmdb_ws_ms_discovery_sources_not_reporting' table
*/
_populateDiscoverySourceNotReportingData: function(ci, classname, discoverySource, lastDiscovered) {
var gr = new GlideRecord(this.DISCOVERY_SOURCES_NOT_REPORTING);
gr.initialize();
gr.setValue(this.CI, ci);
/**
* Group by CI (Document Id) is currently not working in Multisource dashboard,
* we need to remove this once that defect is fixed
* Populating non-cmdb Sys Id into the cmdb_reference field for grouping purpose
* And also to optimize the check for hierarchy at every row
* UI will have cmdb_reference column instead of Document Id until the defect is fixed
*/
gr.setValue(this.CMDB_REFERENCE, ci);
gr.setValue(this.CLASS_NAME, classname);
gr.setValue(this.DISCOVERY_SOURCE, discoverySource);
gr.setValue(this.LAST_DISCOVERED, lastDiscovered);
gr.setValue(this.STATE, this.DRAFT);
gr.insert();
},
/**
* Reads data mismatch config table, if no configurations has been defined returns immediately( nothing to process)
* For every defined config, reads the attributes, condition_type and prepares an 'encoded query' to query multisource database view,
* adds additional query conditions to filter data and makes a glideaggregate call.
* Calculates the aggregate count for one config, store it and repeats the process for other configs.
* Sum of all aggregate counts by config is returned to the caller.
* @returns count of aggregate data mismatches
*/
_calculateDataMismatchAggregateCount: function() {
// read the data_mismatch_config table
var configs = this._getDataMismatchConfig();
if (configs.length == 0) {
gs.error("No configurations defined for data mismatch calculation, cannot calculate data mismatch counts");
return -1;
}
var totalRecords = 0;
// iterate over the config
for (var index = 0; index < configs.length; index++) {
var config = configs[index];
// prepare the encoded query with AND/OR condition
var encodedQuery = this._getEncodedDataMismatchQuery(config);
if (!encodedQuery || encodedQuery === '') {
gs.error("Error in building encoded query for config with " + global.JSON.stringify(config));
continue;
}
var agg = new GlideAggregate(this.CMDB_MULTISOURCE_DIFF_QUERY_VIEW);
agg.addQuery(this.CMDB_MULTISOURCE_DIFF_QUERY_VIEW_PREFIX_1 + this.CLASS_NAME, 'INSTANCEOF', config.table);
if (config.discovery_sources && config.discovery_sources !== '') {
agg.addQuery(this.CMDB_MULTISOURCE_DIFF_QUERY_VIEW_PREFIX_1 + this.DISCOVERY_SOURCE, this.IN, config.discovery_sources);
agg.addQuery(this.CMDB_MULTISOURCE_DIFF_QUERY_VIEW_PREFIX_2 + this.DISCOVERY_SOURCE, this.IN, config.discovery_sources);
}
// Add query to remove duplicate rows between discovery sources, follow only one order betwen sources (A < B)
agg.addQuery(this.CMDB_MULTISOURCE_DIFF_QUERY_VIEW_PREFIX_1 + this.DISCOVERY_SOURCE, this.LT_FIELD, this.CMDB_MULTISOURCE_DIFF_QUERY_VIEW_PREFIX_2 + this.DISCOVERY_SOURCE);
agg.addEncodedQuery(encodedQuery);
agg.addAggregate('COUNT');
agg.setCategory(this.READ_REPLICA_CATEGORY);
agg.query();
var records = 0;
// We issue one aggregate query by one data mismatch config
if (agg.next()) {
records = agg.getAggregate('COUNT');
gs.debug("Data mismatch records for class '" + config.table + "' with encodedQuery '" + encodedQuery + "' is " + records);
totalRecords += parseInt(records);
}
}
gs.debug("Total records for data mismatch: " + totalRecords);
return totalRecords;
},
/**
* Populates raw data mismatch records using data mismatch config
* Iterates over the data mismatch config and populate data mismatch records, if aggregate data mismatch count is greater than max rows to write,
* calculates the percentage of raw data mismatch records for each class and limits results to that limit
* @param {*} aggDataMismatchCount aggregate count of data mismatch records
* @returns
*/
_populateDataMismatchRawData: function(aggDataMismatchCount) {
// read the data_mismatch_config table
var configs = this._getDataMismatchConfig();
if (configs.length == 0) {
gs.error("No configurations defined for data mismatch calculation, cannot populate data mismatch records");
return;
}
// iterate over the config
for (var index = 0; index < configs.length; index++) {
var config = configs[index];
// prepare the encoded query with AND/OR condition
var encodedQuery = this._getEncodedDataMismatchQuery(config);
if (!encodedQuery || encodedQuery === '') {
gs.error("Error in building encoded query for config with " + global.JSON.stringify(config));
continue;
}
var maxRowsToWrite = this.MULTISOURCE_REPORT_MAX_LIMIT;
// When total aggregate data mismatch count is greater than max limit, we store partial data in raw mismatch table
if (aggDataMismatchCount > this.MULTISOURCE_REPORT_MAX_LIMIT) {
maxRowsToWrite = this.MULTISOURCE_REPORT_MAX_LIMIT * config.ci_count_percent / 100;
}
this._executeAndPopulateRawDataMismatch(encodedQuery, config, maxRowsToWrite);
}
},
/**
* Prepares the glide record with diff query view, adds encoded query based on input config, executes the query and store results
* If input config has available discovery sources, diff will be applied with only those discovery sources.
* If input config has no available discovery sources, diff will be applied for all discovery sources in the system
* @param {*} encodedQuery evaluated query condition using diff fields from config
* @param {*} config data mismatch config defined for one class ( diff fields, condition_type, percentage)
* @param {*} maxRowsToWrite maximum number of rows to write for this config
* @returns
*/
_executeAndPopulateRawDataMismatch: function(encodedQuery, config, maxRowsToWrite) {
var msGr = new GlideRecord(this.CMDB_MULTISOURCE_DIFF_QUERY_VIEW);
msGr.addQuery(this.CMDB_MULTISOURCE_DIFF_QUERY_VIEW_PREFIX_1 + this.CLASS_NAME, 'INSTANCEOF', config.table);
if (config.discovery_sources && config.discovery_sources !== '') {
msGr.addQuery(this.CMDB_MULTISOURCE_DIFF_QUERY_VIEW_PREFIX_1 + this.DISCOVERY_SOURCE, this.IN, config.discovery_sources);
msGr.addQuery(this.CMDB_MULTISOURCE_DIFF_QUERY_VIEW_PREFIX_2 + this.DISCOVERY_SOURCE, this.IN, config.discovery_sources);
}
// Add query to remove duplicate rows between discovery sources, follow only one order betwen sources (A < B)
msGr.addQuery(this.CMDB_MULTISOURCE_DIFF_QUERY_VIEW_PREFIX_1 + this.DISCOVERY_SOURCE, this.LT_FIELD, this.CMDB_MULTISOURCE_DIFF_QUERY_VIEW_PREFIX_2 + this.DISCOVERY_SOURCE);
msGr.addEncodedQuery(encodedQuery);
msGr.chooseWindow(0, maxRowsToWrite);
msGr.setCategory(this.READ_REPLICA_CATEGORY);
msGr.query();
var mappedColumnsArr = this._getMappedColumnsDataMismatch(config);
this._writeRawDataMismatchRecords(msGr, mappedColumnsArr, maxRowsToWrite);
},
/**
* Reads data from multisource diff query view glide record, populate data mismatch records from multisource glide record,
* Also populate the diff fields( use mapped columns to get the display name) and discovery sources
* @param {*} msGr GlideRecord of diff query view with all encoded conditions
* @param {*} mappedColumns Map of column -> { display name, column name}
* Ex: col51 -> fqdn, fully Qualified Domain Name
* @returns
*/
_writeRawDataMismatchRecords: function(msGr, mappedColumns, maxRowsToWrite) {
if (!msGr) {
gs.error("Failed to receive multisource glide record for populating data into workspace raw data table");
return;
}
var dataMismatchGr = new GlideRecord(this.DATA_MISMATCH);
var rowWrittenCount = 0;
while (msGr.next()) {
// read fields from multisource
var ci = msGr.getValue(this.CMDB_MULTISOURCE_DIFF_QUERY_VIEW_PREFIX_1 + this.CI);
var clazz = msGr.getValue(this.CMDB_MULTISOURCE_DIFF_QUERY_VIEW_PREFIX_1 + this.CLASS_NAME);
var source1 = msGr.getValue(this.CMDB_MULTISOURCE_DIFF_QUERY_VIEW_PREFIX_1 + this.SYS_ID);
var source2 = msGr.getValue(this.CMDB_MULTISOURCE_DIFF_QUERY_VIEW_PREFIX_2 + this.SYS_ID);
for (var index in mappedColumns) {
var columnObj = mappedColumns[index];
var leftMappedColumnValue = msGr.getValue(this.CMDB_MULTISOURCE_DIFF_QUERY_VIEW_PREFIX_1 + columnObj.indexedColumn);
var rightMappedColumnValue = msGr.getValue(this.CMDB_MULTISOURCE_DIFF_QUERY_VIEW_PREFIX_2 + columnObj.indexedColumn);
/**
* In case of OR condition, few selected attributes might have same value on both sides (left and right), and some
* attributes might have different values. DB will return a row when atleast one of the selected attributes has different value for
* OR condition, we don't want to populate raw data records for those attributes where attribute value is same( left == right).
* This situation doesn't happen for AND condition and we need to handle it explicitly
*/
if (leftMappedColumnValue && rightMappedColumnValue && leftMappedColumnValue !== rightMappedColumnValue) {
dataMismatchGr.initialize();
// populate fields in data mismatch table
dataMismatchGr.setValue(this.CLASS_NAME, clazz);
dataMismatchGr.setValue(this.CI, ci);
/**
* Group by CI (Document Id) is currently not working in Multisource dashboard,
* we need to remove this once that defect is fixed
* Populating non-cmdb Sys Id into the cmdb_reference field for grouping purpose
* And also to optimize the check for hierarchy at every row
* UI will have cmdb_reference column instead of Document Id until the defect is fixed
*/
dataMismatchGr.setValue(this.CMDB_REFERENCE, ci);
dataMismatchGr.setValue(this.DIFF_FIELD, columnObj.displayName);
dataMismatchGr.setValue(this.CLASS_1, this.MULTISOURCE_DATA);
dataMismatchGr.setValue(this.SOURCE_1, source1);
dataMismatchGr.setValue(this.SOURCE_1_VALUE, leftMappedColumnValue);
dataMismatchGr.setValue(this.CLASS_2, this.MULTISOURCE_DATA);
dataMismatchGr.setValue(this.SOURCE_2, source2);
dataMismatchGr.setValue(this.SOURCE_2_VALUE, rightMappedColumnValue);
dataMismatchGr.setValue(this.STATE, this.DRAFT);
dataMismatchGr.insert();
rowWrittenCount++;
}
}
if (rowWrittenCount >= maxRowsToWrite) {
// Wrote required number of rows to data mismatch table, avoiding further processing
return;
}
}
},
/**
*
* Calculate the indexed column, display name of the columns defined in config.
* Prepare an array of objects ( each object representing indexed column, display name and column name)
* @param {*} config
* @returns An example of return value for input config
[
{
'indexedColumn': 'col51',
'displayName': 'Fully Qualified Domain Name'
'columnName' : 'fqdn'
},
{
'indexedColumn': 'col52',
'displayName': 'RAM(MB)'
'columnName' : 'ram'
}
]
*/
_getMappedColumnsDataMismatch: function(config) {
var mappedColumnsArr = [];
var attributes = config.attributes.split(",");
var columnName2DisplayNameMap = sn_cmdb.MultiSourceAnalytics.getColumnMap(config.table);
for (var index in attributes) {
var indexedColumn = this._getIndexedColumn(config.table, attributes[index]);
var displayName = columnName2DisplayNameMap[attributes[index]];
var mappedColumns = {
'indexedColumn': indexedColumn,
'displayName': displayName,
'columnName': attributes[index]
};
mappedColumnsArr.push(mappedColumns);
}
return mappedColumnsArr;
},
/**
* Prepares an encoded query with multisource view prefixes and also replace with OR/AND based on specified condition_type
* Comma separated list of attributes are separated and indexed columns for each attribute is calculated and used in the encoded query
* @param {config} config
* @returns encoded query in string format
*/
_getEncodedDataMismatchQuery: function(config) {
var attributes = config.attributes.split(",");
var operator = (config.condition_type == 1) ? '^OR' : '^';
var encodedQuery = '';
for (var i = 0; i < attributes.length; i++) {
if (i != 0) {
encodedQuery += operator;
}
var indexedColumn = this._getIndexedColumn(config.table, attributes[i].trim());
if (indexedColumn && indexedColumn !== '') {
encodedQuery += this.CMDB_MULTISOURCE_DIFF_QUERY_VIEW_PREFIX_1 + indexedColumn + 'NSAMEAS'+ this.CMDB_MULTISOURCE_DIFF_QUERY_VIEW_PREFIX_2+ indexedColumn;
} else {
gs.error("Failed to find the indexed column for class '" + config.table + "' and attribute '" + attributes[i].trim() + "'");
return;
}
}
return encodedQuery;
},
/**
*
* @param {String} className className in CMDB hierarchy
* @param {String} columnName column defined in className of CMDB
* Iterates over all the defined mappings and finds the right indexed column used for column name
* @returns the mapping of column name to indexed column.
* Example: "fqdn"->"col12", "cpu_count"->"col13"}
*/
_getIndexedColumn: function(className, columnName) {
var columnMapping = this._getMultisourceColumnMappingByClass(className);
if (!columnMapping) {
gs.error("Failed to find the column mapping for " + className);
return;
}
return columnMapping[columnName.trim()];
},
/**
* Read the column metadata from the cmdb_multisource_column_metadata table and prepare of Map of class Name -> {attributeName-> indexedColumnName}
* Ex:- {cmdb_ci_linux_server ->{"fqdn"->"col12", "cpu_count"->"col13"}}
*/
_populateColumnMapping: function() {
var columnMapping = {};
var gr = new GlideRecord(this.MULTISOURCE_COLUMN_METADATA);
gr.query();
while (gr.next()) {
var className = gr.getValue(this.TABLE);
if (!columnMapping[className]) {
columnMapping[className] = {};
}
var indexedField = gr.getValue(this.MS_TABLE_FIELD);
var field = gr.getValue(this.FIELD);
columnMapping[className][field] = indexedField;
}
return columnMapping;
},
/**
* Calculates the mapped columns defined at all levels from the input className to cmdb, walking up the hierarchy
* @param className classname in CMDB
* @returns map of column names -> indexed columns
* Ex: {"fqdn"-> "col1", "host_name"-> "col2"}
*/
_getMultisourceColumnMappingByClass: function(className) {
var table = new GlideTableHierarchy(className);
var parents = table.getTables();
var hierarchicalColumnMappingMetadata = {};
if (!this.columnMetadataMapping) {
gs.error("Failed to find the default multisource column metadata from cache");
return;
}
for (var i = 0; i < parents.length; i++) {
gs.debug("Reading column metadata mapping for " + parents[i]);
var columnMetadataMappingByClass = this.columnMetadataMapping[parents[i]];
if (columnMetadataMappingByClass) {
for (var x in columnMetadataMappingByClass) {
// If the mapping is defined at child class, don't add the parent class mapping
if (!hierarchicalColumnMappingMetadata[x]) {
hierarchicalColumnMappingMetadata[x] = columnMetadataMappingByClass[x];
}
}
}
}
return hierarchicalColumnMappingMetadata;
},
/**
* Read the data mismatch config rows from the table and returns config objects
* @returns empty when no config is available otherwise list of config objects
*/
_getDataMismatchConfig: function() {
var configs = [];
var gr = new GlideRecord(this.DATA_MISMATCH_CONFIG);
gr.query();
while (gr.next()) {
var config = {
"table": gr.getValue(this.TABLE),
"ci_count_percent": gr.getValue(this.CI_COUNT_PERCENT),
"attributes": gr.getValue(this.ATTRIBUTES),
"condition_type": gr.getValue(this.CONDITION_TYPE),
"discovery_sources": gr.getValue(this.DISCOVERY_SOURCES)
};
configs.push(config);
}
return configs;
},
type: 'MultiSourceQueryProcessor'
};Sys ID
8f3ac25c0f28411024b63ab1df767ef8