Users list without any active accounts

[Manu269]

Hello Team,

We need help with 1 report where I need to extract the list of username who does not have any active account .

Ex : user A has access to endpoint x,y,z

The report shud reflect this user only when all the accounts are inactive, in case any of the account is active report should not give this user.

[DaanishJawed]

Hi @Manu269 ,

Please try with the below query -

SELECT 
    U.USERNAME,
    U.FIRSTNAME,
    U.LASTNAME,
    CASE
        WHEN U.STATUSKEY IS NULL THEN 'NOT AVAILABLE'
        WHEN U.STATUSKEY = 0 THEN 'INACTIVE'
        WHEN U.STATUSKEY = 1 THEN 'ACTIVE'
        ELSE U.STATUSKEY
    END 'USER STATUS',
    E.Endpointname AS 'Endpoint',
    u.departmentname AS 'DEPARTMENT',
    e.endpointKey,
    A.NAME AS 'ACCOUNTNAME',
    CASE
        WHEN A.STATUS IS NULL THEN 'NOT AVAILABLE'
        WHEN A.STATUS = 1 THEN 'ACTIVE'
        ELSE A.STATUS
    END 'ACCOUNT STATUS'
FROM
    ACCOUNTS A,
    USER_ACCOUNTS UA,
    USERS U,
    ENDPOINTS E,
    SECURITYSYSTEMS S
WHERE
    A.ACCOUNTKEY = UA.ACCOUNTKEY
        AND U.USERKEY = UA.USERKEY
        AND A.ENDPOINTKEY = E.ENDPOINTKEY
        AND E.SECURITYSYSTEMKEY = S.SYSTEMKEY
        AND U.STATUSKEY = 1
        AND A.STATUS NOT IN (1 , 'Active', 'MANUALLY PROVISIONED')
ORDER BY U.USERNAME , S.SYSTEMNAME , E.ENDPOINTNAME;

[Manu269]

This is not giving right response.

The ask is to fetch only those users who do not have any active account.

[rushikeshvartak]

SELECT U.USERNAME, U.FIRSTNAME, U.LASTNAME, CASE WHEN U.STATUSKEY IS NULL THEN 'NOT AVAILABLE' WHEN U.STATUSKEY = 0 THEN 'INACTIVE' WHEN U.STATUSKEY = 1 THEN 'ACTIVE' ELSE U.STATUSKEY END 'USER STATUS', E.Endpointname AS 'Endpoint', u.departmentname AS 'DEPARTMENT', e.endpointKey, A.NAME AS 'ACCOUNTNAME', CASE WHEN A.STATUS IS NULL THEN 'NOT AVAILABLE' WHEN A.STATUS = 1 THEN 'ACTIVE' ELSE A.STATUS END 'ACCOUNT STATUS' FROM ACCOUNTS A, USER_ACCOUNTS UA, USERS U, ENDPOINTS E, SECURITYSYSTEMS S WHERE A.ACCOUNTKEY = UA.ACCOUNTKEY AND U.USERKEY = UA.USERKEY AND A.ENDPOINTKEY = E.ENDPOINTKEY AND E.SECURITYSYSTEMKEY = S.SYSTEMKEY AND U.STATUSKEY = 1 AND A.STATUS IN (2) AND e.endpointname in ('Saviynt','Coupa') ORDER BY U.USERNAME , S.SYSTEMNAME , E.ENDPOINTNAME;

[rushikeshvartak]

so if any 1 account in x y z endpoint is inactive then list user ?

[Manu269]

Hello,

This query works:

Here we need to exclude 1 particular ep

Select username, noa as 'no. of accounts', noia as 'no. Of inactive accounts' 

From (

Select u.usernamr, count (ua.accountkey) as 'noa',

Sum (case when a.status not in ('1', 'Active', 'Manually Provisioned') then 1 else 0 end) as 'noia'

From

Users u, user_accounts us, accounts a, endpoint ed where u.userkey = ua.userkey and ua.accountkey=a.accountkry and u.statuskey=1 and ed.endpointkey=a.endpointkey and ed.endpointname not in ("epname") group by 2 ) as X where noa = noia;

[rushikeshvartak]

SELECT username,
       noa AS 'no. of accounts',
       noia AS 'no. Of inactive accounts'
FROM (
    SELECT u.username,
           COUNT(ua.accountkey) AS noa,
           SUM(CASE WHEN a.status NOT IN ('1', 'Active', 'Manually Provisioned') THEN 1 ELSE 0 END) AS noia
    FROM Users u
    JOIN user_accounts ua ON u.userkey = ua.userkey
    JOIN accounts a ON ua.accountkey = a.accountkey
    JOIN endpoints ed ON ed.endpointkey = a.endpointkey
    WHERE u.statuskey = 1
      AND ed.endpointname NOT IN ('epname')
    GROUP BY u.username
) AS X
WHERE noa = noia;