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

Analytics query to revoke access verifying from multiple edpoints

sandeepgudipudi
New Contributor III
New Contributor III

Hi, I have a requirement to check in endpoint "ABC" if account last login is greater than 6 months and revoke access to specific entitlements from endpoint "XYZ"

25 REPLIES 25

rushikeshvartak
All-Star
All-Star

SELECT ACCOUNTS.NAME,
       ENTITLEMENT_VALUES.ENTITLEMENT_VALUE,
       ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY AS entvaluekey,
       ACCOUNTS.ACCOUNTKEY                     AS acctKey
FROM   ACCOUNT_ENTITLEMENTS1
       JOIN ACCOUNTS
         ON ACCOUNT_ENTITLEMENTS1.ACCOUNTKEY = ACCOUNTS.ACCOUNTKEY
       JOIN ENTITLEMENT_VALUES
         ON ACCOUNT_ENTITLEMENTS1.ENTITLEMENT_VALUEKEY =
            ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY
       JOIN ENDPOINTS
         ON ACCOUNTS.ENDPOINTKEY = ENDPOINTS.ENDPOINTKEY
WHERE  ENDPOINTS.ENDPOINTNAME = 'XYZ'
       AND ACCOUNTS.NAME IN (SELECT NAME
                             FROM   ACCOUNTS A1,
                                    ENDPOINTS E1
                             WHERE  A1.ENDPOINTKEY = E1.ENDPOINTKEY
                                    AND E1.ENDPOINTNAME = 'ABC'
                                    AND LASTLOGONDATE < DATE_SUB(NOW(),
                                                        INTERVAL 6 MONTH)); 


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

How to revoke for specific entitlements only not all entitlements in endpoint.

eg: entitlements like ("cn=abc,cn=org.cn=com","cn=wrete,cn=org.cn=com")

@sandeepgudipudi 
If it is single entitlement.
ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY AS entvaluekey,  
Replace this line with below 123 should be the value key of this ("cn=abc,cn=org.cn=com","cn=wrete,cn=org.cn=com")  
'123' AS entvaluekey,   
or 

SELECT ACCOUNTS.NAME,
ENTITLEMENT_VALUES.ENTITLEMENT_VALUE,
ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY AS entvaluekey,
ACCOUNTS.ACCOUNTKEY AS acctKey,
ENTITLEMENT_TYPES.DISPLAYNAME AS entitlement_type
FROM ACCOUNT_ENTITLEMENTS1
JOIN ACCOUNTS
ON ACCOUNT_ENTITLEMENTS1.ACCOUNTKEY = ACCOUNTS.ACCOUNTKEY
JOIN ENTITLEMENT_VALUES
ON ACCOUNT_ENTITLEMENTS1.ENTITLEMENT_VALUEKEY = ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY
JOIN ENTITLEMENT_TYPES
ON ENTITLEMENT_VALUES.ENTITLEMENTTYPEKEY = ENTITLEMENT_TYPES.ENTITLEMENTTYPEKEY
JOIN ENDPOINTS
ON ACCOUNTS.ENDPOINTKEY = ENDPOINTS.ENDPOINTKEY
WHERE ENDPOINTS.ENDPOINTNAME = 'XYZ'
AND ACCOUNTS.NAME IN (SELECT NAME
FROM ACCOUNTS A1
JOIN ENDPOINTS E1
ON A1.ENDPOINTKEY = E1.ENDPOINTKEY
WHERE E1.ENDPOINTNAME = 'ABC'
AND LASTLOGONDATE < DATE_SUB(NOW(), INTERVAL 6 MONTH))
AND ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY in (1,2,3,.....X). -- place all the entitlements value keys here


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

SELECT ACCOUNTS.NAME,
       ENTITLEMENT_VALUES.ENTITLEMENT_VALUE,
       ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY AS entvaluekey,
       ACCOUNTS.ACCOUNTKEY                     AS acctKey
FROM   ACCOUNT_ENTITLEMENTS1
       JOIN ACCOUNTS
         ON ACCOUNT_ENTITLEMENTS1.ACCOUNTKEY = ACCOUNTS.ACCOUNTKEY
       JOIN ENTITLEMENT_VALUES
         ON ACCOUNT_ENTITLEMENTS1.ENTITLEMENT_VALUEKEY =
            ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY
       JOIN ENDPOINTS
         ON ACCOUNTS.ENDPOINTKEY = ENDPOINTS.ENDPOINTKEY
WHERE  ENDPOINTS.ENDPOINTNAME = 'XYZ'
       AND ACCOUNTS.NAME IN (SELECT NAME
                             FROM   ACCOUNTS A1,
                                    ENDPOINTS E1
                             WHERE  A1.ENDPOINTKEY = E1.ENDPOINTKEY
                                    AND E1.ENDPOINTNAME = 'ABC'
                                    AND LASTLOGONDATE < DATE_SUB(NOW(),
                                                        INTERVAL 6 MONTH)); 

AND  ENTITLEMENT_VALUES.ENTITLEMENT_VALUE in  ("cn=abc,cn=org.cn=com","cn=wrete,cn=org.cn=com")


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

sandeepgudipudi
New Contributor III
New Contributor III

i modified the query with endpointkey but no data is found

 

SELECT ACCOUNTS.NAME,
ENTITLEMENT_VALUES.ENTITLEMENT_VALUE,
ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY AS entvaluekey,
ACCOUNTS.ACCOUNTKEY AS acctKey,
ENTITLEMENT_TYPES.DISPLAYNAME AS entitlement_type
FROM ACCOUNT_ENTITLEMENTS1
JOIN ACCOUNTS
ON ACCOUNT_ENTITLEMENTS1.ACCOUNTKEY = ACCOUNTS.ACCOUNTKEY
JOIN ENTITLEMENT_VALUES
ON ACCOUNT_ENTITLEMENTS1.ENTITLEMENT_VALUEKEY = ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY
JOIN ENTITLEMENT_TYPES
ON ENTITLEMENT_VALUES.ENTITLEMENTTYPEKEY = ENTITLEMENT_TYPES.ENTITLEMENTTYPEKEY
JOIN ENDPOINTS
ON ACCOUNTS.ENDPOINTKEY = ENDPOINTS.ENDPOINTKEY
WHERE ENDPOINTS.ENDPOINTKEY = 66
AND ACCOUNTS.NAME IN (SELECT NAME
FROM ACCOUNTS A1
JOIN ENDPOINTS E1
ON A1.ENDPOINTKEY = E1.ENDPOINTKEY
WHERE E1.ENDPOINTKEY = 63
AND A1.customproperty1 < DATE_SUB(NOW(), INTERVAL 6 MONTH))
AND ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY in (3328039)

Narrow down query and validate


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

sandeepgudipudi
New Contributor III
New Contributor III

Let me rephrase the requirement

From endpoint 66 we need to fetch entitlements for email[which is customproperty2] that matches withcustomproperty2[which is email] from endpoint 63 along with customproerty1 from endpoint 63 which hold lastlogondate, here customproprty2 has a common value in both endpoints

 

SELECT A.NAME,
       EV.ENTITLEMENT_VALUE,
       EV.ENTITLEMENT_VALUEKEY AS entvaluekey,
       A.ACCOUNTKEY            AS acctKey,
       ET.DISPLAYNAME          AS entitlement_type,
       A1.CUSTOMPROPERTY1      AS lastlogondate
FROM   ACCOUNT_ENTITLEMENTS1 AE
       JOIN ACCOUNTS A
         ON AE.ACCOUNTKEY = A.ACCOUNTKEY
       JOIN ENTITLEMENT_VALUES EV
         ON AE.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY
       JOIN ENTITLEMENT_TYPES ET
         ON EV.ENTITLEMENTTYPEKEY = ET.ENTITLEMENTTYPEKEY
       JOIN ENDPOINTS E
         ON A.ENDPOINTKEY = E.ENDPOINTKEY
       JOIN ACCOUNTS A1
         ON A.CUSTOMPROPERTY2 = A1.CUSTOMPROPERTY2
       -- Matching customproperty2 between both endpoints
       JOIN ENDPOINTS E1
         ON A1.ENDPOINTKEY = E1.ENDPOINTKEY
WHERE  E.ENDPOINTKEY = 66
       AND E1.ENDPOINTKEY = 63
       AND A1.CUSTOMPROPERTY1 < DATE_SUB(NOW(), INTERVAL 6 MONTH)
       AND EV.ENTITLEMENT_VALUEKEY IN ( 3328039 ); 


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

sandeepgudipudi
New Contributor III
New Contributor III

Still no data found, can you please check in your environment

 

SELECT A.NAME,
EV.ENTITLEMENT_VALUE,
EV.ENTITLEMENT_VALUEKEY AS entvaluekey,
A.ACCOUNTKEY AS acctKey,
ET.DISPLAYNAME AS entitlement_type,
A1.CUSTOMPROPERTY1 AS lastlogondate
FROM ACCOUNT_ENTITLEMENTS1 AE
JOIN ACCOUNTS A
ON AE.ACCOUNTKEY = A.ACCOUNTKEY
JOIN ENTITLEMENT_VALUES EV
ON AE.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY
JOIN ENTITLEMENT_TYPES ET
ON EV.ENTITLEMENTTYPEKEY = ET.ENTITLEMENTTYPEKEY
JOIN ENDPOINTS E
ON A.ENDPOINTKEY = E.ENDPOINTKEY
JOIN ACCOUNTS A1
ON A.CUSTOMPROPERTY2 = A1.CUSTOMPROPERTY2
JOIN ENDPOINTS E1
ON A1.ENDPOINTKEY = E1.ENDPOINTKEY
WHERE E.ENDPOINTKEY = 66
AND E1.ENDPOINTKEY = 63
AND A1.CUSTOMPROPERTY1 < DATE_SUB(NOW(), INTERVAL 6 MONTH)
AND EV.ENTITLEMENT_VALUEKEY IN ( 3328037 );

Narrow down query in small part and work. i dont have data and i need to build data so you validate by narrow dowing queyr


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

Validate basic query and date format

SELECT A.NAME,
       EV.ENTITLEMENT_VALUE,
       EV.ENTITLEMENT_VALUEKEY AS entvaluekey,
       A.ACCOUNTKEY            AS acctKey,
       ET.DISPLAYNAME          AS entitlement_type,
       A1.CUSTOMPROPERTY1      AS lastlogondate
FROM   ACCOUNT_ENTITLEMENTS1 AE
       JOIN ACCOUNTS A
         ON AE.ACCOUNTKEY = A.ACCOUNTKEY
       JOIN ENTITLEMENT_VALUES EV
         ON AE.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY
       JOIN ENTITLEMENT_TYPES ET
         ON EV.ENTITLEMENTTYPEKEY = ET.ENTITLEMENTTYPEKEY
       JOIN ENDPOINTS E
         ON A.ENDPOINTKEY = E.ENDPOINTKEY
       JOIN ACCOUNTS A1
         ON A.CUSTOMPROPERTY2 = A1.CUSTOMPROPERTY2
       JOIN ENDPOINTS E1
         ON A1.ENDPOINTKEY = E1.ENDPOINTKEY
WHERE  E.ENDPOINTKEY = 66
       AND E1.ENDPOINTKEY = 63

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

sandeepgudipudi
New Contributor III
New Contributor III

Iam getting error while executing the query, and with same query even Analytics is not getting created

sandeepgudipudi_0-1728658813427.png

 

You may have wrong joins validate and narrow down


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

sandeepgudipudi
New Contributor III
New Contributor III

I validated the one you provided, when i limit to specific user i can see the output, however with out condition still taking longer  and terminating the query

SELECT A.NAME AS ADAccountName,
A1.Name AS AtlassianAccountName,
A1.CUSTOMPROPERTY2 AS AccountEmail,
EV.ENTITLEMENT_VALUE,
EV.ENTITLEMENT_VALUEKEY AS entvaluekey,
A.ACCOUNTKEY AS acctKey,
ET.DISPLAYNAME AS entitlement_type,
A1.CUSTOMPROPERTY1 AS lastlogondate
FROM ACCOUNT_ENTITLEMENTS1 AE
JOIN ACCOUNTS A
ON AE.ACCOUNTKEY = A.ACCOUNTKEY
JOIN ENTITLEMENT_VALUES EV
ON AE.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY
JOIN ENTITLEMENT_TYPES ET
ON EV.ENTITLEMENTTYPEKEY = ET.ENTITLEMENTTYPEKEY
JOIN ENDPOINTS E
ON A.ENDPOINTKEY = E.ENDPOINTKEY
JOIN ACCOUNTS A1
ON A.CUSTOMPROPERTY2 = A1.CUSTOMPROPERTY2
JOIN ENDPOINTS E1
ON A1.ENDPOINTKEY = E1.ENDPOINTKEY
WHERE E.ENDPOINTKEY = 66
AND E1.ENDPOINTKEY = 63 and A1.customproperty2='cjhjd@xyz.net'

Accounts CP2 is not indexed hence it will take longer time.


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

What is the solution to achieve my requirement and its taking longer and terminating

  • Check if support team if index can be added  
  • or change cp2 column to indexed column 

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

sandeepgudipudi
New Contributor III
New Contributor III

Hi I build that is fetching the ent values, now iam looking for an analytics query to revoke access for the specific ent values

SELECT ACCOUNTS.NAME as ADAccount,
A1.Name AS AtlassianAccountName,
A1.customproperty1 AS lastlogondate
FROM ACCOUNT_ENTITLEMENTS1
JOIN ACCOUNTS
ON ACCOUNT_ENTITLEMENTS1.ACCOUNTKEY = ACCOUNTS.ACCOUNTKEY
JOIN ENTITLEMENT_VALUES
ON ACCOUNT_ENTITLEMENTS1.ENTITLEMENT_VALUEKEY = ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY
JOIN ENTITLEMENT_TYPES
ON ENTITLEMENT_VALUES.ENTITLEMENTTYPEKEY = ENTITLEMENT_TYPES.ENTITLEMENTTYPEKEY
JOIN ENDPOINTS
ON ACCOUNTS.ENDPOINTKEY = ENDPOINTS.ENDPOINTKEY
JOIN (SELECT NAME, customproperty1, ENDPOINTKEY
FROM ACCOUNTS
WHERE ENDPOINTKEY = 67
AND customproperty1 < DATE_SUB(NOW(), INTERVAL 6 MONTH)) A1
ON ACCOUNTS.customproperty16 = A1.NAME
WHERE ENDPOINTS.ENDPOINTKEY = 66 AND ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY IN ('3328036','3328037','3328038','3328039');

Does query giving required results ?


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

sandeepgudipudi
New Contributor III
New Contributor III

For now yes, this is fetching the internal user that are matching values from both endpoints.

SELECT ACCOUNTS.NAME as ADAccount,
A1.Name AS AtlassianAccountName,
A1.customproperty1 AS lastlogondate,
'Deprovision Access' as 'Default_Action_For_Analytics' ,
ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY as entvaluekey,
A1.accountkey as acctKey
FROM ACCOUNT_ENTITLEMENTS1
JOIN ACCOUNTS
ON ACCOUNT_ENTITLEMENTS1.ACCOUNTKEY = ACCOUNTS.ACCOUNTKEY
JOIN ENTITLEMENT_VALUES
ON ACCOUNT_ENTITLEMENTS1.ENTITLEMENT_VALUEKEY = ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY
JOIN ENTITLEMENT_TYPES
ON ENTITLEMENT_VALUES.ENTITLEMENTTYPEKEY = ENTITLEMENT_TYPES.ENTITLEMENTTYPEKEY
JOIN ENDPOINTS
ON ACCOUNTS.ENDPOINTKEY = ENDPOINTS.ENDPOINTKEY
JOIN (SELECT NAME, customproperty1, ENDPOINTKEY
FROM ACCOUNTS
WHERE ENDPOINTKEY = 67
AND customproperty1 < DATE_SUB(NOW(), INTERVAL 6 MONTH)) A1
ON ACCOUNTS.customproperty16 = A1.NAME
WHERE ENDPOINTS.ENDPOINTKEY = 66 AND ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY IN ('3328036','3328037','3328038','3328039');


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

sandeepgudipudi
New Contributor III
New Contributor III

Hi,

Deprovision access should be on endpoint 66 but its fetching endpoint 67

SELECT ACCOUNTS.NAME as ADAccount,
A1.Name AS AtlassianAccount,
A1.customproperty1 AS lastlogondate,
ENTITLEMENT_VALUES.ENTITLEMENT_VALUE as Entitlements,
'Deprovision Access' as 'Default_Action_For_Analytics' ,
ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY as entvaluekey,
A1.accountkey as acctKey
FROM ACCOUNT_ENTITLEMENTS1
JOIN ACCOUNTS
ON ACCOUNT_ENTITLEMENTS1.ACCOUNTKEY = ACCOUNTS.ACCOUNTKEY
JOIN ENTITLEMENT_VALUES
ON ACCOUNT_ENTITLEMENTS1.ENTITLEMENT_VALUEKEY = ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY
JOIN ENTITLEMENT_TYPES
ON ENTITLEMENT_VALUES.ENTITLEMENTTYPEKEY = ENTITLEMENT_TYPES.ENTITLEMENTTYPEKEY
JOIN ENDPOINTS
ON ACCOUNTS.ENDPOINTKEY = ENDPOINTS.ENDPOINTKEY
JOIN (SELECT NAME, customproperty1, accountkey, ENDPOINTKEY
FROM ACCOUNTS
WHERE ENDPOINTKEY = 67
AND customproperty1 < DATE_SUB(NOW(), INTERVAL 7 DAY)) A1
ON ACCOUNTS.customproperty16 = A1.NAME
WHERE ENDPOINTS.ENDPOINTKEY = 66 AND ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY IN ('3328037');

SELECT 
    ACCOUNTS.NAME as ADAccount,
    A1.Name AS AtlassianAccount,
    A1.customproperty1 AS lastlogondate,
    ENTITLEMENT_VALUES.ENTITLEMENT_VALUE as Entitlements,
    'Deprovision Access' as 'Default_Action_For_Analytics',
    ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY as entvaluekey,
    A1.accountkey as acctKey
FROM 
    ACCOUNT_ENTITLEMENTS1
JOIN 
    ACCOUNTS ON ACCOUNT_ENTITLEMENTS1.ACCOUNTKEY = ACCOUNTS.ACCOUNTKEY
JOIN 
    ENTITLEMENT_VALUES ON ACCOUNT_ENTITLEMENTS1.ENTITLEMENT_VALUEKEY = ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY
JOIN 
    ENTITLEMENT_TYPES ON ENTITLEMENT_VALUES.ENTITLEMENTTYPEKEY = ENTITLEMENT_TYPES.ENTITLEMENTTYPEKEY
JOIN 
    ENDPOINTS ON ACCOUNTS.ENDPOINTKEY = ENDPOINTS.ENDPOINTKEY
JOIN 
    (SELECT 
         NAME, customproperty1, accountkey, ENDPOINTKEY 
     FROM 
         ACCOUNTS 
     WHERE 
         ENDPOINTKEY = 67 
         AND customproperty1 < DATE_SUB(NOW(), INTERVAL 7 DAY)
    ) A1 
    ON ACCOUNTS.customproperty16 = A1.NAME
WHERE 
    ENDPOINTS.ENDPOINTKEY = 66 
    AND ENTITLEMENT_VALUES.ENTITLEMENT_VALUEKEY IN ('3328037');

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

sandeepgudipudi
New Contributor III
New Contributor III

Still Remove task is creating with endpoint 67 and usersname is populating as admin, ideally useraname should fetch from endpoint 66 and deprovision access should be from endpoint 66

sandeepgudipudi_0-1729281347368.png

 

What is visible in analytics result?


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