How do I report on a configuration item's certification status?

[Robert Campbell]

I want to show one result for each configuration item.  If a configuration item has any element that has a state of failed, that is the result of that configuration item regardless of how many pending or certified states it has.  If a configuration item has only pending and certified elements, that configuration item has a result of pending.  If all elements of a configuration item is certified, it has a result of certified.  How do I accomplish this?

var cs = new GlideAggregate('cert_element');
var resultString = '';
var count = 0;

cs.addAggregate('COUNT', 'configuration_item');
cs.addQuery('sys_created_on', '>=', '2022-12-05');
cs.orderBy('configuration_item');
cs.orderBy('state');
cs.groupBy('configuration_item');
cs.query();

while(cs.next()){
  resultString += ('Audit: ' +cs.audit.name+ '\tState: ' +cs.state+ '\tConfiguration Item: ' +cs.configuration_item.name);
  resultString += '\n';
  count++;
}

gs.print(count);
gs.print(resultString);

I'm open to doing this another way.  In the end, I want to be able to tell which ci's passed or failed certification.  Certification is based on elements but I want to look at it as a whole.  Failed, Pending, Certified is the order of precedence. If one element of a ci is failed, the entire ci is failed.  If none are failed but one is pending, the entire ci is pending.  If all are certified, they entire ci is certified.  This is the report I need.

I would like to have this in a pie chart but I would also like to do trending on this so I can see over time if certain cis continually fail or how long it takes for a ci to complete certification.

I would also like to modify a field (Certification Status) on the configuration item to 'Failed|Pending|Certified' and the date and time (Cert Status Date) that value changed.

[Josh McDonald]

I was looking for the same.  Any update on this?

[Robert Campbell · Accepted solution]

I've wrote this script:

function getCertStatus(tablename, certInstanceNumber) {
  var ce = new GlideRecord(tablename); // Identify the table to work with

  ce.addQuery('cert_task.cert_instance.number',certInstanceNumber);
  ce.orderBy('configuration_item');
  ce.orderBy('state');
  ce.groupBy('configuration_item');
  ce.query();

  const failedci = [];    // Array to store failed configuration item names
  const pendingci = [];   // Array to store pending configuration item names
  const certifiedci = []; // Array to store certified configuration item names
  const pending = [];     // Array to store pending sys_ids
  const certified = [];   // Array to store certified sys_ids
  const cert = [];        // Array to store configuration item names
  const failedsi = [];    // Array to store failed sys_ids
  const pendingsi = [];   // Array to store pending sys_ids
  const psi = [];         // Array to store filtered pending sys_ids
  const certifiedsi = []; // Array to store certified sys_ids
  const certsi = [];      // Array to store filtered certified sys_ids
  const csi = [];         // Array to store certified sys_ids
  const results = [];     // Array to store the final results

  // Build the arrays

  while (ce.next()) {
    if (ce.state == 'Failed') {
      failedci.push(ce.configuration_item.name);    // Add the failed configuration item name to the array
      failedsi.push(ce.sys_id.toString());          // Add the corresponding sys_id to the array
    } else if (ce.state == 'Pending') {
      pendingci.push(ce.configuration_item.name);   // Add the pending configuration item name to the array
      pendingsi.push(ce.sys_id.toString());         // Add the corresponding sys_id to the array
    } else if (ce.state == 'Certified') {
      certifiedci.push(ce.configuration_item.name); // Add the certified configuration item name to the array
      certifiedsi.push(ce.sys_id.toString());       // Add the corresponding sys_id to the array
    }
  }

  // Compare and filter the arrays

  for (var i = 0; i < pendingci.length; i++) {
    if (failedci.indexOf(pendingci[i]) == -1) {      // Check if the pending configuration item is not failed
      pending.push(pendingci[i]);                    // Add the pending configuration item name to the array
      psi.push(pendingsi[i].toString());             // Add the corresponding sys_id to the array
    }
  }

  for (var i = 0; i < certifiedci.length; i++) {
    if (failedci.indexOf(certifiedci[i]) == -1) {    // Check if the certified configuration item is not failed
      cert.push(certifiedci[i]);                     // Add the certified configuration item name to the array
      certsi.push(certifiedsi[i].toString());        // Add the corresponding sys_id to the array
    }
  }

  for (var i = 0; i < cert.length; i++) {
    if (pending.indexOf(cert[i]) == -1) {            // Check if the certified configuration item is not pending
      certified.push(cert[i]);                       // Add the certified configuration item name to the array
      csi.push(certsi[i].toString());                // Add the corresponding sys_id to the array
    }
  }

  results.push(failedsi.toString());                 // Push the failed sys_ids to the results array
  results.push(psi.toString());                      // Push the filtered pending sys_ids to the results array
  results.push(csi.toString());                      // Push the filtered certified sys_ids to the results array

  // Additional handling for cert_instance.number
  if (certInstanceNumber) {
    results.push(certInstanceNumber);                // Push the provided cert_instance.number to the results array
  }

  return results;                                    // Return the final results array
};

In the report use it like this:

Without using the script, you end up with similar numbers except you end up with at least 5 more because it gets double counted.

[Josh McDonald]

Wow Thanks!!