Report: how many users got itil access this year and through which group

[GauriSharma]

Hello Team,

How can I create a report to get the details of how many users got itil access this year and through which group they got it.

I am aware that we have different tables for groups and users, can anyone guide me with dot walking or maybe filters, or maybe I create a database view ( which tables and what filters should be applied).

Thanking in anticipation,

Gauri 

[ersureshbe]

Hi, If you want to track only role you can get it easily with the help of 'sys_user_has_role' table. Refer the created date field and understand the role provided date.

If you want to track with the help of group when role assigned then you should create the database view to connect both role and group table. You should use the database view table and get the report. Here one complexity is there more than one group having same role then its little difficult.

[GauriSharma]

Thank you for quick response, 

I tried both the ways. 

In view I am unable to sort by group using distinct  users.

And my client is basically looking for the count which group consumed more this year itil license so that he can keep a track, we running short of itil license and this report is for the same.

Is it possible to know:  which group consumed how many itil license this year.

Thank you

[ersureshbe]

Hi, The Database view you should create between sys_group_has_role and sys_user_grmember table.

Filter the group based itil role and check the user when the filtered group assigned to the user. It will give you the right result.

[GauriSharma]

check the user when the filtered group is assigned to the user.  >>> how do I apply this filter?