ITIL users used to be an easy query, because it used to be the case that we manually added every ServiceNow user with an assignment group to a separate assignment group called ITIL.
After running like that for a year and a half, it was clear that this was a lot of work and mindless maintenance to keep things going.
Instead, we modified every assignment to grant the ITIL role, and we decommissioned the ITIL assignment group.
This change was non-controversial, and freed up the ServiceNow administrators to do more valuable things with their time.
However, this broke reporting Venkat was using for generating a report for ITIL users.
Venkat needed a new way to pull a report from ServiceNow of ITIL users. It's here:
The business rule is called: Itil Users
The UI page is called: user_has_role_itil
The PROD URL is: https://yale.service-now.com/user_has_role_itil.do
Tech details:
There is a cleaner way to do this, but here's one way to get this list:
var gr2 = new GlideRecord('sys_user_role');
gr2.addQuery('name','itil');
gr2.query();
if (gr2.next() ) {
gs.print('itil group sysid is: ' + gr2.sys_id);
}
//var gr = new GlideAggregate('sys_user'); //GlideAggregate query
var gr = new GlideRecord('sys_user');
gr.addQuery('roles','CONTAINS',gr2.sys_id);
gr.query();
var gr4 = new GlideAggregate('sys_user_has_role');
gr4.addQuery('role',gr2.sys_id);
gr4.groupBy('user');
gr4.query();
var count3=0;
while (gr4.next()) {
if (gr4.user.active!=false && gr4.user.email != '') {
gs.print('one user record is: ' + gr4.user.email);
count3++;
}
}
gs.print('4p,p,.puixx.eu count: ' + count3);
Tech details round two
function findItil() {
var user_array = [];
var role_id = []
var user_has_role = new GlideAggregate('sys_user_has_role');
user_has_role.addEncodedQuery('role=b4c1f5331011100094adb14c071ff154^user.active!=false^user.emailISNOTEMPTY^user.user_nameISNOTEMPTY');
user_has_role.addAggregate('COUNT', 'user');
user_has_role.query();
while (user_has_role.next()) {
user_array.push(user_has_role.user.user_name);
}
return user_array;
}
Getting a list of members of Financial Approvers (both all members, and isolating members who have no other assignment groups)
That's the basis for the maximum possible numbers. I'm counting 189 today.
What we really want is what we're describing at: https://yale.service-now.com/nav_to.do?uri=sc_task.do?sys_id=7af754d2f9146144fcb0542a9ae2793c%26sysparm_view=portal
which is that we want a collection of users who are Financial Approvers, but not a member of any other group.
Here's the listing I've been working with: https://yaledevelopment.service-now.com/sys_user_has_role_list.do
I've been working with Alan at Fruition to build up a list of Financial Approver group members who are not also members of any other assignment group. He came up with:
https://yaledevelopment.service-now.com/financial_group_members.do
I need to vet this and see whether it is true. I spot checked for Deb Bettley and Nancy Scanlon. Both were not members of the Alan version, but are members of the overall group. As such, this seems to really work.
function findFinancialMembers() {
var user_array = [];
var user_in_group = new GlideRecord('sys_user_grmember');
user_in_group.addQuery('group', '4da8813ea1a8990494ad46651315d87d'); //Financial Approvers group
user_in_group.query();
while (user_in_group.next()) {
var group_count = new GlideAggregate('sys_user_grmember');
group_count.addQuery('user', user_in_group.user.sys_id);
group_count.addAggregate('COUNT');
group_count.query();
var groups = 0;
if (group_count.next()) {
groups = group_count.getAggregate('COUNT');
if (groups == 1) {
user_array.push(user_in_group.user.user_name);
}
}
}
return user_array;
}
Isolating ITIL users who didn't get the role from Financial Approvers
So now that I have two sets of users, I really want to do set logic on it, and subtract the results of the financial_group_members.do list from the sys_user_has_role_list.do results.
This is pretty close; I'm not certain why it is that I'm getting math that doesn't quite add up.
function findItil() {
var user_array = [];
var role_id = []
var user_has_role = new GlideAggregate('sys_user_has_role');
user_has_role.addEncodedQuery('role=b4c1f5331011100094adb14c071ff154^user.active!=false^user.emailISNOTEMPTY^user.user_nameISNOTEMPTY');
user_has_role.addAggregate('COUNT', 'user');
user_has_role.query();
while (user_has_role.next()) {
user_array.push(user_has_role.user.user_name);
}
return user_array;
}
function findFinancialMembersWithNoOtherRole() {
var user_array = [];
var user_in_group = new GlideRecord('sys_user_grmember');
user_in_group.addQuery('group', '4da8813ea1a8990494ad46651315d87d'); //Financial Approvers group
user_in_group.query();
while (user_in_group.next()) {
var group_count = new GlideAggregate('sys_user_grmember');
group_count.addQuery('user', user_in_group.user.sys_id);
group_count.addAggregate('COUNT');
group_count.query();
var groups = 0;
if (group_count.next()) {
groups = group_count.getAggregate('COUNT');
if (groups == 1) {
user_array.push(user_in_group.user.user_name);
}
}
}
return user_array;
}
function arr_diff(a1, a2)
{
var a=[], diff=[];
for(var i=0;i<a1.length;i++)
a[a1[i]]=true;
for(var i=0;i<a2.length;i++)
if(a[a2[i]]) delete a[a2[i]];
else a[a2[i]]=true;
for(var k in a)
diff.push(k);
return diff;
}
function findItilMinusFinancialMembersWithNoOtherRole () {
var itil_users = findItil();
var finance_no_role_users = findFinancialMembersWithNoOtherRole();
gs.print("The length of itil users is: " + itil_users.length);
gs.print("The length of finance approvers with no other role is: " + finance_no_role_users.length);
var result = arr_diff(itil_users,finance_no_role_users);
gs.print("The subtracted set of elements is: " + result.length);
}
findItilMinusFinancialMembersWithNoOtherRole();
*** Script: The length of itil users is: 1183
*** Script: The length of finance approvers with no other role is: 185
*** Script: The subtracted set of elements is: 1000
You would expect that 1183-185 would be 998. So there must be certain elements that somehow are NOT getting counted as ITIL users? So they're not in the original set? So they don't get subtracted? That's the only answer that makes any sense to me. This suggests there is a bug in the ITIL users count. I may need to compare the ITIL users account I get from Allen's method with the one I got from my old way that takes longer.
I compared my long ITIL method against the Alan method and still came up with 1183; so I'm baffled. Anyway, this is a sufficient answer. Going to wrap it, and hand it over.