Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon

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

VisveswaraReddy
New Contributor II
New Contributor II

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.

 

 

3 REPLIES 3

Raghu
All-Star
All-Star

@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


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

rushikeshvartak
All-Star
All-Star
  • 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

    );

Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

stalluri
Valued Contributor II
Valued Contributor II

@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
              )
);

Best Regards,
Sam Talluri
If you find this a helpful response, kindly consider selecting Accept As Solution and clicking on the kudos button.