Query to get active accounts in one security system and not in another system and users are active.

[VisveswaraReddy]

Hi team,

I need return query that account should have active in one security endpoint and it should not present in another endpoint different security system and user should be active and we need orgid condition also.

[Raghu]

@VisveswaraReddy  below query use

SELECT
E.DISPLAYNAME,
A.NAME,
A.STATUS
FROM
USERS U,
ACCOUNTS A,
USER_ACCOUNTS UA,
ENDPOINTS E,
ENTITLEMENT_VALUES EV,
ACCOUNT_ENTITLEMENTS1 AE1,
SECURITYSYSTEMS SS
WHERE
U.USERKEY = UA.USERKEY
AND A.ACCOUNTKEY = UA.ACCOUNTKEY
AND A.ENDPOINTKEY = E.ENDPOINTKEY
AND E.SECURITYSYSTEMKEY = SS.SYSTEMKEY
AND AE1.ACCOUNTKEY = A.ACCOUNTKEY
AND AE1.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY
AND A.ENDPOINTKEY = 1377
AND A.SYSTEMID = 5
AND A.STATUS = 1
AND NOT EXISTS (
SELECT 1
FROM ACCOUNTS A2
WHERE A2.NAME = A.NAME
AND A2.SYSTEMID!=5
AND A2.ENDPOINTKEY!=1377

)

pass endpointkey and systemkey

[rushikeshvartak]

• if query does not work then Please elobrate with example
• SELECT u.USERKEY, u.USERNAME, a.name, a.endpointkey, u.customer
  FROM USERS u
  Join user_Accounts ua on u.userkey=ua.userkey
  JOIN ACCOUNTS a ON ua.accountkey = a.accountkey
  WHERE
  u.STATUSkey = 1
  AND a.ENDPOINTkey = 11
  AND a.STATUS not in ('SUSPENDED FROM IMPORT SERVICE')
  AND u.customer= '1a'
  AND NOT EXISTS (
  SELECT 1
  FROM ACCOUNTS a2,user_Accounts ua2
  WHERE ua2.accountkey=a2.accountkey and ua2.USERKEY = u.USERKEY
  AND a2.ENDPOINTkey = 2
  
  );

[stalluri]

@VisveswaraReddy 

SELECT A.NAME,
       CASE
           WHEN A.STATUS = 1 THEN
               'Active'
           WHEN A.STATUS = 2 THEN
               'Inactive'
           WHEN A.STATUS = 'Manually Suspended' THEN
               'Manually Suspended'
           WHEN A.STATUS = 'Manually Provisioned' THEN
               'Manually Provisioned'
           ELSE
               'Unknown'
       END AS ACCOUNT_STATUS,
       SS.DISPLAYNAME,
       E.EndpointName,
       U.Username,
       CASE
           WHEN U.STATUSKEY = 1 THEN
               'Active'
           WHEN U.STATUSKEY = 0 THEN
               'Inactive'
           ELSE
               'Unknown'
       END AS USER_STATUS
FROM ACCOUNTS A
    JOIN USER_ACCOUNTS UA
        ON A.ACCOUNTKEY = UA.ACCOUNTKEY
    JOIN USERS U
        ON UA.USERKEY = U.USERKEY
    JOIN ENDPOINTS E
        ON A.ENDPOINTKEY = E.ENDPOINTKEY
    JOIN SECURITYSYSTEMS SS
        ON E.SECURITYSYSTEMKEY = SS.SYSTEMKEY
WHERE A.ENDPOINTKEY = XX
      AND A.SYSTEMID = XX
      AND (
              A.STATUS = 1
              OR A.STATUS = 'Manually Provisioned'
          )
      AND NOT EXISTS
(
    SELECT 1
    FROM ACCOUNTS A2
    WHERE A2.ACCOUNTID = A.ACCOUNTID
          AND (
                  A2.STATUS = 1
                  OR A2.STATUS = 'Manually Provisioned'
              )
          AND (
                  A2.SYSTEMID != XX
                  OR A2.ENDPOINTKEY != XX
              )
);