Write a Query to find users having manager and manager having AD account and manager employeeid.

[Pratham]

Hi Team,

We have to create analytics to find

Users having Manager, Manager having AD account(not users account) show as Yes else NO and Manager's employeeid.

for a particular endpoint(endpointname="XYZ")

Required columns: username, user employeeid, manager, manager's employeeid, manager AD account in Yes or no.

[rushikeshvartak]

Share your draft query

[Pratham]

Hi @rushikeshvartak ,

can you check this query. additionally, we have to show manager has AD account or not both.

SELECT
    u.username AS "User Name",
    u.employeeId AS "User Employee ID",
    u.manager AS "Manager Id",
    u.owner AS "Manager Name",
    u2.employeeid AS "Manager Employee ID",
    e.endpointname AS "Endpoint",
    CASE u.statuskey WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' ELSE 'UNKNOWN' END AS "USER STATUS", 
     CASE a.status WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' ELSE 'UNKNOWN' END AS "USER AD Account Status",
    CASE u2.statuskey WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' ELSE 'UNKNOWN' END AS "Manager STATUS",
    CASE a2.status WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' ELSE 'UNKNOWN' END AS "Manager AD Account Status"
FROM
 users u
 LEFT JOIN users u2
 ON u.manager = u2.userkey
 join user_accounts ua on u.userkey = ua.userkey
 join accounts a on a.accountkey = ua.accountkey
 join endpoints e on e.endpointkey = a.endpointkey
 LEFT JOIN  user_accounts ua2 on u2.userkey = ua2.userkey
 LEFT JOIN accounts a2 on a2.accountkey = ua2.accountkey
WHERE
 u.manager is not null 
AND a.status NOT IN (
 '0',
 'INACTIVE',
 'Manually Suspended',
 'SUSPENDED FROM IMPORT SERVICE' )
AND a2.status NOT IN (
 '0',
 'INACTIVE',
 'Manually Suspended',
 'SUSPENDED FROM IMPORT SERVICE' ) and
e.endpointname = 'XYZ' 

[rushikeshvartak]

SELECT u.username     AS "User Name",
       u.employeeid   AS "User Employee ID",
       u.manager      AS "Manager Id",
       u2.username    AS "Manager Name",
       u2.employeeid  AS "Manager Employee ID",
       e.endpointname AS "Endpoint",
       CASE u.statuskey
         WHEN '0' THEN 'INACTIVE'
         WHEN '1' THEN 'ACTIVE'
         ELSE 'UNKNOWN'
       END            AS "User Status",
       CASE a.status
         WHEN '0' THEN 'INACTIVE'
         WHEN '1' THEN 'ACTIVE'
         ELSE 'UNKNOWN'
       END            AS "User AD Account Status",
       CASE u2.statuskey
         WHEN '0' THEN 'INACTIVE'
         WHEN '1' THEN 'ACTIVE'
         ELSE 'UNKNOWN'
       END            AS "Manager Status",
       CASE a2.status
         WHEN '0' THEN 'INACTIVE'
         WHEN '1' THEN 'ACTIVE'
         ELSE 'UNKNOWN'
       END            AS "Manager AD Account Status",
       CASE
         WHEN ua2.userkey IS NOT NULL
              AND ua2.userkey != '' THEN 'Yes'
         ELSE 'No'
       END            AS "Manager AD Account Exists"
FROM   users u
       LEFT JOIN users u2
              ON u.manager = u2.userkey
       JOIN user_accounts ua
         ON u.userkey = ua.userkey
       JOIN accounts a
         ON a.accountkey = ua.accountkey
       JOIN endpoints e
         ON e.endpointkey = a.endpointkey
       LEFT JOIN user_accounts ua2
              ON u2.userkey = ua2.userkey
       LEFT JOIN accounts a2
              ON a2.accountkey = ua2.accountkey
WHERE  u.manager IS NOT NULL
       AND a.status NOT IN ( '0', 'INACTIVE', 'Manually Suspended',
                             'SUSPENDED FROM IMPORT SERVICE' )
       AND ( a2.status IS NULL
              OR a2.status NOT IN ( '0', 'INACTIVE', 'Manually Suspended',
                                    'SUSPENDED FROM IMPORT SERVICE' ) )
       AND e.endpointname = 'XYZ'; 

[rushikeshvartak]

SELECT u.username   AS user_username,
       u.employeeid AS user_employeeid,
       m.username   AS manager_username,
       m.employeeid AS manager_employeeid,
       CASE
         WHEN ua.userkey IS NOT NULL
              AND ua.userkey != '' THEN 'Yes'
         ELSE 'No'
       END          AS manager_ad_account
FROM   users u
       JOIN users m
         ON u.manager = m.userkey
       LEFT JOIN user_accounts ua
              ON m.userkey = ua.userkey
       LEFT JOIN accounts a
              ON ua.accountkey = a.accountkey
       JOIN endpoints e
         ON a.endpointkey = e.endpointkey
WHERE  e.endpointname = 'Rushi AD';