I need a SQL query for CA

[JimmyNeutron]

Hi,

I need help creating a sql query to collect the following data:

     *   User Name/Login ID
     *   First Name
     *   Last Name
     *   Email address
     *   Account Disabled Flag
     *   Entitlement within the application once logged in (i.e., role, profile, permissions)

[EV_Ajay]

Hi Rambone,

Please run following SQL query against CA customer database.

Select tu.DisplayName

,tu.FirstName

,tu.Surname

,tc.CaseID 'DepartmentID'

,tc.Name 'Department Name'

,tu.EmployeeID

,ta.Address 'EmailID'

,tu.MonitorStatusID

,ts.Name

From tblAddressUser tu

Inner Join tblHistCaseAddressUser th

ON th.AddressOwnerID=tu.AddressOwnerID

Inner Join tblCase tc

ON tc.CaseID=th.CaseID AND th.EndDate IS NULL

Inner Join tblAddress ta

ON ta.AddressOwnerID=tu.AddressOwnerID

Inner Join tblStatus ts

ON ts.StatusID=tu.MonitorStatusID

Where tc.Type=102

Order by tc.Name

[EV_Ajay · Accepted solution]

Hi run following SQL query to find out Roles and permissions :

SELECT DISTINCT(tcp.PermissionID)

, tp.Name AS 'Permission Name'

, tr.RoleName AS 'Role'

, tcp.CaseID

, tc.Name AS 'Case/Dept/Folder Name'

, CASE WHEN (tc.FolderType = 330 AND tc.Type = 101) THEN 'Case'

WHEN (tc.FolderType = 330 AND tc.Type = 102) THEN 'Department'

WHEN (tc.FolderType = 331 AND tc.Type = 101) THEN 'Folder'

WHEN (tc.FolderType = 332 AND tc.Type = 101) THEN 'Hidden Folder'

ELSE 'Other' END 'FolderType'

, tpl.PrincipalID

, tpl.PrincipalName

, tpl.PrincipalLogin

, tcp.StartDate

, tcp.EndDate

FROM tblHistCasePermission AS tcp

INNER JOIN tblPermission AS tp ON tcp.PermissionID = tp.PermissionID

INNER JOIN tblIntRolePermission tirp ON tcp.PermissionID = tirp.PermissionID

INNER JOIN tblRole tr ON tirp.RoleID = tr.RoleID

INNER JOIN tblCase AS tc ON tc.CaseID = tcp.CaseID

INNER JOIN tblPrincipal AS tpl ON tpl.PrincipalID = tcp.PrincipalID

ORDER BY tpl.PrincipalName, tc.Name, tr.RoleName, tp.Name, tcp.StartDate, tcp.EndDate

[JimmyNeutron]

Hi Ajay,

I will give these a try tomorrow morning and let you know if this is what I'm looking for. 

Thanks