Click HERE to see how Saviynt Intelligence is transforming the industry. |
10/09/2024 07:43 AM
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"
10/09/2024 09:11 AM
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));
10/09/2024 09:45 AM
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")
10/09/2024 10:29 AM - edited 10/09/2024 10:39 AM
@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
10/09/2024 10:39 AM - edited 10/09/2024 10:40 AM
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")
10/09/2024 10:17 PM
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)
10/09/2024 10:45 PM
Narrow down query and validate
10/10/2024 08:55 AM
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
10/10/2024 09:22 AM
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 );
10/10/2024 09:27 AM
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 );
10/10/2024 09:28 AM
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
10/10/2024 09:30 AM
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
10/11/2024 08:00 AM
Iam getting error while executing the query, and with same query even Analytics is not getting created
10/11/2024 08:07 AM
You may have wrong joins validate and narrow down
10/14/2024 07:29 AM
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'
10/14/2024 10:12 AM
Accounts CP2 is not indexed hence it will take longer time.
10/15/2024 07:31 AM
What is the solution to achieve my requirement and its taking longer and terminating
10/15/2024 08:16 AM
10/17/2024 09:06 AM
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');
10/17/2024 09:33 AM
Does query giving required results ?
10/17/2024 09:48 AM
For now yes, this is fetching the internal user that are matching values from both endpoints.
10/17/2024 10:37 AM
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');
10/18/2024 10:07 AM
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');
10/18/2024 11:12 AM
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');
10/18/2024 12:55 PM
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
10/18/2024 03:57 PM
What is visible in analytics result?