Functionality to update the CI record with a Last Certification Date when the Data Certification task is completed

[melissahill]

Hi all,

I am hoping for this awesome community to be able to help me with the following business requirement.

Right now, we are only running one data certification, and it is against the Business Application table.  I could see this expanding in the future, though.

My need is that when the Data Certification task is completed, the business applications the task was created for (Document, or id) are updated with a Last Certified Date.  Here are my thoughts so far:

- The Last Certified Date is a new field on the cmdb_ci table, so this will work with any certification

- I will probably need to add a field to the certification template that indicates if the tasks created from that template should update the last certified date.  At this point, since I only have one, I am going to bypass that complexity and build it another day.

- There is no table that simply holds the records being certified and their status.  I have to go straight from task to elements.  Therefore, my logic is that when the task is closed, the following should happen:

1. I need the unique ids within that task
2. I need to know that all elements within that id have a status of Certified
3. When that is true, then i need to update the target ID record with the current date as the Last Certified date

I am getting hung up on the multiple rows for one business application in the elements table, and can't figure out the syntax of a nested glide query, and I not sure that is the best approach.  I have also thought about using an array to hold the output, but I am not sure exactly what to output and then how to use the array as input to a glide query.

All of your help and guidance would be greatly appreciated!

Thanks,

Melissa

[melissahill]

I worked through the code and developed my own solution.  The code is below for anyone who might want to take the same approach.

Business Rule running after update

Against table cert_task

Filter Condition =State Changes to Closed Complete

OR State Changes to Closed Incomplete

(function executeRule(current, previous /*null when async*/) {

// Add your code here
var arrayID = [];

var certDocs = new GlideAggregate('cert_element');
certDocs.addQuery('cert_task',current.sys_id);
//certDocs.groupBy('id');
certDocs.addAggregate('COUNT','id');
certDocs.query();

while (certDocs.next()) {
var id = certDocs.id;
var count = certDocs.getAggregate('COUNT','id');

//gs.log("EMN - Count: "+count+" ID: "+id);

var dataElement = new GlideAggregate('cert_element');
dataElement.addQuery('id',id);
dataElement.addQuery('state','certified');
dataElement.addAggregate('COUNT','id');
dataElement.query();

while (dataElement.next()){
var certCount = dataElement.getAggregate('COUNT','id');
//gs.log("EMN Count: "+count+ " CertCount: "+certCount+ " ID: "+id);
if (count==certCount){
//gs.log("EMN Count and Cert Count Matches for ID: "+id);

var updateCI = new GlideRecord('cmdb_ci');
updateCI.addQuery('sys_id',id);
updateCI.query();

while(updateCI.next()) {
updateCI.u_last_certified = gs.nowDateTime(); //New field created on the cmdb_ci table
updateCI.update();
}


}
else {
//gs.log("EMN Count and Cert Count Does not Match for ID: "+id);
}
}

}


})(current, previous);

[Community Alums]

Melissa, 

Thanks. I had the need to do exactly what you described. I did find that the code did not work on my system until I added dataElement.addQuery('cert_task', current.sys_id); to the second Glide Aggregate query. Not sure if this is something unique to my environment. 

Here's my code (which is mostly yours) with the added statement.

Thanks again for posting.

(function executeRule(current, previous /*null when async*/) {

//Code from Community
//https://community.servicenow.com/community?id=community_question&sys_id=c6e8585ddb34ffc454250b55ca961937&view_source=searchResult

var arrayID = [];

var certDocs = new GlideAggregate('cert_element');
certDocs.addQuery('cert_task',current.sys_id);
//certDocs.groupBy('id');
certDocs.addAggregate('COUNT','id');
certDocs.query();

while (certDocs.next()) {
var id = certDocs.id;
var count = certDocs.getAggregate('COUNT','id');

//gs.log("EMN - Count: "+count+" ID: "+id);

var dataElement = new GlideAggregate('cert_element');
dataElement.addQuery('id',id);
dataElement.addQuery('state','certified');
dataElement.addQuery('cert_task', current.sys_id);
dataElement.addAggregate('COUNT','id');
dataElement.query();

while (dataElement.next()){
var certCount = dataElement.getAggregate('COUNT','id');
//gs.log("EMN Count: "+count+ " CertCount: "+certCount+ " ID: "+id);
if (count==certCount){
//gs.log("EMN Count and Cert Count Matches for ID: "+id);

var updateCI = new GlideRecord('cmdb_ci');
updateCI.addQuery('sys_id',id);
updateCI.query();

while(updateCI.next()) {
//gs.log('EMN UpdateCI ' + updateCI.name);
updateCI.u_validation_date_complete = gs.nowDateTime(); //New field created on the cmdb_ci table
updateCI.update();
}

}
else {
//gs.log("EMN Count and Cert Count Does not Match for ID: "+id);
}
}
}

})(current, previous);

[adamtoth]

Have there been any updates to ServiceNow that allow a simpler process to add Certification Date and Name of certifier to the CI?