Need reporting on business stakeholder license consumption (unique license, excluding ITIL)

[Suggy]

We have created reports on sys_user_has_role table to calculate itil and business_stakeholder licencse usage.

Issue is that, few users who have both the above roles., hence such users are getting reported in both reports.

But since ServiceNow's license count is based on the higher privilege role assigned to a user, not on both roles together, if a user has both roles, then only ITIL will be considered for that user right.

Hence in this case, how can report such a way that, of user has both itil and business_stakeholder, eliminate such users from 'Business stakeholder licensed users' report.

[Brian Lancaster]

I have to try and create some test data as that would not have worked. You still would have gotten duplicate accounts between the two reports. I'm not sure that there is a good way to do this.

[Brian Lancaster]

I think you best bet would be to do a cleanup via a script. You could run a script in script background to delete the business_stakeholder role from anybody who also has the ITIL role. Script below. Test in a lower environment first.

var gr = new GlideRecord('sys_user');
gr.addEncodedQuery("roles=itil^roles=business_stakeholder"); //Query sys_user table for user that have both roles
gr.query();
while(gr.next()){ //Loop thought all use who have both roles
	var role = new GlideRecord('sys_user_has_role'); //Query sys_user_has_role table
	role.addQuery('user', gr.sys_id); //get sys_id of the user from the user table query
	role.addQuery('role', "3be5d3d1b7103300dff9fa13ee11a987"); //query for business_stakeholder role. Note this sys_id is out of the box so it should be the same but you want to double check anyway.
	role.query();
	if (role.next()){
		role.deleteMultiple();
	}
}

[swapnali ombale]

Hu @Suggy 

1. Identify the relevant tables and fields:

• sys_user: This table stores user information, including the user's ID.
• sys_user_has_role: This table stores the roles assigned to each user. You'll need to use this table to check for both the "business\_stakeholder" and "itil" roles. 

2. Construct your report:

• Report Type:

  Choose a report type that allows for filtering, such as a List Report or a Report Widget.
• Table:

  Select the sys_user table as the base for your report.
• Filters:

  Add filters to the report to include only users with the "business\_stakeholder" role.
• Exclude ITIL users:

  Add another filter to exclude users who also have the "itil" role. This can be achieved by:

  • Adding a "Has Role" filter.
  • Selecting "ITIL" from the role list.
  • Selecting the "not in list" option (or similar, depending on your report type). 

3. Example Report:

• Report Name: Business Stakeholder Licensed Users (excluding ITIL)
• Table: sys\_user
• Filter 1: Has Role = "business\_stakeholder"
• Filter 2: Has Role = "itil" (and then, select "not in list") 

Kindly mark my answer as helpful and accept solution if it helped you in anyway. 

[Brian Lancaster]

Has role is not available in reports.

[Suggy]

Anyone 🙂