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 still has duplicates in it. Boo.
Trying to squeeze out the last duplicates
Code Block |
---|
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
...