Get total number of users with itil license

[dave_edgar]

Hi

For a special catalog item only available some certain users I want/need to display to total number of active users in a group that already have itil licenses. 

We have users in multiple groups so show in the sys_user_has_role table shows the user multiple times.  So how could I get a total number for the users.  I can't seem to get that work.  I know the count should be 115 but I get 240.

I am getting this via a client script triggering a script include:

var usercount = Class.create();
usercount.prototype = Object.extendsObject(AbstractAjaxProcessor, {
aggregate: function() {
var getcount = new GlideAggregate('sys_user_has_role');
          // getcount.groupBy('user');    // THIS RETURNS 'NULL' 
           getcount.addQuery('role.name','itil');
           getcount.addQuery('user.company.name','STARTSWITH','<company_name>');

           getcount.addQuery
           getcount.query();

while (getcount.next())
{
var users =getcount.user;
var license =getcount.getAggregate("COUNT", users);
gs.log("Users count ==" + license);
}
return license;
},
});

I also tried replacing getcount.addAggregate("COUNT"); AND getcount.getAggregate("COUNT", users);  with getfjscount.addAggregate("COUNT(DISTINCT)","user"); but that NULL's

[Deepak Ingale1]

https://docs.servicenow.com/bundle/kingston-application-development/page/app-store/dev_portal/API_reference/GlideAggregate/concept/c_GlideAggregateAPI.html#ariaid-title14 

Please give it a try using setGroup method along with groupBy

[richelle_pivec]

If all you need is a list of unique itil users (regardless of groups), I do this with a report. I'm guessing you want something fancier than that, but just in case:

Richelle

[Ashutosh Munot1]

Hi,

Use below code:

var gr = new GlideAggregate('sys_user_has_role');

gr.addQuery('role',sys_id of Role);
gr.addAggregate('COUNT', 'user');
gr.groupBy('user');
gr.addHaving('COUNT', '>', 1);
gr.query();
while (gr.next())
{

gs.log("Numb of Duplicates: " + gr.getAggregate('COUNT', 'user') , 'ROLES');

}

Thanks,
Ashutosh Munot

[dave_edgar]

Thanks Ashutosh but this isn’t giving me a total number of users with itil licenses, just a list like so:

ROLES: Numb of Duplicates: 3
ROLES: Numb of Duplicates: 3
ROLES: Numb of Duplicates: 3
ROLES: Numb of Duplicates: 3
ROLES: Numb of Duplicates: 2

…

When what I’m after is the total number record grouped by users, rather than the total amount of records found which finds 240 not the 116 users:

var getfjscount = new GlideAggregate('sys_user_has_role');

                                //getfjscount.groupBy('user');

                                getfjscount.addQuery('role.name','itil');

                                getfjscount.addQuery('user.company.name','STARTSWITH','fjs');

                                getfjscount.addAggregate("COUNT");

                                getfjscount.query();

                                while (getfjscount.next())

                                {

                                                var users = getfjscount.user;

                                                var license = getfjscount.getAggregate("COUNT");

                                }

gs.info('Count is = ' + license);

Script: Count is = 240

Dave