Helpdesk: User Access & Roles with teams (Built-in)

[Nick_VDB]

Added in v.6.0.100

The report below gives back the information that is found in the Users section under Configuration\User Access & Roles. Multiple rows are given back for a single user if they are part of more than one team.

Select Top 1000000 htblusers.name As [User],
  htblusers.username,
  htblusers.userdomain,
  htblusers.email As Email,
  htblusers.mobile,
  htblusers.telephone,
  htblusers.company,
  htblusers.department,
  tsysLanguages.EnglishName As Language,
  tsysRoles.Name As Role,
  Case When htblusers.userid = htblagents.userid Then 'Agent' Else 'User'
  End As AgentOrUser,
  htblteams.teamname As Team,
  Case htblagents.active When 1 Then 'Yes' Else 'No' End As IsLicensed
From htblusers
  Left Join tsysRoles On htblusers.Roleid = tsysRoles.RoleID
  Left Join htblagents On htblusers.userid = htblagents.userid
  Inner Join tsysLanguages On tsysLanguages.LanguageId = htblusers.language
  Left Join htblagentteam On htblagents.agentid = htblagentteam.agentid
  Left Join htblteams On htblteams.teamid = htblagentteam.teamid
Where htblusers.name <> 'System'
Order By [User],
  Team