Hi,
We are currently implementing Life Cycle Management in SAM-P and we and we would like to create a report linking records in the Software Installation table to Software Model Lifecycle table. We created a database view, joining several tables in our attempt to achieve this.
One of the joins in this view is about linking the Software Model table to the Software Discovery Model .
Within a record of the Software Model table, you can click on Show Matching Software Discovery Models in Related Links and it shows something like this:
Product, Language, Platform, Edition and Version.
However, the ServiceNow Docs on
https://docs.servicenow.com/bundle/newyork-it-service-management/page/product/asset-management/conce...
says this about the subject:
The ServiceNow platform uses any of the following field combinations to match the new software discovery model to an
existing software model.
- Display Name, Publisher, and Version
- Display Name and Version if the Publisher field is empty
- Display Name only if the Publisher and Version fields are empty
This is not the same as the filter with the 5 fields mentioned above and the database view produces different results when I use these two alternate field options.
Could anyone advise me on what would be the recommended way to join these two tables?
Also, I'm wondering why you choose to read lifecycle data from sam_sw_model_lifecycle instead of samp_lifecycle_definition.
We had done up this data model and did a database view along the blue route. Wondering if we should have taken the red route.
Based on the above notes, below is the solution that can be used.
Create a base view with four tables as below. As the data resides in three different tables you will need to add them to the view and then add relevant conditions.
1. cmdb_sam_sw_discovery_model
2. cmdb_sam_sw_install (csam_discovery_model=samlm_sys_id)
3. samp_sw_entitlement_definition (dmap_sw_product=samlm_norm_product && dmap_version=samlm_norm_version)
4. samp_lifecycle_definition (saml_entitlement_definition=dmap_sys_id)
This should now give you a single report where you can report on softwares installed on a computer and EOL dates based on DMAP and Lifecycle.
Note: We can only match base on Product and Version. If you need the exact match you would need to write a script to put them all into a seperate table and this requires a bit of work. It would be good to create an idea on idea portal soo ServiceNow can add this to the required functionality.
This will almost work, but will result in duplicate results because multiple matching samp_sw_entitlement_definitions exist, even when matching on all the norm_ fields (edition, language, platform, etc). The exact same piece of software can be licensed differently, therefore there will be multiple matching Discovery Maps for the same Discovery Model.
If you go through cmdb_software_product_model to get to sam_sw_model_lifecycle, it should fix that, but you'll need models created for all the software you're managing.
The functions in the script below can help to create Software Models for either all discovered software, or all software received from the content service (in case you need to analyse lifecycle data on software you are considering purchasing in the same view as purchased software).
This is as far as we've gotten in our ogranisation, but are still a bit away from a working solution. Feedback, updates, assistance, and advise welcome.
/*OBJECTIVE: To meet the business objective that all discovered, and all known software can be "managed" (blacklisted etc), this script ensures that all records in samp_sw_entitlement_definition, and all normalised records in cmdb_sam_sw_discovery_model have a cmdb_software_product_model. Logs start with samp.syncModels*/
// TODO Move functions into a sampUtils script include.
// TODO Write business rules to maintain cmdb_software_product_model models for new/updated samp_sw_entitlement_definition and cmdb_sam_sw_discovery_model records.
(function syncModels(syncDiscoMapsStatus, syncDiscoModelsStatus) {
gs.log("samp.syncModels: Starting");
syncDiscoMapsStatus = syncDiscoMaps();
if (syncDiscoMapsStatus) // syncs Discovery Models AFTER Discovery Maps have been synced.
syncDiscoModelsStatus = syncDiscoModels();
gs.log("samp.syncModels: Finished. syncDiscoMapsStatus = " + syncDiscoMapsStatus + ", syncDiscoModelsStatus = " + syncDiscoModelsStatus);
}
(false, false));
function syncDiscoMaps() {
// Ensures that every samp_sw_entitlement_definition has a corrisponding cmdb_software_product_model. This ensures that all known software can be blacklisted, released, etc...
var commonFields = getCommonFields('samp_sw_entitlement_definition', 'cmdb_software_product_model'); // Discovery and Product Models will likely have common fields, and this might be expanded upon in future releases, so lets capture common field names (excluding sys fields) here and sync them later in the function.
var grMap = new GlideRecord('samp_sw_entitlement_definition');
grMap.query();
while (grMap.next()) {
var product = grMap.getValue('sw_product');
var manufacturer = new SAMCoreCompanyUtil().resolveCoreCompanyForSoftwareProduct(product);
var productModelJSON = {
"manufacturer": manufacturer,
"product": product,
"discovery_map": grMap.getValue('sys_id'),
"source_table": "samp_sw_entitlement_definition",
"source_record": grMap.getValue('sys_id'),
"mandatory_coalesce_fields": ["product"],
"optional_coalesce_fields": ["version", "edition", "language", "platform", "edition_operator", "version_operator", "language_operator", "platform_operator"]
};
for (var field in commonFields)
productModelJSON[commonFields[field]] = grMap.getValue(commonFields[field]);
insertOrUpdateProductModels(productModelJSON);
}
return true;
}
function syncDiscoModels() {
// Ensures that every normalised cmdb_sam_sw_discovery_model has a corrisponding cmdb_software_product_model. This ensures that all installed software that has been normalised can be blacklisted, and be assessed for software currency etc...
var grDiscoveryModel = new GlideRecord('cmdb_sam_sw_discovery_model');
grDiscoveryModel.addQuery('status', 'normalized');
grDiscoveryModel.addQuery('modelISEMPTY');
grDiscoveryModel.query();
while (grDiscoveryModel.next()) {
var product = grDiscoveryModel.getValue('norm_product');
var manufacturer = new SAMCoreCompanyUtil().resolveCoreCompanyForSoftwareProduct(product);
var productModelJSON = {
"manufacturer": manufacturer,
"product": product,
"edition": grDiscoveryModel.getValue('norm_edition'),
"version": grDiscoveryModel.getValue('norm_version'),
"language": grDiscoveryModel.getValue('norm_language'),
"platform": grDiscoveryModel.getValue('norm_platform'),
"source_table": "cmdb_sam_sw_discovery_model",
"source_record": grDiscoveryModel.getValue('sys_id'),
"mandatory_coalesce_fields": ["product"],
"optional_coalesce_fields": ["version", "edition", "language", "platform", "edition_operator", "version_operator", "language_operator", "platform_operator"]
};
grDiscoveryModel.setValue('model', insertOrUpdateProductModels(productModelJSON) || ""); // Creates the Product Model and relates it to the Discovery Model.
grDiscoveryModel.update();
}
return true;
}
function insertOrUpdateProductModels(productModelJSON) {
// Takes a JSON payload in the following format and inserts or updates a cmdb_software_product_model record. product, version, source_table, source_record, mandatory_coalesce_fields, and optional_coalesce_fields are requied. mandatory_coalesce_fields will always try to match, optional_coalesce_fields will try to match only if the field is popualted in the source record. Any other fields added to the JSON will populate the corrisponding field on the cmdb_software_product_model table.
/*
productModelJSON = {
"product": "d9f89df98df7sd7f9s87s98g7s8g7sd7g98", // a sys_id
"version": "1.5", // a String
"source_table": "cmdb_sam_sw_discovery_model", // a table name
"source_record": "d0u09df8ug9dfg89df8sdfgysdyg9dy", // a sys_id
"mandatory_coalesce_fields": ["product", "version"], // an array of field names
"optional_coalesce_fields": ["edition", "language", "platform", "edition_operator", "version_operator", "language_operator", "platform_operator"], // an array of field names
"<fieldname>": "<value>"; // any field name and value to be populated... add as many of these as is needed.
};
*/
gs.log("samp.syncModels.insertOrUpdateProductModels: processing " + JSON.stringify(productModelJSON));
try {
var grProductModels = new GlideRecord('cmdb_software_product_model');
// Add queries for mandatory_coalesce_fields. If a mandatory_coalesce_fields object has no value in productModelJSON, abort and log it.
for (var mcf in mandatory_coalesce_fields) {
if (!productModelJSON[mandatory_coalesce_fields[mcf]]) {
gs.log("samp.syncModels.insertOrUpdateProductModels [ERROR]: Mandatory Coalesce Field " + mandatory_coalesce_fields[mcf] + " missing on " + productModelJSON.source_table + " record " + productModelJSON.source_record);
return;
}
grProductModels.addQuery(mandatory_coalesce_fields[mcf], productModelJSON[mandatory_coalesce_fields[mcf]]);
}
// Add queries for optional_coalesce_fields. If an optional_coalesce_fields object has no value in productModelJSON, skip it and proceed with the rest of the function.
for (var ocf in optional_coalesce_fields)
if (productModelJSON[optional_coalesce_fields[ocf]])
grProductModels.addQuery(optional_coalesce_fields[ocf], productModelJSON[optional_coalesce_fields[ocf]]);
grProductModels.query();
if (grProductModels.hasNext()) {
while (grProductModels.next()) {
var relatedMap = grProductModels.getValue('discovery_map');
if (productModelJSON.discovery_map && (relatedMap == productModelJSON.discovery_map || !relatedMap)) {
grProductModels = setValues(grProductModels, productModelJSON);
grProductModels.update();
}
gs.log("samp.syncModels.insertOrUpdateProductModels: updated " + grProductModels.getValue('sys_id') + " with " + JSON.stringify(productModelJSON));
}
} else {
grProductModels.initialize();
grProductModels = setValues(grProductModels, productModelJSON);
grProductModels.insert();
gs.log("samp.syncModels.insertOrUpdateProductModels: inserted " + grProductModels.getValue('sys_id') + " with " + JSON.stringify(productModelJSON));
}
} catch (err) {
gs.log("samp.syncModels.insertOrUpdateProductModels [ERROR]: " + err + " on " + productModelJSON.source_table + " record " + productModelJSON.source_record + ". Payload = " + JSON.stringify(productModelJSON));
}
return grProductModels.getValue('sys_id');
}
function setValues(grProductModels, productModelJSON) {
// Sets values on the cmdb_software_product_model table in a consistant way based on the values in productModelJSON.
for (var key in productModelJSON)
if (grProductModels.isValidField(key))
grProductModels.setValue(key, productModelJSON[key]);
return grProductModels;
}
function getCommonFields(table1, table2) {
var commonFields = [];
var grTable1 = getTableAsObject(table1);
var grTable2 = getTableAsObject(table2);
var gRU = new GlideRecordUtil();
var table1Fields = gRU.getFields(grTable1);
for (var key in table1Fields)
if (table1Fields[key].substring(0, 4) != "sys_" && grTable2.isValidField(table1Fields[key]))
commonFields.push(table1Fields[key]);
return commonFields;
}
function getTableAsObject(table) {
var grTable = new GlideRecord(table);
grTable.query();
grTable.next();
return grTable;
}
