On R1100, we are creating roles and groups in SN to ensure we grant My Approval widget access to people who need it.

We need a way of querying who should have that access:

var role = "u_bolt_member";
gs.print("querying for " + role);
var gr = new GlideAggregate('u_cmn_organization'); //GlideAggregate query
gr.addAggregate('count'); //Count aggregate (only necessary for a count of items of each OS)
gr.orderByAggregate('count'); //Count aggregate ordering
gr.groupBy(role); //Group aggregate by the 'os' field
gr.query();
var total = 0;
while(gr.next()){
   var person = gr.u_bolt_member;
   var boltCount = gr.getAggregate('count'); //Get the count of the OS group
   //Print the OS name and count of items with that OS
  // gs.print('Distinct bolt people: ' + gr.u_head + ': ' + boltCount);
   var guser = new GlideRecord('sys_user'); // user query
   guser.addQuery('sys_id',person);
   guser.query();
   guser.next();
   var netid = guser.user_name;
//   gs.print(gr.u_head + " maps onto netid: " + netid); 
//   gs.print(netid); 
   total++;
}
gs.print("total is: " + total);
var role = "u_head";
////////////////////////
gs.print("querying for " + role);
var gr = new GlideAggregate('u_cmn_organization'); //GlideAggregate query
gr.addAggregate('count'); //Count aggregate (only necessary for a count of items of each OS)
gr.orderByAggregate('count'); //Count aggregate ordering
gr.groupBy(role); //Group aggregate by the 'os' field
gr.query();
var total = 0;
while(gr.next()){
   var person = gr.u_bolt_member;
   var boltCount = gr.getAggregate('count'); //Get the count of the OS group
   //Print the OS name and count of items with that OS
  // gs.print('Distinct bolt people: ' + gr.u_head + ': ' + boltCount);
   var guser = new GlideRecord('sys_user'); // user query
   guser.addQuery('sys_id',person);
   guser.query();
   guser.next();
   var netid = guser.user_name;
//   gs.print(gr.u_head + " maps onto netid: " + netid); 
//   gs.print(netid); 
   total++;
}
gs.print("total is: " + total);
//////////////////
var role = "u_opsmgr";
gs.print("querying for " + role);
var gr = new GlideAggregate('u_cmn_organization'); //GlideAggregate query
gr.addAggregate('count'); //Count aggregate (only necessary for a count of items of each OS)
gr.orderByAggregate('count'); //Count aggregate ordering
gr.groupBy(role); //Group aggregate by the 'os' field
gr.query();
var total = 0;
while(gr.next()){
   var person = gr.u_bolt_member;
   var boltCount = gr.getAggregate('count'); //Get the count of the OS group
   //Print the OS name and count of items with that OS
  // gs.print('Distinct bolt people: ' + gr.u_head + ': ' + boltCount);
   var guser = new GlideRecord('sys_user'); // user query
   guser.addQuery('sys_id',person);
   guser.query();
   guser.next();
   var netid = guser.user_name;
//   gs.print(gr.u_head + " maps onto netid: " + netid); 
//   gs.print(netid); 
   total++;
}
gs.print("total is: " + total);

results:

*** Script: querying for u_bolt_member
*** Script: total is: 7
*** Script: querying for u_head
*** Script: total is: 75
*** Script: querying for u_opsmgr
*** Script: total is: 115

Here's an attempt to converge this to a single query

var role = "u_bolt_member";
gs.print("querying for " + role);
var gr = new GlideAggregate('u_cmn_organization'); //GlideAggregate query
gr.addAggregate('count'); //Count aggregate (only necessary for a count of items of each OS)
gr.orderByAggregate('count'); //Count aggregate ordering
gr.groupBy('u_bolt_member'); //Group aggregate by the 'os' field
gr.groupBy('u_head');
gr.groupBy('u_opsmgr');
gr.query();
var total = 0;
while(gr.next()){
   var person = gr.u_bolt_member;
   var boltCount = gr.getAggregate('count'); //Get the count of the OS group
   //Print the OS name and count of items with that OS
   //gs.print('Distinct people: ' + gr.u_head + ': ' + boltCount);
   var guser = new GlideRecord('sys_user'); // user query
   guser.addQuery('sys_id',person);
   guser.query();
   guser.next();
   var netid = guser.user_name;
//   gs.print(gr.u_head + " maps onto netid: " + netid); 
   gs.print(netid); 
   total++;
}
gs.print("total is: " + total);
var role = "u_head";

result

*** Script: total is: 165

This seems to be the correct total. However, I don't seem to have a reliable method to get just the unique netids out, because the appropriate element on the row we need to yank out is difficult to determine: sometimes the LA, sometimes the OM, sometimes the Bolt.

The next idea is to return to the separate queries and then try merging things using a javascript array, then make things unique in javascript. Here goes that try

var m = new Array();
var role = "u_bolt_member";
gs.print("querying for " + role);
var gr = new GlideAggregate('u_cmn_organization'); //GlideAggregate query
gr.addAggregate('count'); //Count aggregate (only necessary for a count of items of each OS)
gr.orderByAggregate('count'); //Count aggregate ordering
gr.groupBy(role); //Group aggregate by the 'os' field
gr.query();
var total = 0;
while(gr.next()){
   var person = gr.u_bolt_member;
   var boltCount = gr.getAggregate('count'); //Get the count of the OS group
   //Print the OS name and count of items with that OS
  // gs.print('Distinct bolt people: ' + gr.u_head + ': ' + boltCount);
   var guser = new GlideRecord('sys_user'); // user query
   guser.addQuery('sys_id',person);
   guser.query();
   guser.next();
   var netid = guser.user_name;
//   gs.print(gr.u_head + " maps onto netid: " + netid); 
//   gs.print(netid); 
    m.push(netid);
   total++;
}
gs.print("total is: " + total);
gs.print("array count is: " + m.length);
////////////////////////
var role = "u_head";
gs.print("querying for " + role);
var gr = new GlideAggregate('u_cmn_organization'); //GlideAggregate query
gr.addAggregate('count'); //Count aggregate (only necessary for a count of items of each OS)
gr.orderByAggregate('count'); //Count aggregate ordering
gr.groupBy(role); //Group aggregate by the 'os' field
gr.query();
var total = 0;
while(gr.next()){
   var person = gr.u_head;
   var boltCount = gr.getAggregate('count'); //Get the count of the OS group
   //Print the OS name and count of items with that OS
  // gs.print('Distinct bolt people: ' + gr.u_head + ': ' + boltCount);
   var guser = new GlideRecord('sys_user'); // user query
   guser.addQuery('sys_id',person);
   guser.query();
   guser.next();
   var netid = guser.user_name;
//   gs.print(gr.u_head + " maps onto netid: " + netid); 
//   gs.print(netid); 
   m.push(netid);
   total++;
}
gs.print("total is: " + total);
gs.print("array count is: " + m.length);
//////////////////
var role = "u_opsmgr";
gs.print("querying for " + role);
var gr = new GlideAggregate('u_cmn_organization'); //GlideAggregate query
gr.addAggregate('count'); //Count aggregate (only necessary for a count of items of each OS)
gr.orderByAggregate('count'); //Count aggregate ordering
gr.groupBy(role); //Group aggregate by the 'os' field
gr.query();
var total = 0;
while(gr.next()){
   var person = gr.u_opsmgr;
   var boltCount = gr.getAggregate('count'); //Get the count of the OS group
   //Print the OS name and count of items with that OS
  // gs.print('Distinct bolt people: ' + gr.u_head + ': ' + boltCount);
   var guser = new GlideRecord('sys_user'); // user query
   guser.addQuery('sys_id',person);
   guser.query();
   guser.next();
   var netid = guser.user_name;
//   gs.print(gr.u_head + " maps onto netid: " + netid); 
//   gs.print(netid); 
   m.push(netid);
   total++;
}
gs.print("total is: " + total);
gs.print("array count is: " + m.length);
/////////////
gs.print('trying to flatten the array');
var arrayUtil = new ArrayUtil();
var distinct = arrayUtil.unique(m);
gs.print("unique count is: " + distinct.length);
distinct.sort();
gs.print(distinct.toString());
gs.print("ready for AD");
// this is programmed to remove the undefined element
var ad = '';
for( var i = 0; i < distinct.length -1; i++ ) {
 if ( ad != '' ) {
  ad = ad + ';';
 }
 ad = ad + distinct[i];
}
gs.print(ad);

result

*** Script: querying for u_bolt_member
*** Script: total is: 7
*** Script: array count is: 7
*** Script: querying for u_head
*** Script: total is: 75
*** Script: array count is: 82
*** Script: querying for u_opsmgr
*** Script: total is: 115
*** Script: array count is: 197
*** Script: trying to flatten the array
*** Script: unique count is: 195
*** Script: ab695,ach39,ad522,aev4,alb45,amb25,apr27,as2349,as2388,as349,asgambat,aws22,baa35,bd248,beechejr,bjr4,bm92,bp254,caf2,cam49,cap9,capezzcp,cascott,cclaflin,cf394,cjb6,cjd43,cjd43,clr26,cody,crb3,cs348,dbpetros,dck26,ddh2,ddimassa,de89,dk35,dlj35,dlp4,dmcgraw,dmk8,dmm73,dmz5,dos2,dt379,dtn4,dw352,dz3,eab78,em228,emc23,esandor,fb7,ftemple,gp72,grantjr,grantjr,hb276,hmp25,hpretty,hs343,iss6,jal227,jbentley,jc728,jca35,jcrosby,jcrosby,jcs228,jd39,jdd25,jdj3,jef32,jew8,jg825,jh877,jis4,jk424,jk835,jl898,jld23,jm238,jm536,jrollins,jrw48,js2334,js2334,jt449,jw266,jwl47,jwl7,kal4,kcm4,kd259,kdb9,kf4,kg247,kjd2,km537,km653,kr5,krd7,ks584,kv86,kw248,lac88,lbc3,lds7,lefebvre,lj62,ljm33,lkw2,lms53,lr369,ls8,luanne,margmac,mbw22,mc2,mcr8,mcsmith,mcy2,md452,mf74,mg869,mgb22,mkd22,mm235,mz237,nas39,nls4,nmc3,nmd2,no43,pab22,pab23,pal36,palmeri,patience,pcoles,ped2,pf92,popolid,pp264,pv26,pwilson,ral29,rb7,rgk23,rh369,rh458,rk44,rmc22,rmc7,rro2,rs825,rsrome,sag3,sally,sandran,saz4,sbk29,sc655,scs6,sd44,sdm5,sep5,sg32,sic7,sjb52,sjl53,sjperry,sjs38,slatter,sm622,spm3,spw4,sts8,szh4,szotto,szotto,tac3,tad1,tb23,td77,tenajg,tlm26,wc6,wjl5,wp96,zit3,zl68,zmb3,
*** Script: ready for AD
*** Script: ab695;ach39;ad522;aev4;alb45;amb25;apr27;as2349;as2388;as349;asgambat;aws22;baa35;bd248;beechejr;bjr4;bm92;bp254;caf2;cam49;cap9;capezzcp;cascott;cclaflin;cf394;cjb6;cjd43;cjd43;clr26;cody;crb3;cs348;dbpetros;dck26;ddh2;ddimassa;de89;dk35;dlj35;dlp4;dmcgraw;dmk8;dmm73;dmz5;dos2;dt379;dtn4;dw352;dz3;eab78;em228;emc23;esandor;fb7;ftemple;gp72;grantjr;grantjr;hb276;hmp25;hpretty;hs343;iss6;jal227;jbentley;jc728;jca35;jcrosby;jcrosby;jcs228;jd39;jdd25;jdj3;jef32;jew8;jg825;jh877;jis4;jk424;jk835;jl898;jld23;jm238;jm536;jrollins;jrw48;js2334;js2334;jt449;jw266;jwl47;jwl7;kal4;kcm4;kd259;kdb9;kf4;kg247;kjd2;km537;km653;kr5;krd7;ks584;kv86;kw248;lac88;lbc3;lds7;lefebvre;lj62;ljm33;lkw2;lms53;lr369;ls8;luanne;margmac;mbw22;mc2;mcr8;mcsmith;mcy2;md452;mf74;mg869;mgb22;mkd22;mm235;mz237;nas39;nls4;nmc3;nmd2;no43;pab22;pab23;pal36;palmeri;patience;pcoles;ped2;pf92;popolid;pp264;pv26;pwilson;ral29;rb7;rgk23;rh369;rh458;rk44;rmc22;rmc7;rro2;rs825;rsrome;sag3;sally;sandran;saz4;sbk29;sc655;scs6;sd44;sdm5;sep5;sg32;sic7;sjb52;sjl53;sjperry;sjs38;slatter;sm622;spm3;spw4;sts8;szh4;szotto;szotto;tac3;tad1;tb23;td77;tenajg;tlm26;wc6;wjl5;wp96;zit3;zl68;zmb3

This still has duplicates in it. Boo.

Trying to squeeze out the last duplicates

function checkDuplicates(a) {
   //Check all values in the incoming array and eliminate any duplicates
   var r = new Array(); //Create a new array to be returned with unique values
   //Iterate through all values in the array passed to this function
   o:for(var i = 0, n = a.length; i < n; i++){
      //Iterate through any values in the array to be returned
      for(var x = 0, y = r.length; x < y; x++){
         //Compare the current value in the return array with the current value in the incoming array
         if(r[x]==a[i]){
            //If they match, then the incoming array value is a duplicate and should be skipped
            continue o;
         }
      }
      //If the value hasn't already been added to the return array (not a duplicate) then add it
      r[r.length] = a[i];
   }
   //Return the reconstructed array of unique values
   return r;
}

var m = new Array();
var role = "u_bolt_member";
gs.print("querying for " + role);
var gr = new GlideAggregate('u_cmn_organization'); //GlideAggregate query
gr.addAggregate('count'); //Count aggregate (only necessary for a count of items of each OS)
gr.orderByAggregate('count'); //Count aggregate ordering
gr.groupBy(role); //Group aggregate by the 'os' field
gr.query();
var total = 0;
while(gr.next()){
   var person = gr.u_bolt_member;
   var boltCount = gr.getAggregate('count'); //Get the count of the OS group
   //Print the OS name and count of items with that OS
  // gs.print('Distinct bolt people: ' + gr.u_head + ': ' + boltCount);
   var guser = new GlideRecord('sys_user'); // user query
   guser.addQuery('sys_id',person);
   guser.query();
   guser.next();
   var netid = guser.user_name;
//   gs.print(gr.u_head + " maps onto netid: " + netid); 
//   gs.print(netid); 
    m.push(netid);
   total++;
}
gs.print("total is: " + total);
gs.print("array count is: " + m.length);
////////////////////////
var role = "u_head";
gs.print("querying for " + role);
var gr = new GlideAggregate('u_cmn_organization'); //GlideAggregate query
gr.addAggregate('count'); //Count aggregate (only necessary for a count of items of each OS)
gr.orderByAggregate('count'); //Count aggregate ordering
gr.groupBy(role); //Group aggregate by the 'os' field
gr.query();
var total = 0;
while(gr.next()){
   var person = gr.u_head;
   var boltCount = gr.getAggregate('count'); //Get the count of the OS group
   //Print the OS name and count of items with that OS
  // gs.print('Distinct bolt people: ' + gr.u_head + ': ' + boltCount);
   var guser = new GlideRecord('sys_user'); // user query
   guser.addQuery('sys_id',person);
   guser.query();
   guser.next();
   var netid = guser.user_name;
//   gs.print(gr.u_head + " maps onto netid: " + netid); 
//   gs.print(netid); 
   m.push(netid);
   total++;
}
gs.print("total is: " + total);
gs.print("array count is: " + m.length);
//////////////////
var role = "u_opsmgr";
gs.print("querying for " + role);
var gr = new GlideAggregate('u_cmn_organization'); //GlideAggregate query
gr.addAggregate('count'); //Count aggregate (only necessary for a count of items of each OS)
gr.orderByAggregate('count'); //Count aggregate ordering
gr.groupBy(role); //Group aggregate by the 'os' field
gr.query();
var total = 0;
while(gr.next()){
   var person = gr.u_opsmgr;
   var boltCount = gr.getAggregate('count'); //Get the count of the OS group
   //Print the OS name and count of items with that OS
  // gs.print('Distinct bolt people: ' + gr.u_head + ': ' + boltCount);
   var guser = new GlideRecord('sys_user'); // user query
   guser.addQuery('sys_id',person);
   guser.query();
   guser.next();
   var netid = guser.user_name;
//   gs.print(gr.u_head + " maps onto netid: " + netid); 
//   gs.print(netid); 
   m.push(netid);
   total++;
}
gs.print("total is: " + total);
gs.print("array count is: " + m.length);
/////////////
gs.print('trying to flatten the array');
var distinct = new ArrayUtil().unique(m);
gs.print("unique count is: " + distinct.length);
distinct.sort();
var uniquefied = checkDuplicates(m);

gs.print('uniqified length: ' + uniquefied.length);
gs.print(distinct.toString());
gs.print("ready for AD");
distinct = uniquefied;
distinct.sort();
// this is programmed to remove the undefined element
var ad = '';
for( var i = 0; i < distinct.length -1; i++ ) {
 if ( ad != '' ) {
  ad = ad + ';';
 }
 ad = ad + distinct[i];
}
gs.print(ad);

Result

*** Script: querying for u_bolt_member
*** Script: total is: 7
*** Script: array count is: 7
*** Script: querying for u_head
*** Script: total is: 75
*** Script: array count is: 82
*** Script: querying for u_opsmgr
*** Script: total is: 115
*** Script: array count is: 197
*** Script: trying to flatten the array
*** Script: unique count is: 195
*** Script: uniqified length: 190
*** Script: ab695,ach39,ad522,aev4,alb45,amb25,apr27,as2349,as2388,as349,asgambat,aws22,baa35,bd248,beechejr,bjr4,bm92,bp254,caf2,cam49,cap9,capezzcp,cascott,cclaflin,cf394,cjb6,cjd43,cjd43,clr26,cody,crb3,cs348,dbpetros,dck26,ddh2,ddimassa,de89,dk35,dlj35,dlp4,dmcgraw,dmk8,dmm73,dmz5,dos2,dt379,dtn4,dw352,dz3,eab78,em228,emc23,esandor,fb7,ftemple,gp72,grantjr,grantjr,hb276,hmp25,hpretty,hs343,iss6,jal227,jbentley,jc728,jca35,jcrosby,jcrosby,jcs228,jd39,jdd25,jdj3,jef32,jew8,jg825,jh877,jis4,jk424,jk835,jl898,jld23,jm238,jm536,jrollins,jrw48,js2334,js2334,jt449,jw266,jwl47,jwl7,kal4,kcm4,kd259,kdb9,kf4,kg247,kjd2,km537,km653,kr5,krd7,ks584,kv86,kw248,lac88,lbc3,lds7,lefebvre,lj62,ljm33,lkw2,lms53,lr369,ls8,luanne,margmac,mbw22,mc2,mcr8,mcsmith,mcy2,md452,mf74,mg869,mgb22,mkd22,mm235,mz237,nas39,nls4,nmc3,nmd2,no43,pab22,pab23,pal36,palmeri,patience,pcoles,ped2,pf92,popolid,pp264,pv26,pwilson,ral29,rb7,rgk23,rh369,rh458,rk44,rmc22,rmc7,rro2,rs825,rsrome,sag3,sally,sandran,saz4,sbk29,sc655,scs6,sd44,sdm5,sep5,sg32,sic7,sjb52,sjl53,sjperry,sjs38,slatter,sm622,spm3,spw4,sts8,szh4,szotto,szotto,tac3,tad1,tb23,td77,tenajg,tlm26,wc6,wjl5,wp96,zit3,zl68,zmb3,
*** Script: ready for AD
*** Script: ab695;ach39;ad522;aev4;alb45;amb25;apr27;as2349;as2388;as349;asgambat;aws22;baa35;bd248;beechejr;bjr4;bm92;bp254;caf2;cam49;cap9;capezzcp;cascott;cclaflin;cf394;cjb6;cjd43;clr26;cody;crb3;cs348;dbpetros;dck26;ddh2;ddimassa;de89;dk35;dlj35;dlp4;dmcgraw;dmk8;dmm73;dmz5;dos2;dt379;dtn4;dw352;dz3;eab78;em228;emc23;esandor;fb7;ftemple;gp72;grantjr;hb276;hmp25;hpretty;hs343;iss6;jal227;jbentley;jc728;jca35;jcrosby;jcs228;jd39;jdd25;jdj3;jef32;jew8;jg825;jh877;jis4;jk424;jk835;jl898;jld23;jm238;jm536;jrollins;jrw48;js2334;jt449;jw266;jwl47;jwl7;kal4;kcm4;kd259;kdb9;kf4;kg247;kjd2;km537;km653;kr5;krd7;ks584;kv86;kw248;lac88;lbc3;lds7;lefebvre;lj62;ljm33;lkw2;lms53;lr369;ls8;luanne;margmac;mbw22;mc2;mcr8;mcsmith;mcy2;md452;mf74;mg869;mgb22;mkd22;mm235;mz237;nas39;nls4;nmc3;nmd2;no43;pab22;pab23;pal36;palmeri;patience;pcoles;ped2;pf92;popolid;pp264;pv26;pwilson;ral29;rb7;rgk23;rh369;rh458;rk44;rmc22;rmc7;rro2;rs825;rsrome;sag3;sally;sandran;saz4;sbk29;sc655;scs6;sd44;sdm5;sep5;sg32;sic7;sjb52;sjl53;sjperry;sjs38;slatter;sm622;spm3;spw4;sts8;szh4;szotto;tac3;tad1;tb23;td77;tenajg;tlm26;wc6;wjl5;wp96;zit3;zl68;zmb3

ServiceNow

Querying ORG table data for approvers

We need a way of querying who should have that access:

results:

Here's an attempt to converge this to a single query

result

The next idea is to return to the separate queries and then try merging things using a javascript array, then make things unique in javascript. Here goes that try

result

Trying to squeeze out the last duplicates

Result