Checking Group Dependency before deactivating group.

[Vijay Pawar3]

Hello All,
        Many times we have requirement to deactivate a group as part of operational activists, after deactivating groups sometimes we get incidents about something not working as expected because deactivated groups was hard-coded in some script, system property or workflow.
        I have created a utility which provides information about all the places where group is used on the instance, all you need to do is select a group and click "Get Results". After group is selected Group dependency results are displayed which shows where group is used either as group name or sys id. Using this information you can take appropriate action before deactivating group.

How to use :
A) You can import attached update set "Group Dependency Check_v1" and commit, it will create a table named Group Dependency. using this table you can check dependency.
    1) Navigate Group Dependency > Create New.

    2) Select Group and click on Get Results.

    3) Results will be generated as shown below.

    4) You can Change Fix the dependency and click on "Update Results" to get dependency again.

B) I have attached a Script include "GroupDependencyUtilsNormal"  which is brain behind the utility, you can use it with your own setup. It can be used with business rule, client script if you are using it on catalog item/record producer.

Hope this helps !

Here is the script :

var GroupDependencyUtilsNormal = Class.create();
GroupDependencyUtilsNormal.prototype = {
    initialize: function() {},

    getGroupDependency: function(groupName, groupSys_ID) {
        var results = 'Group Dependency for group name: ' + groupName + ' - group sys_id: ' + groupSys_ID + '\n';
        var queryString = '';
        //Active Tasks
        results += this.checkActiveTasks(groupSys_ID, groupName);
        //Email NOtifications
        results += this.checkEmailNotifications(groupSys_ID, groupName);
        //Inbound Email Actions
        results += this.checkInboundActions(groupSys_ID, groupName);
        //System Properties
        results += this.checkSystemProperties(groupSys_ID, groupName);
        //Workflow Fulfillment
        results += this.checkWorkflowVariables(groupSys_ID, groupName);
        //WorkFlow Scripts
        results += this.checkWorkflowScripts(groupSys_ID, groupName);
        //Business Service
        results += this.checkBusinessServices(groupSys_ID, groupName);
        // Catalog Items
        results += this.checkCatalogItems(groupSys_ID, groupName);
        //Business Rules
        results += this.checkBusinessRules(groupSys_ID, groupName);
        //Client Scripts
        results += this.checkClientScripts(groupSys_ID, groupName);
        //Scheduled Jobs
        results += this.checkScheduledJobs(groupSys_ID, groupName);
        //UI Action
        results += this.checkUIActions(groupSys_ID, groupName);
        //ACL
        results += this.checkACL(groupSys_ID, groupName);
        //UI Policy
        results += this.checkUIPolicies(groupSys_ID, groupName);
        //Record Producer
        results += this.checkRecordProducers(groupSys_ID, groupName);
        // Script Includes
        results += this.checkScriptIncludes(groupSys_ID, groupName);
        //Email Scripts
        results += this.checkEmailScripts(groupSys_ID, groupName);

        return results;

    },

    // Check Active tasks
    checkActiveTasks: function(groupSys_ID, groupName) {
        var results = "";
        queryString = 'active=true^assignment_group=' + groupSys_ID;
        var count = new GlideAggregate('task');
        count.addEncodedQuery(queryString);
        count.addAggregate('COUNT');
        count.query();
        var tasks = 0;
        if (count.next())
            tasks = count.getAggregate('COUNT');
        results += "Number of Active tasks assigned to group " + groupName + ": " + tasks + '\n';

        return results;
    },

    //check email notifications
    checkEmailNotifications: function(groupSys_ID, groupName) {
        var results = "";
        queryString = 'active=true^recipient_groups=' + groupSys_ID + '^ORconditionLIKE' + groupSys_ID + '^ORadvanced_conditionLIKE' + groupSys_ID + '^ORadvanced_conditionLIKE' + groupName;
        var gAgg = new GlideAggregate('sysevent_email_action');
        gAgg.addEncodedQuery(queryString);
        gAgg.addAggregate('COUNT');
        gAgg.query();
        var count = 0;
        if (gAgg.next()) {
            results += 'Group used in Email Notifications : ' + gAgg.getAggregate('COUNT') + '\n';
            count = gAgg.getAggregate('COUNT');
            if (count > 0) {
                var emailAction = new GlideRecord('sysevent_email_action');
                emailAction.addEncodedQuery(queryString);
                emailAction.query();

                while (emailAction.next()) {
                    results += '--> Group needs to be removed from notification: ' + emailAction.name + ' - sys_id: ' + emailAction.sys_id + '\n';
                }
            }
        }
        return results;
    },

    //Check Inbound email Actions
    checkInboundActions: function(groupSys_ID, groupName) {
        var results = "";
        queryString = 'active=true^scriptLIKE' + groupSys_ID + '^ORscriptLIKE' + groupName;
        var gAgg = new GlideAggregate('sysevent_in_email_action');
        gAgg.addEncodedQuery(queryString);
        gAgg.addAggregate('COUNT');
        gAgg.query();
        var count = 0;
        if (gAgg.next()) {
            results += 'Group used in Inbound Email Action : ' + gAgg.getAggregate('COUNT') + '\n';
            count = gAgg.getAggregate('COUNT');
            if (count > 0) {
                var inboundEmailAction = new GlideRecord('sysevent_in_email_action');
                inboundEmailAction.addEncodedQuery(queryString);
                inboundEmailAction.query();

                while (inboundEmailAction.next()) {
                    results += '--> Group needs to be removed from Inbound Action: ' + inboundEmailAction.name + ' - sys_id: ' + inboundEmailAction.sys_id + '\n';
                }
            }
        }

        return results;
    },

    //Check system properties
    checkSystemProperties: function(groupSys_ID, groupName) {
        var results = "";
        queryString = 'valueLIKE' + groupSys_ID + '^ORvalueLIKE' + groupName;
        var gAgg = new GlideAggregate('sys_properties');
        gAgg.addEncodedQuery(queryString);
        gAgg.addAggregate('COUNT');
        gAgg.query();
        var count = 0;
        if (gAgg.next()) {
            results += ' Group used in System Properties : ' + gAgg.getAggregate('COUNT') + '\n';
            count = gAgg.getAggregate('COUNT');
            if (count > 0) {
                var systemProperties = new GlideRecord('sys_properties');
                systemProperties.addEncodedQuery(queryString);
                systemProperties.query();
                while (systemProperties.next()) {
                    results += '--> Group needs to be removed from System Properties: ' + systemProperties.name + ' - sys_id: ' + systemProperties.sys_id + '\n';
                }
            }
        }
        return results;
    },

    //check workflow fulfillment variables
    checkWorkflowVariables: function(groupSys_ID, groupName) {
        var results = "";
        //check workflow fulfillment variable
        queryString = 'document=wf_activity^value=' + groupSys_ID + '^ORvalueSTARTSWITH' + groupName + '^ORDERBYorder';
        var gAgg = new GlideAggregate('sys_variable_value');
        gAgg.addEncodedQuery(queryString);
        gAgg.addAggregate('COUNT');
        gAgg.query();
        var count = 0;
        if (gAgg.next()) {

            results += ' Group used in Workflow Variables(sys_variable_value), number contains both Active and Inactive workflows but detail result is for only active workflows : ' + gAgg.getAggregate('COUNT') + '\n';
            count = gAgg.getAggregate('COUNT');
            if (count > 0) {
                var workflowVariable = new GlideRecord('sys_variable_value');
                workflowVariable.addEncodedQuery(queryString);
                workflowVariable.query();
                var workflowActivityVariable = new GlideRecord('wf_activity');
                while (workflowVariable.next()) {
                    if (workflowActivityVariable.get(workflowVariable.document_key))
                        if (workflowActivityVariable.workflow_version.published == true)
                            results += '--> For Workflow: ' + workflowActivityVariable.workflow_version.name + ', activity needs group change: ' + workflowVariable.document_key.name + ' - variable: ' + workflowVariable.variable.label + ' - sys_id: ' + workflowVariable.sys_id + '\n';
                }
            }
        }
        return results;
    },
    //Check Workflow Scripts
    checkWorkflowScripts: function(groupSys_ID, groupName) {
        var results = "";
        //check workflow script
        queryString = 'document=wf_activity^variableLIKEscript^ORvariable.internal_type.nameLIKEscript^valueLIKE' + groupName + '^ORvalueLIKE' + groupSys_ID + '^ORDERBYorder';
        var gAgg = new GlideAggregate('sys_variable_value');
        gAgg.addEncodedQuery(queryString);
        gAgg.addAggregate('COUNT');
        gAgg.query();
        var count = 0;
        if (gAgg.next()) {
            results += 'Group used in Workflow script,number contains both Active and Inactive workflows but detail result is for only active workflows :  ' + gAgg.getAggregate('COUNT') + '\n';
            count = gAgg.getAggregate('COUNT');

            if (count > 0) {
                var workflowScript = new GlideRecord('sys_variable_value');
                workflowScript.addEncodedQuery(queryString);
                workflowScript.query();

                var workflowActivityScript = new GlideRecord('wf_activity');
                while (workflowScript.next()) {
                    if (workflowActivityScript.get(workflowScript.document_key))
                        if (workflowActivityScript.workflow_version.published == true)
                            results += '--> For Workflow: ' + workflowActivityScript.workflow_version.name + ', script activity needs group change: ' + workflowScript.document_key.name + ' - sys_id: ' + workflowScript.sys_id + '\n';
                }
            }
        }
        return results;
    },

    //Check Business Services
    checkBusinessServices: function(groupSys_ID, groupName) {
        var results = "";
        //check business services
        queryString = "change_control=" + groupSys_ID + "^ORassignment_group=" + groupSys_ID + "^ORsupport_group=" + groupSys_ID + "^ORuser_group=" + groupSys_ID;
        var gAgg = new GlideAggregate('cmdb_ci_service');
        gAgg.addEncodedQuery(queryString);
        gAgg.addAggregate('COUNT');
        gAgg.query();
        var count = 0;
        if (gAgg.next()) {
            results += 'Group used in Business Services : ' + gAgg.getAggregate('COUNT') + '\n';
            count = gAgg.getAggregate('COUNT');

            if (count > 0) {
                var serviceRec = new GlideRecord('cmdb_ci_service');
                serviceRec.addEncodedQuery(queryString);
                serviceRec.query();

                while (serviceRec.next()) {
                    results += '--> Business service needs group change: ' + serviceRec.name + '\n';
                }
            }
        }
        return results;
    },

    //CHeck Catalog Items
    checkCatalogItems: function(groupSys_ID, groupName) {
        var results = "";
        queryString = 'group=' + groupSys_ID;
        var gAgg = new GlideAggregate('sc_cat_item');
        gAgg.addEncodedQuery(queryString);
        gAgg.addAggregate('COUNT');
        gAgg.query();
        var count = 0;
        if (gAgg.next()) {
            results += 'Group used in Catalog Items : ' + gAgg.getAggregate('COUNT') + '\n';
            count = gAgg.getAggregate('COUNT');

            if (count > 0) {
                var catalogItem = new GlideRecord('sc_cat_item');
                catalogItem.addEncodedQuery(queryString);
                catalogItem.query();

                while (catalogItem.next()) {
                    results += '--> Catalog item needs group change: ' + catalogItem.name + ' - sys_id: ' + catalogItem.sys_id + '\n';
                }
            }
        }
        return results;
    },

    //Check Business Rules
    checkBusinessRules: function(groupSys_ID, groupName) {
        var results = "";
        queryString = 'scriptLIKE' + groupName + '^ORscriptLIKE' + groupSys_ID;
        var gAgg = new GlideAggregate('sys_script');
        gAgg.addEncodedQuery(queryString);
        gAgg.addAggregate('COUNT');
        gAgg.query();
        var count = 0;
        if (gAgg.next()) {
            results += 'Group used in Bsuiness Rules : ' + gAgg.getAggregate('COUNT') + '\n';
            count = gAgg.getAggregate('COUNT');

            if (count > 0) {
                var businessRule = new GlideRecord('sys_script');
                businessRule.addEncodedQuery(queryString);
                businessRule.query();

                while (businessRule.next()) {
                    results += '--> Business rule needs group change: ' + businessRule.name + ' - sys_id: ' + businessRule.sys_id + '\n';
                }
            }
        }
        return results;
    },

    //Check Client Scripts
    checkClientScripts: function(groupSys_ID, groupName) {
        var results = "";
        //check client scripts
        queryString = 'scriptLIKE' + groupName + '^ORscriptLIKE' + groupSys_ID;
        var gAgg = new GlideAggregate('sys_script_client');
        gAgg.addEncodedQuery(queryString);
        gAgg.addAggregate('COUNT');
        gAgg.query();
        var count = 0;
        if (gAgg.next()) {
            results += 'Group used in Client Scripts : ' + gAgg.getAggregate('COUNT') + '\n';
            count = gAgg.getAggregate('COUNT');

            if (count > 0) {
                var clientScript = new GlideRecord('sys_script_client');
                clientScript.addEncodedQuery(queryString);
                clientScript.query();

                while (clientScript.next()) {
                    results += '--> Client script needs group change: ' + clientScript.name + ' - sys_id: ' + clientScript.sys_id + '\n';
                }
            }
        }
        return results;
    },

    //Scheduled Job Scripts
    checkScheduledJobs: function(groupSys_ID, groupName) {
        var results = "";
        //scheduled job scripts
        queryString = 'scriptLIKE' + groupName + '^ORscriptLIKE' + groupSys_ID;
        var gAgg = new GlideAggregate('sysauto_script');
        gAgg.addEncodedQuery(queryString);
        gAgg.addAggregate('COUNT');
        gAgg.query();
        var count = 0;
        if (gAgg.next()) {
            results += 'Group used in Scheduled Jobs : ' + gAgg.getAggregate('COUNT') + '\n';
            count = gAgg.getAggregate('COUNT');

            if (count > 0) {
                var scheduledJobScript = new GlideRecord('sysauto_script');
                scheduledJobScript.addEncodedQuery(queryString);
                scheduledJobScript.query();

                while (scheduledJobScript.next()) {
                    results += '--> Client script needs group change: ' + scheduledJobScript.name + ' - sys_id: ' + scheduledJobScript.sys_id + '\n';
                }
            }
        }
        return results;
    },

    //Check UI Actions
    checkUIActions: function(groupSys_ID, groupName) {
        var results = "";
        //ui action scripts
        queryString = 'scriptLIKE' + groupName + '^ORscriptLIKE' + groupSys_ID;
        var gAgg = new GlideAggregate('sys_ui_action');
        gAgg.addEncodedQuery(queryString);
        gAgg.addAggregate('COUNT');
        gAgg.query();
        var count = 0;
        if (gAgg.next()) {

            results += 'Group used in UI Actions : ' + gAgg.getAggregate('COUNT') + '\n';
            count = gAgg.getAggregate('COUNT');

            if (count > 0) {
                var uiAction = new GlideRecord('sys_ui_action');
                uiAction.addEncodedQuery(queryString);
                uiAction.query();

                while (uiAction.next()) {
                    results += '--> Client script needs group change: ' + uiAction.name + ' - sys_id: ' + uiAction.sys_id + '\n';
                }
            }
        }
        return results;
    },

    //Check ACL 
    checkACL: function(groupSys_ID, groupName) {
        var results = "";
        queryString = 'scriptLIKE' + groupName + '^ORscriptLIKE' + groupSys_ID;
        var gAgg = new GlideAggregate('sys_security_acl');
        gAgg.addEncodedQuery(queryString);
        gAgg.addAggregate('COUNT');
        gAgg.query();
        var count = 0;
        if (gAgg.next()) {

            results += 'Group used in ACLs : ' + gAgg.getAggregate('COUNT') + '\n';
            count = gAgg.getAggregate('COUNT');

            if (count > 0) {
                var aclScript = new GlideRecord('sys_security_acl');
                aclScript.addEncodedQuery(queryString);
                aclScript.query();

                while (aclScript.next()) {
                    results += '--> ACL group change: ' + aclScript.name + ' - sys_id: ' + aclScript.sys_id + '\n';
                }
            }
        }

        return results;
    },
    //Check UI Policies
    checkUIPolicies: function(groupSys_ID, groupName) {
        var results = "";
        queryString = 'script_falseLIKE' + groupName + '^ORscript_falseLIKE' + groupSys_ID + '^ORscript_trueLIKE' + groupName + '^ORscript_trueLIKE' + groupSys_ID;
        var gAgg = new GlideAggregate('sys_ui_policy');
        gAgg.addEncodedQuery(queryString);
        gAgg.addAggregate('COUNT');
        gAgg.query();
        var count = 0;
        if (gAgg.next()) {

            results += 'Group used in UI Policies : ' + gAgg.getAggregate('COUNT') + '\n';
            count = gAgg.getAggregate('COUNT');

            if (count > 0) {
                var uiPolicy = new GlideRecord('sys_ui_policy');
                uiPolicy.addEncodedQuery(queryString);
                uiPolicy.query();

                while (uiPolicy.next()) {
                    results += '--> UI policy group change: ' + uiPolicy.name + ' - sys_id: ' + uiPolicy.sys_id + '\n';
                }
            }
        }
        return results;
    },

    //Check Record Producers
    checkRecordProducers: function(groupSys_ID, groupName) {
        var results = "";
        queryString = 'scriptLIKE' + groupName + '^ORscriptLIKE' + groupSys_ID;
        var gAgg = new GlideAggregate('sc_cat_item_producer');
        gAgg.addEncodedQuery(queryString);
        gAgg.addAggregate('COUNT');
        gAgg.query();
        var count = 0;
        if (gAgg.next()) {

            results += 'Group used in Record Producers : ' + gAgg.getAggregate('COUNT') + '\n';
            count = gAgg.getAggregate('COUNT');

            if (count > 0) {
                var recordProducer = new GlideRecord('sc_cat_item_producer');
                recordProducer.addEncodedQuery(queryString);
                recordProducer.query();

                while (recordProducer.next()) {
                    results += '--> Record producer group change: ' + recordProducer.name + ' - sys_id: ' + recordProducer.sys_id + '\n';
                }
            }
        }
        return results;
    },

    //Check Script Incudes
    checkScriptIncludes: function(groupSys_ID, groupName) {
        var results = "";
        queryString = 'scriptLIKE' + groupName + '^ORscriptLIKE' + groupSys_ID;
        var gAgg = new GlideAggregate('sys_script_include');
        gAgg.addEncodedQuery(queryString);
        gAgg.addAggregate('COUNT');
        gAgg.query();
        var count = 0;
        if (gAgg.next()) {

            results += 'Group used in Script Includes : ' + gAgg.getAggregate('COUNT') + '\n';
            count = gAgg.getAggregate('COUNT');

            if (count > 0) {
                var scriptInclude = new GlideRecord('sys_script_include');
                scriptInclude.addEncodedQuery(queryString);
                scriptInclude.query();

                while (scriptInclude.next()) {
                    results += '--> Script include group change: ' + scriptInclude.name + ' - sys_id: ' + scriptInclude.sys_id + '\n';
                }
            }
        }
        return results;

    },

    //Check email Scripts
    checkEmailScripts: function(groupSys_ID, groupName) {
        var results = "";
        queryString = 'scriptLIKE' + groupName + '^ORscriptLIKE' + groupSys_ID;
        var gAgg = new GlideAggregate('sys_script_email');
        gAgg.addEncodedQuery(queryString);
        gAgg.addAggregate('COUNT');
        gAgg.query();
        var count = 0;
        if (gAgg.next()) {

            results += 'Group used in Email Scripts : ' + gAgg.getAggregate('COUNT') + '\n';
            count = gAgg.getAggregate('COUNT');

            if (count > 0) {
                var mailScript = new GlideRecord('sys_script_email');
                mailScript.addEncodedQuery(queryString);
                mailScript.query();

                while (mailScript.next()) {
                    results += '--> Mail script group change: ' + mailScript.name + ' - sys_id: ' + mailScript.sys_id + '\n';
                }
            }
        }
        return results;
    },

    type: 'GroupDependencyUtilsNormal'
};

[Community Alums]

Hi Vijay Pawar

The UpdateSet Group Dependency Check_v1 is empty, it does not contain any custom updates at all, can you please recheck and upload the correct one, this is a really helpful feature

[Vijay Pawar3]

Done.

Thanks,

Vijay.

[Community Alums]

thank you for quick fix

[mcconnellsj]

Nice work!  I just called the script from a UI Action on the group form and displayed the result in an info message to get quick overview.  Have to keep an eye on the custom tables count!

[Rob96]

@mcconnellsj looking to do the same thing. What do I need to add to get the information as an info message?

[mcconnellsj]

@Rob96The UI Action would call the script include function then place in the message, something like:

var theResult = (new GroupUtils().getGroupDependency(current.name,current.sys_id));
gs.addInfoMessage('Result: '+theResult);
action.setRedirectURL(current);

[Rob96]

@mcconnellsj thanks that did the job!

[Luis Austin]

Is there a way to have this tool also check flows and subflows?

[showkaths]

Great work. Very helpful. 

[Bradley Ross]

I love this function. I also wanted this to help me see where a group is being used in new Flows. There are a zillion tables that are used for flows and some of the contents are compressed and encoded which makes them difficult to search. With help from an undocumented API, I was able to vibe-code an update to this script that serves my other needs. 

I put a UI action in the related links section of my group form. That UI action has the following script.  You can probably think of several improvements already. This doesn't fit very nicely in an info message, so a custom UI page to display this would be a good next step. 

var theResult = new GroupDependencyUtilsNormal().getGroupDependency(current.name, current.sys_id);
var formatted = '<pre style="white-space:pre-wrap">Result:\n' + theResult + '</pre>';
gs.addInfoMessage(formatted);
action.setRedirectURL(current);

This monster script include is my updated version of what @Vijay Pawar3 provided above.

var GroupDependencyUtilsNormal = Class.create();
GroupDependencyUtilsNormal.prototype = Object.extendsObject(AbstractAjaxProcessor, {

  // Utility to escape HTML special characters
  escapeHTML: function(str) {
    if (!str) return '';
    return String(str)
      .replace(/&/g, '&')
      .replace(/</g, '&lt;')
      .replace(/>/g, '&gt;')
      .replace(/"/g, '&quot;')
      .replace(/'/g, '&#39;');
  },

  // Utility to build a ServiceNow record link
  buildLink: function(table, sysId, label) {
    return '<a href="/nav_to.do?uri=' + table + '.do?sys_id=' + sysId + '" target="_blank">' + this.escapeHTML(label) + '</a>';
  },

  getGroupDependency: function(groupName, groupSys_ID) {
    try {
      var html = [];
      html.push('<div class="dependency-report" style="font-family:Arial,sans-serif;">');
      html.push('<h2>Dependency Report for "' + this.escapeHTML(groupName) + '"</h2>');
      html.push('<p><em>Group sys_id: ' + this.escapeHTML(groupSys_ID) + '</em></p>');

      var sections = [
        { title: 'Active Tasks', items: this.checkActiveTasks(groupSys_ID, groupName), columns: ['Description'] },
        { title: 'Email Notifications', items: this.checkEmailNotifications(groupSys_ID, groupName), columns: ['Notification', 'Link'] },
        { title: 'Inbound Email Actions', items: this.checkInboundActions(groupSys_ID, groupName), columns: ['Inbound Action', 'Link'] },
        { title: 'System Properties', items: this.checkSystemProperties(groupSys_ID, groupName), columns: ['Property Name', 'Link'] },
        { title: 'Business Services', items: this.checkBusinessServices(groupSys_ID, groupName), columns: ['Service Name', 'Life Cycle Stage', 'CMDB Class'] },
        { title: 'Other Configuration Items', items: this.checkOtherConfigurationItems(groupSys_ID, groupName), columns: ['CI Name', 'Life Cycle Stage', 'CMDB Class'] },
        {
  title: 'Flows Referencing Group',
  items: this.checkFlowsReferencingGroup(groupSys_ID, groupName),
  columns: ['Flow Name', 'Table', 'Field', 'Application Scope']
},
{ title: 'Catalog Items', items: this.checkCatalogItems(groupSys_ID, groupName), columns: ['Catalog Item', 'Link'] },
        { title: 'Business Rules', items: this.checkBusinessRules(groupSys_ID, groupName), columns: ['Business Rule', 'Link'] },
        { title: 'Client Scripts', items: this.checkClientScripts(groupSys_ID, groupName), columns: ['Client Script', 'Link'] },
        { title: 'Scheduled Jobs', items: this.checkScheduledJobs(groupSys_ID, groupName), columns: ['Scheduled Job', 'Link'] },
        { title: 'UI Actions', items: this.checkUIActions(groupSys_ID, groupName), columns: ['UI Action', 'Link'] },
        { title: 'ACLs', items: this.checkACL(groupSys_ID, groupName), columns: ['ACL Name', 'Link'] },
        { title: 'UI Policies', items: this.checkUIPolicies(groupSys_ID, groupName), columns: ['UI Policy', 'Link'] },
        { title: 'Record Producers', items: this.checkRecordProducers(groupSys_ID, groupName), columns: ['Record Producer', 'Link'] },
        { title: 'Script Includes', items: this.checkScriptIncludes(groupSys_ID, groupName), columns: ['Script Include', 'Link'] },
        { title: 'Email Scripts', items: this.checkEmailScripts(groupSys_ID, groupName), columns: ['Email Script', 'Link'] }
      ];

      for (var i = 0; i < sections.length; i++) {
        var sec = sections[i];
        if (sec.items && sec.items.length) {
          html.push('<h3>' + this.escapeHTML(sec.title) + '</h3>');
          html.push('<table border="1" cellpadding="4" cellspacing="0" style="border-collapse:collapse; width:100%;">');
          html.push('<thead><tr>');
          for (var c = 0; c < sec.columns.length; c++) {
            html.push('<th>' + this.escapeHTML(sec.columns[c]) + '</th>');
          }
          html.push('</tr></thead><tbody>');

          for (var j = 0; j < sec.items.length; j++) {
            var row = sec.items[j];
            html.push('<tr>');
            if (Array.isArray(row)) {
              for (var k = 0; k < row.length; k++) {
                html.push('<td>' + row[k] + '</td>');
              }
            } else {
              html.push('<td colspan="' + sec.columns.length + '">' + row + '</td>');
            }
            html.push('</tr>');
          }

          html.push('</tbody></table>');
        }
      }

      html.push('</div>');
      return html.join('');
    } catch (err) {
      return '<div style="color:red;"><strong>Error generating report:</strong> ' + this.escapeHTML(err.message) + '</div>';
    }
  },

  checkActiveTasks: function(id, name) {
    var ga = new GlideAggregate('task');
    ga.addQuery('active', true);
    ga.addQuery('assignment_group', id);
    ga.addAggregate('COUNT');
    ga.query();
    var count = ga.next() ? parseInt(ga.getAggregate('COUNT'), 10) : 0;
    return count > 0 ? [[this.escapeHTML(count + ' active task(s) assigned to ' + name)]] : [];
  },

  checkEmailNotifications: function(id, name) {
    var out = [];
    var gr = new GlideRecord('sysevent_email_action');
    gr.addEncodedQuery('active=true^recipient_groups=' + id + '^ORconditionLIKE' + id + '^ORadvanced_conditionLIKE' + id + '^ORadvanced_conditionLIKE' + name);
    gr.query();
    while (gr.next()) {
      out.push([
        this.escapeHTML(gr.name),
        this.buildLink('sysevent_email_action', gr.getUniqueValue(), 'Open')
      ]);
    }
    return out;
  },

  checkInboundActions: function(id, name) {
    var out = [];
    var gr = new GlideRecord('sysevent_in_email_action');
    gr.addEncodedQuery('active=true^scriptLIKE' + id + '^ORscriptLIKE' + name);
    gr.query();
    while (gr.next()) {
      out.push([
        this.escapeHTML(gr.name),
        this.buildLink('sysevent_in_email_action', gr.getUniqueValue(), 'Open')
      ]);
    }
    return out;
  },

  checkSystemProperties: function(id, name) {
    var out = [];
    var gr = new GlideRecord('sys_properties');
    gr.addEncodedQuery('valueLIKE' + id + '^ORvalueLIKE' + name);
    gr.query();
    while (gr.next()) {
      out.push([
        this.escapeHTML(gr.name),
        this.buildLink('sys_properties', gr.getUniqueValue(), 'Open')
      ]);
    }
    return out;
  },

  checkBusinessServices: function(id, name) {
    var out = [];
    var gr = new GlideRecord('cmdb_ci_service');
    gr.addEncodedQuery('assignment_group=' + id + '^ORsupport_group=' + id + '^ORuser_group=' + id + '^ORchange_control=' + id + '^ORmanaged_by_group=' + id);
    gr.query();
    while (gr.next()) {
      var serviceName = gr.name ? gr.name.toString() : '';
      var link = this.buildLink('cmdb_ci_service', gr.getUniqueValue(), serviceName);
      var lifeCycleStage = '';
      if (gr.life_cycle_stage) {
        lifeCycleStage = (typeof gr.life_cycle_stage.getDisplayValue === 'function')
          ? this.escapeHTML(gr.life_cycle_stage.getDisplayValue())
          : this.escapeHTML(gr.life_cycle_stage.toString());
      }
      var cmdbClass = gr.sys_class_name ? this.escapeHTML(gr.sys_class_name.toString()) : '';
      out.push([link, lifeCycleStage, cmdbClass]);
    }
    return out;
  },

  checkOtherConfigurationItems: function(id, name) {
    var out = [];
    var gr = new GlideRecord('cmdb_ci');
    var groupQuery =
      'assignment_group=' + id +
      '^ORsupport_group=' + id +
      '^ORuser_group=' + id +
      '^ORchange_control=' + id +
      '^ORmanaged_by_group=' + id;
    var classExclusion = '^sys_class_nameNOT LIKEcmdb_ci_service^sys_class_name!=service_offering';
    gr.addEncodedQuery(groupQuery + classExclusion);
    gr.query();
    while (gr.next()) {
      var ciName = gr.name ? gr.name.toString() : '';
      var link = this.buildLink('cmdb_ci', gr.getUniqueValue(), ciName);
      var lifeCycleStage = '';
      if (gr.life_cycle_stage) {
        lifeCycleStage = (typeof gr.life_cycle_stage.getDisplayValue === 'function')
          ? this.escapeHTML(gr.life_cycle_stage.getDisplayValue())
          : this.escapeHTML(gr.life_cycle_stage.toString());
      }
      var cmdbClass = gr.sys_class_name ? this.escapeHTML(gr.sys_class_name.toString()) : '';
      out.push([link, lifeCycleStage, cmdbClass]);
    }
    return out;
  },

checkFlowsReferencingGroup: function(groupSysId, groupName) {
  var out = [];
  var tables = [
    'sys_hub_flow',
    'sys_hub_flow_snapshot',
    'sys_flow_catalog_trigger',
    'sys_hub_flow_input',
    'sys_hub_flow_base',
    'sys_hub_action_instance_v2',
    'sys_flow_trigger_plan',
    'sys_hub_flow_component'
  ];

  var compressedFieldsByTable = {
    'sys_hub_action_instance_v2': ['values']
  };

  // Build a map of flow sys_id -> { name, current_version, app_scope }
  var flowMap = {};
  var grFlows = new GlideRecord('sys_hub_flow');
  grFlows.query();
  while (grFlows.next()) {
    flowMap[grFlows.getUniqueValue()] = {
      name: grFlows.getValue('name'),
      current_version: grFlows.getValue('current_version'),
      app_scope: grFlows.getValue('sys_scope') // sys_scope is a reference to the application
    };
  }

  // Map of sys_scope sys_id to scope name
  var scopeMap = {};
  var grScopes = new GlideRecord('sys_scope');
  grScopes.query();
  while (grScopes.next()) {
    scopeMap[grScopes.getUniqueValue()] = grScopes.getValue('scope');
  }

  function decompressIfPossible(value) {
    try {
      var bytes = GlideStringUtil.base64DecodeAsBytes(value);
      return GlideCompressionUtil.expandToString(bytes);
    } catch (e) {
      return null;
    }
  }

  var seen = {};

  for (var i = 0; i < tables.length; i++) {
    var table = tables[i];
    try {
      var gr = new GlideRecord(table);
      gr.query();
      while (gr.next()) {
        var fields = gr.getFields();
        for (var j = 0; j < fields.size(); j++) {
          var fieldName = fields.get(j).getName();
          var fieldValue = gr.getValue(fieldName);
          if (!fieldValue) continue;

          var foundMatch = false;

          // Direct match
          if (fieldValue.indexOf(groupSysId) !== -1) {
            var flowId = gr.getValue('flow');
            if (!flowId || !flowMap[flowId]) continue;

            if (table === 'sys_hub_action_instance_v2') {
              var flowVersion = gr.getValue('flow_version');
              if (flowVersion !== flowMap[flowId].current_version) {
                continue;
              }
            }

            var key = flowId + '|' + table + '|' + fieldName;
            if (seen[key]) continue;
            seen[key] = true;

            var flowName = flowMap[flowId].name || '(unknown)';
            var flowLink = this.buildLink('sys_hub_flow', flowId, flowName);
            var scopeId = flowMap[flowId].app_scope;
            var scopeName = scopeMap[scopeId] || 'global';

            out.push([
              flowLink,
              this.escapeHTML(table),
              this.escapeHTML(fieldName),
              this.escapeHTML(scopeName)
            ]);
            foundMatch = true;
          }

          // Decompress only known compressed fields
          if (!foundMatch && compressedFieldsByTable[table] && compressedFieldsByTable[table].indexOf(fieldName) !== -1) {
            var decompressed = decompressIfPossible(fieldValue);
            if (decompressed && decompressed.indexOf(groupSysId) !== -1) {
              var flowId = gr.getValue('flow');
              if (!flowId || !flowMap[flowId]) continue;

              if (table === 'sys_hub_action_instance_v2') {
                var flowVersion = gr.getValue('flow_version');
                if (flowVersion !== flowMap[flowId].current_version) {
                  continue;
                }
              }

              var key = flowId + '|' + table + '|' + fieldName;
              if (seen[key]) continue;
              seen[key] = true;

              var flowName = flowMap[flowId].name || '(unknown)';
              var flowLink = this.buildLink('sys_hub_flow', flowId, flowName);
              var scopeId = flowMap[flowId].app_scope;
              var scopeName = scopeMap[scopeId] || 'global';

              out.push([
                flowLink,
                this.escapeHTML(table),
                this.escapeHTML(fieldName),
                this.escapeHTML(scopeName)
              ]);
            }
          }
        }
      }
    } catch (e) {
      // Optional: log or handle error
    }
  }
  return out;
},

  checkCatalogItems: function(id, name) {
    var out = [];
    var gr = new GlideRecord('sc_cat_item');
    gr.addQuery('group', id);
    gr.query();
    while (gr.next()) {
      out.push([
        this.escapeHTML(gr.name),
        this.buildLink('sc_cat_item', gr.getUniqueValue(), 'Open')
      ]);
    }
    return out;
  },

  checkBusinessRules: function(id, name) {
    var out = [];
    var gr = new GlideRecord('sys_script');
    gr.addEncodedQuery('scriptLIKE' + name + '^ORscriptLIKE' + id);
    gr.query();
    while (gr.next()) {
      out.push([
        this.escapeHTML(gr.name),
        this.buildLink('sys_script', gr.getUniqueValue(), 'Open')
      ]);
    }
    return out;
  },

  checkClientScripts: function(id, name) {
    var out = [];
    var gr = new GlideRecord('sys_script_client');
    gr.addEncodedQuery('scriptLIKE' + name + '^ORscriptLIKE' + id);
    gr.query();
    while (gr.next()) {
      out.push([
        this.escapeHTML(gr.name),
        this.buildLink('sys_script_client', gr.getUniqueValue(), 'Open')
      ]);
    }
    return out;
  },

  checkScheduledJobs: function(id, name) {
    var out = [];
    var gr = new GlideRecord('sysauto_script');
    gr.addEncodedQuery('scriptLIKE' + name + '^ORscriptLIKE' + id);
    gr.query();
    while (gr.next()) {
      out.push([
        this.escapeHTML(gr.name),
        this.buildLink('sysauto_script', gr.getUniqueValue(), 'Open')
      ]);
    }
    return out;
  },

  checkUIActions: function(id, name) {
    var out = [];
    var gr = new GlideRecord('sys_ui_action');
    gr.addEncodedQuery('scriptLIKE' + name + '^ORscriptLIKE' + id);
    gr.query();
    while (gr.next()) {
      out.push([
        this.escapeHTML(gr.name),
        this.buildLink('sys_ui_action', gr.getUniqueValue(), 'Open')
      ]);
    }
    return out;
  },

  checkACL: function(id, name) {
    var out = [];
    var gr = new GlideRecord('sys_security_acl');
    gr.addEncodedQuery('scriptLIKE' + name + '^ORscriptLIKE' + id);
    gr.query();
    while (gr.next()) {
      out.push([
        this.escapeHTML(gr.name),
        this.buildLink('sys_security_acl', gr.getUniqueValue(), 'Open')
      ]);
    }
    return out;
  },

  checkUIPolicies: function(id, name) {
    var out = [];
    var gr = new GlideRecord('sys_ui_policy');
    gr.addEncodedQuery('script_trueLIKE' + name + '^ORscript_trueLIKE' + id + '^ORscript_falseLIKE' + name + '^ORscript_falseLIKE' + id);
    gr.query();
    while (gr.next()) {
      out.push([
        this.escapeHTML(gr.name),
        this.buildLink('sys_ui_policy', gr.getUniqueValue(), 'Open')
      ]);
    }
    return out;
  },

  checkRecordProducers: function(id, name) {
    var out = [];
    var gr = new GlideRecord('sc_cat_item_producer');
    gr.addEncodedQuery('scriptLIKE' + name + '^ORscriptLIKE' + id);
    gr.query();
    while (gr.next()) {
      out.push([
        this.escapeHTML(gr.name),
        this.buildLink('sc_cat_item_producer', gr.getUniqueValue(), 'Open')
      ]);
    }
    return out;
  },

  checkScriptIncludes: function(id, name) {
    var out = [];
    var gr = new GlideRecord('sys_script_include');
    gr.addEncodedQuery('scriptLIKE' + name + '^ORscriptLIKE' + id);
    gr.query();
    while (gr.next()) {
      out.push([
        this.escapeHTML(gr.name),
        this.buildLink('sys_script_include', gr.getUniqueValue(), 'Open')
      ]);
    }
    return out;
  },

  checkEmailScripts: function(id, name) {
    var out = [];
    var gr = new GlideRecord('sys_script_email');
    gr.addEncodedQuery('scriptLIKE' + name + '^ORscriptLIKE' + id);
    gr.query();
    while (gr.next()) {
      out.push([
        this.escapeHTML(gr.name),
        this.buildLink('sys_script_email', gr.getUniqueValue(), 'Open')
      ]);
    }
    return out;
  },

  type: 'GroupDependencyUtilsNormal'
});