How To Export Associated Groups & Roles

[MrDevanWright]

I'm trying to obtain an excel spread sheet of all the groups, child groups, and their associated/inherited roles. Is there a way to export some file showing these relationships? 

[MrDevanWright]

Thank you for the help! I assume this script would be created in a Script Include module in ServiceNow? That worked but I am not sure how to view the data it created, nor do I know how to implement the second part with your link.

[KB18]

Dear @MrDevanWright 

As you want excel spread sheet for the group and their associated roles. 

Please create a database view on the basis of Group [ sys_user_group], Group Member [sys_user_grmember_list] and Roles [sys_group_has_role]  table and once database view is created, right click on the any column in the list and export it.

[MrDevanWright]

I successfully added sys_user_group, sys_user_grmember, and sys_group_has_role to a Database View but how do I view the data? I assume creating a report based on that Database View but no data is shown in that report. A tutorial for creating a Database View on YouTube added information to the 'Where clause' but I'm not sure how to apply that to the data I am looking for. Can you assist?

[Rahul Kumar17]

Hi MrDevanWright,

Yes, you can export a file showing the relationships between groups, child groups, and their associated/inherited roles in ServiceNow. Here's an example of how you can achieve this using a scripted export:

1. Navigate to the "System Definition" > "Script Includes" module in ServiceNow.

2. Create a new Script Include, and provide a name for it, such as "GroupHierarchyExporter".

3. In the Script Include, add the following JavaScript code:

var GroupHierarchyExporter = Class.create();

GroupHierarchyExporter.prototype = {
  exportGroupHierarchy: function() {
    var groups = this.getAllGroups();
    var spreadsheet = this.createSpreadsheet(groups);
    return spreadsheet.export();
  },

  getAllGroups: function() {
    var groupList = [];

    var gr = new GlideRecord('sys_user_group');
    gr.query();
    while (gr.next()) {
      groupList.push(gr.getUniqueValue());
    }

    return groupList;
  },

  createSpreadsheet: function(groups) {
    var spreadsheet = new global.JSONSpreadsheet();
    spreadsheet.addSheet('Group Hierarchy');

    for (var i = 0; i < groups.length; i++) {
      var groupSysId = groups[i];
      var groupRecord = new GlideRecord('sys_user_group');
      if (groupRecord.get(groupSysId)) {
        var groupName = groupRecord.getValue('name');
        var roleNames = this.getInheritedRoleNames(groupRecord);

        var row = [groupName].concat(roleNames);
        spreadsheet.addRow('Group Hierarchy', row);
      }
    }

    return spreadsheet;
  },

  getInheritedRoleNames: function(groupRecord) {
    var roleNames = [];

    var gr = new GlideRecord('sys_user_has_role');
    gr.addQuery('user', groupRecord.sys_id);
    gr.query();
    while (gr.next()) {
      var roleName = gr.getValue('role.name');
      roleNames.push(roleName);
    }

    return roleNames;
  },
};

After that

1. Create a Business Rule or UI Action that will execute the export process when triggered. For example, you can create a UI Action on the "sys_user_group" table with the following script:

(function() {
  var exporter = new GroupHierarchyExporter();
  var spreadsheet = exporter.exportGroupHierarchy();

  var fileName = 'GroupHierarchy.xlsx';
  gs.saveAsAttachment(spreadsheet, fileName);
})();

Thanks,

Rahul Kumar

[MrDevanWright]

When I try to build the script in Script Include it throws a an error "Could not save record because of a compile error: JavaScript parse error at line (7) column (30) problem = missing name after . operator (<refname>; line 7)." Any suggestions?