CMDB Health Dashboard: Scripted Audits to Identify Orphan CIs

[JinkaH]

Could you please provide assistance for this to identify orphan CI 
Where all CI status is installed and CI type is not empty and ownedby.active=false.
All the CIS which match the above filter condition and where Owned By.Active=False and Owned By.Dormant=False and OwnedBy.Deactivation Date Is not Empty should fail the certification and  be marked as non-compliant CIs.
The audit should be run on daily basis and should executed @3AM CET

No Task creation required

the CIs which are in status installed are owned by user which is Active =False and Dormant=False are marked as non-compliant and as report as non-compliant CIs in the Compliance metrics of the CMDB Health Dashboard and as well each of the failed CIs must be available in cert_audit_result and the cmdb_health_result table bydefault as part of the standard Service Now process.


Please provide the assistance related to this requirement to design the solution.

[JinkaH]

Could you please provide assistance 

[abirakundu23]

Hi @JinkaH ,

Please review above solution can be achieve by OOB or not before creating custom solution.
You can create custom certificate rule and execute schedule job at 3AM CET. After that it will reflect in CMDB Health dashborad.

Please follow below product documents:

https://www.servicenow.com/docs/r/servicenow-platform/configuration-management-database-cmdb/c_Scrip...

https://www.servicenow.com/docs/r/yokohama/application-portfolio-management/run-desired-and-scripted...

Community threads :
https://www.servicenow.com/community/cmdb-forum/report-to-find-orphaned-cis-without-upstream-relatio...

Please mark helpful & correct answer if it's worthy for you.

[Vikram Reddy]

Hi @JinkaH,

This is exactly what Desired State Scripted Audits under Compliance > Desired State > Scripted Audits are built for, it's the only audit path that writes to cert_audit_result and rolls up into cmdb_health_result automatically, which is what you're asking for. One thing worth flagging up front: this is not the same as the native Orphan CI rule in CI Class Manager (Health > Correctness), that rule is relationship or attribute based (missing owner, no relationships to a parent class), not "owner is inactive and dormant." You need a custom scripted audit, not an orphan rule.

Here's the build, end to end:

1. Create the filter first: go to Data Certification > Filters, new record, table cmdb_ci, condition install_status = Installed AND sys_class_name is not empty AND owned_by.active = false. This is your candidate population, broader than your fail condition on purpose.
2. Create the audit: Compliance > Desired State > Scripted Audits, new record. Set Type to Scripted, Table to cmdb_ci, and Filter to the one you just built. Leave the Follow-on Task Template field empty since you don't want tasks created.
3. Write the script: use the CertificationProcessing API to pull the filtered records and log a pass or fail per CI. Put this in the Script field:

new SNC.CertificationProcessing().updateLastRunDate(current.sys_id);

var candidates = new SNC.CertificationProcessing().getFilterRecords(current.filter);
while (candidates.next()) {
    var ciId = candidates.getValue('sys_id');
    var ownerActive = candidates.owned_by.active.toString() == 'true';
    var ownerDormant = candidates.owned_by.dormant.toString() == 'true';
    var deactivationDate = candidates.owned_by.getValue('u_deactivation_date');

    if (!ownerActive && !ownerDormant && deactivationDate) {
        new SNC.CertificationProcessing().logAuditResultFail(
            current.sys_id,
            ciId,
            '',
            'owned_by',
            'is not',
            'Active, non-dormant owner',
            'Inactive owner with deactivation date set',
            true
        );
    } else {
        new SNC.CertificationProcessing().logAuditResultPass(current.sys_id, ciId, true);
    }
}

Two things to check before you trust this on your instance:

• updateLastRunDate has to run first, before the filtering logic. Skip it and the audit's Last run date never populates, and CMDB Health silently ignores the run, it won't show in Compliance metrics at all even though cert_audit_result gets rows.
• Dormant and Deactivation Date are not guaranteed stock fields on sys_user in every instance, Dormant in particular is often tied to license/inactivity monitoring add-ons and Deactivation Date is commonly a custom field like u_deactivation_date. Confirm the real field names in your dictionary before you ship this, a typo here just means the CI silently passes every time.

For the schedule, set the audit's run time and double check what timezone it actually resolves against, some instances run scheduled jobs against the system default timezone rather than the user's, so "3AM" in the schedule field might not be 3AM CET unless the instance default is set to CET. Confirm this on your instance before you lock the job in.

References

• Scripted Audits and CMDB Dashboard (Community, confirms updateLastRunDate requirement)
• Scripted Audit and Follow On Tasks (Community, CertificationProcessing script example)
• Certification audit results (cert_audit_result)
• CMDB Orphan CIs Correctness Score (Community)

Thank you,
Vikram Karety
Octigo Solutions INC

[Tanushree Maiti]

Hi @JinkaH 

To automate this, create a CMDB Scripted Audit paired with a Scheduled Job pushes results to cert_audit_result and cmdb_health_result.

Use the following strategy to map this into ServiceNow without triggering task creation:

1. Create Scripted Audit Definition

Navigate to Configuration > CMDB Dashboard > CMDB Audits >click New, and create a Scripted Audit.

(function evaluateAudit(auditResult) {

    var gr = new GlideRecord('cmdb_ci');

    gr.addQuery('install_status', 1); // 1 = Installed

    gr.addNotNullQuery('sys_class_name');

    gr.query();   

    while (gr.next()) {

        var ownerUser = new GlideRecord('sys_user');

        if (ownerUser.get(gr.owned_by)) {

            if (ownerUser.active == false && ownerUser.u_dormant == false && !ownerUser.u_deactivation_date.nil()) {

                 var healthResult = new GlideRecord('cmdb_health_result');

                healthResult.initialize();

                healthResult.ci = gr.sys_id;

                healthResult.audit = auditResult.sys_id;

                healthResult.score = 0;

                healthResult.status = 'non_compliant';

                healthResult.metric_name = 'Compliance';

                healthResult.insert();

                var certResult = new GlideRecord('cert_audit_result');

                certResult.initialize();

                certResult.audit = auditResult.sys_id;

                certResult.audit_result = 'failed';

                certResult.ci = gr.sys_id;

                certResult.task = ''; // Empty string ensures No Task Creation

                certResult.insert();

            }

        }

    }

})(auditResult);

1. Scheduled Job Setup

Navigate to System Definition > Scheduled Jobs and create

• Run: Daily
• Time: 03:00:00 (Convert 3 AM CET to your instance's System/UTC time zone).
• Run this script:

var auditSysID = 'YOUR_Scripted_AUDIT_SYS_ID_HERE';

var auditRec = new GlideRecord('cert_audit');

if (auditRec.get(auditSysID)) {

    SNC.CertificationAuditHandler.executeAudit(auditRec);

}

1. CMDB Health Dashboard Metric

Ensure your CMDB Health Properties are tracking the Compliance scorecard.

Since you are inserting non-compliant states directly into cmdb_health_result tied to the CI, it will evaluate on the dashboard accordingly.

Refer: Scripted audits