Agent access report or SQL extract

[JeremyB3ntham]

Hi,

Does anyone know of a report that can be run, either via the front end or SQL to extract all Agents and what access they have to the system, including: Agent Name, Role, Group and Queue access?

[crythias]

Assuming that the users are in the users table:

Here are (some? most?) of the fields of importance
users.login, users.id, users.valid_id
role_user.user_id, role_user.role_id
role.id, role.name, role.valid_id
queue.id, queue.name, queue.group_id, queue.valid_id
groups.id, groups.name, groups.valid_id
group_role.group_id, group_role.permission_key, group_role.permission_value, group_role.role_id
group_user.group_id, group_user.permission_key, group_user.permission_value, group_role.user_id

You'll need a big list of joins and you'll want to make sure that all of the users, roles, groups, queues are valid.

To get you started:
What are the valid users?

Code: Select all

SELECT users.login FROM users WHERE users.valid_id=1

What group(s) are the valid users a member of and what permissions do they hold?

Code: Select all

SELECT users.login, groups.name, group_user.permission_key, group_user.permission_value FROM users LEFT JOIN group_user ON group_user.user_id=users.id LEFT JOIN groups ON groups.id=group_user.group_id WHERE users.valid_id=1 AND groups.valid_id=1

Likewise, it's possible to follow roles, queues by group membership, etc.