Forum Discussion

JimmyNeutron's avatar
10 years ago
Solved

I need a SQL query for CA

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)

 

 

  • 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

     

  • 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

     

  • 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

     

  • Hi Ajay,

     

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

     

     

    Thanks