On R1100, we are creating roles and groups in SN to ensure we grant My Approval widget access to people who need it.
| Table of Contents |
|---|
We need a way of querying who should have that access:
| Code Block |
|---|
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
| Code Block |
|---|
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
| Code Block |
|---|
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 works properly. Hooray.