How to get a list of users is members of XYZ group is not part of assigned to or owned by

[Nani18]

Hello Experts,

My requirement is 

example :  members of ABC group (sys_user_grmember) is not part of assigned to(referenced to user table ) or owned by(referenced to user table) in windows table(cmdb_ci_win_server).

Note : Assigned to and owned by fields is present in windows server table.

How to get those users sys_ids which users is not part of assigned to " or " owned by by using script include.

Why script include is i am trying to create a report.  

Best Regards,

Nani

[Riya Verma · Accepted solution]

Hi There,
you can create a Script Include in ServiceNow that will query the sys_user_grmember table to find users who are not part of the "assigned to" or "owned by" fields in the cmdb_ci_win_server (Windows server) table.

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

  // Function to get the users who are not part of assigned to or owned by fields
  getNotAssignedOrOwnedUsers: function () {
    var usersNotAssignedOrOwned = [];

    var grMembers = new GlideRecord('sys_user_grmember');
    grMembers.query();
    while (grMembers.next()) {
      var userSysID = grMembers.user.toString();
      var assignedTo = new GlideRecord('cmdb_ci_win_server');
      assignedTo.addQuery('assigned_to', userSysID);
      assignedTo.query();
      if (!assignedTo.next()) {
        var ownedBy = new GlideRecord('cmdb_ci_win_server');
        ownedBy.addQuery('owned_by', userSysID);
        ownedBy.query();
        if (!ownedBy.next()) {
          usersNotAssignedOrOwned.push(userSysID);
        }
      }
    }

    return usersNotAssignedOrOwned;
  },

  type: 'NotAssignedOrOwnedUsers'
};

call this script from the report

[Nani18]

Hello @Riya Verma 

Thanks for your help.

Above script is working fine but the problem is in the result i can see only one user sys_id is coming.

Any changes required in script..?