whats the query for searching the accounts who are having two or more groups assigned?

[Pratham]

Hi team, I have to write actionable analytics report to shows the accounts who are having 2 or more groups assigned to them and then we have to deprovision their access. We have filter the accounts by endpointname. 

[rushikeshvartak]

Share current query

[Pratham]

Thanks for the reply, Rushikesh.

select
a.name as 'Accname',
u.username as 'Username',
e.endpointname as 'EndpointName',
a.ACCOUNTID as 'DistinguishedName',
CASE u.STATUSKEY WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' END AS USERSTATUS,
ev.entitlement_value as 'Groups',
ae1.accountkey as 'acctKey',
'Deprovision Access' as 'Default_Action_For_Analytics',
ae1.entitlement_valuekey as 'entvaluekey'

from users u join user_accounts ua on u.userkey=ua.userkey
join accounts a on a.accountkey=ua.accountkey
join account_entitlements1 ae1 on a.accountkey=ae1.accountkey
join entitlement_values ev on ev.entitlement_valuekey=ae1.entitlement_valuekey
join entitlement_types et on et.entitlementtypekey=ev.entitlementtypekey
join endpoints e on e.endpointkey=et.endpointkey
where e.endpointname='TETRAAD'

How to find that accounts which have multiple groups assigned to them from that endpointname?

we have to perform deprovision access task for that accounts who have 2 or more groups assigned, we have to remove other groups which are not identified with the above endpoint name. 

[pmahalle]

@Pratham ,

User below query:

select
a.name as 'Accname',
u.username as 'Username',
e.endpointname as 'EndpointName',
a.ACCOUNTID as 'DistinguishedName',
CASE u.STATUSKEY WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' END AS USERSTATUS,
ev.entitlement_value as 'Groups',
ae1.accountkey as 'acctKey',
'Deprovision Access' as 'Default_Action_For_Analytics',
ae1.entitlement_valuekey as 'entvaluekey'
from users u join user_accounts ua on u.userkey=ua.userkey
join accounts a on a.accountkey=ua.accountkey
join account_entitlements1 ae1 on a.accountkey=ae1.accountkey
join entitlement_values ev on ev.entitlement_valuekey=ae1.entitlement_valuekey
join entitlement_types et on et.entitlementtypekey=ev.entitlementtypekey
join endpoints e on e.endpointkey=et.endpointkey
where e.endpointname='TETRAAD' and a.accountkey in (select tab.accountkey from (select ae1.accountkey as accountkey, count(ae1.accountkey) as entitlementassigned from account_entitlements1 ae1, accounts a, endpoints e where a.accountkey=ae1.accountkey and a.endpointkey=e.endpointkey and e.endpointname='TETRAAD' and a.status=1 group by ae1.accountkey) tab where tab.entitlementassigned > 1)

[Pratham]

Hi Pandharinath, The above query was working but it shows no records found when I change endpoint it working it shows some accounts that have multiple groups assigned. How can we check the account exist only in the defined/ identified group match pattern?

The main query was have to Create actionable analytics to identify the accounts falling outside the Tier2 groups and remove the account form the non-matched pattern groups, Tier2 accounts should exist only in the defined/ identified group match pattern

[pmahalle]

@Pratham ,

Do you mean you want to remove only specific groups/entitlements and not all?

[Pratham]

Yes, we have to remove the non-matched groups which are not mentioned in that endpoints, If any account has groups of other endpoints we have to remove those groups. Not all groups we have to remove only other endpoints groups from that. 

[rushikeshvartak]

Can you explain with example

[Pratham]

Hi @rushikeshvartak ,

Yes sure, we have to remove the non-matched groups which are not mentioned in that endpoints, If any account has groups of other endpoints we have to remove those groups. Not all groups we have to remove only other endpoints groups from that. For example : we have one endpoint name =TETRAAD and in that there are 11 entitlement values (groups) and 1000 accounts and each account has one or two groups assigned , we means contractor, users, admin, Maintenace, etc. these are the groups as per the request we have to find that accounts which are non-matched groups which are not mentioned in that endpoints ,we have to remove that accounts it can be groups of other endpoints also we have to find that and remove that one only.

[rushikeshvartak]

SELECT
    a.name AS 'Accname',
    u.username AS 'Username',
    e.endpointname AS 'EndpointName',
    a.ACCOUNTID AS 'DistinguishedName',
    CASE u.STATUSKEY 
        WHEN '0' THEN 'INACTIVE' 
        WHEN '1' THEN 'ACTIVE' 
    END AS USERSTATUS,
    ev.entitlement_value AS 'Groups',
    ae1.accountkey AS 'acctKey',
    'Deprovision Access' AS 'Default_Action_For_Analytics',
    ae1.entitlement_valuekey AS 'entvaluekey'
FROM users u
JOIN user_accounts ua ON u.userkey = ua.userkey
JOIN accounts a ON a.accountkey = ua.accountkey
JOIN account_entitlements1 ae1 ON a.accountkey = ae1.accountkey
JOIN entitlement_values ev ON ev.entitlement_valuekey = ae1.entitlement_valuekey
JOIN entitlement_types et ON et.entitlementtypekey = ev.entitlementtypekey
JOIN endpoints e ON e.endpointkey = et.endpointkey
WHERE e.endpointname = 'TETRAAD'
AND a.accountkey IN (
    SELECT a.accountkey
    FROM accounts a
    JOIN account_entitlements1 ae1 ON a.accountkey = ae1.accountkey
    JOIN entitlement_values ev ON ev.entitlement_valuekey = ae1.entitlement_valuekey
    JOIN entitlement_types et ON et.entitlementtypekey = ev.entitlementtypekey
    JOIN endpoints e ON e.endpointkey = et.endpointkey
    WHERE e.endpointname = 'TETRAAD'
    GROUP BY a.accountkey
    HAVING COUNT(DISTINCT ev.entitlement_valuekey) = (
        SELECT COUNT(DISTINCT ev.entitlement_valuekey)
        FROM account_entitlements1 ae1
        JOIN entitlement_values ev ON ev.entitlement_valuekey = ae1.entitlement_valuekey
        JOIN entitlement_types et ON et.entitlementtypekey = ev.entitlementtypekey
        JOIN endpoints e ON e.endpointkey = et.endpointkey
        WHERE e.endpointname = 'TETRAAD'
          AND ae1.accountkey = a.accountkey
    )
)

[Pratham]

Hi @rushikeshvartak ,

Thanks for the quick response.

The previous query functioned correctly, but it displayed all accounts, even those with only one group assigned to them. We need to filter and sort the accounts to show only those with multiple groups, in accordance with the specified conditions.

[rushikeshvartak]

Share query output and which data should be hidden with screenshot

[Pratham]

Hi @rushikeshvartak , @pmahalle 

The above problem was resolved.

can you please check the below query,

we have to Deprovision access of other entitlement values except the given one only, does it work in that case or not, if not can you please make some changes for that one, is it compulsory to add a.endpoinkey for deprovisioning access

select
a.name as 'Accname',
u.username as 'Username',
e.endpointname as 'EndpointName',
ev.entitlement_value as 'Groups',
CASE u.STATUSKEY WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' END AS USERSTATUS,
a.ACCOUNTID as 'DistinguishedName',
ae1.accountkey as 'acctKey',
'Deprovision Access' as 'Default_Action_For_Analytics',
ae1.entitlement_valuekey as 'entvaluekey'
from users u join user_accounts ua on u.userkey=ua.userkey
join accounts a on a.accountkey=ua.accountkey
join account_entitlements1 ae1 on a.accountkey=ae1.accountkey
join entitlement_values ev on ev.entitlement_valuekey=ae1.entitlement_valuekey
join entitlement_types et on et.entitlementtypekey=ev.entitlementtypekey
join endpoints e on e.endpointkey=et.endpointkey
where e.endpointname='TETRAAD' and ev.entitlement_value !='CN=SG-SAVAD-Test-App89-Users,OU=Saviynt-Dev,DC=tetra,DC=com'and
a.accountkey in (select tab.accountkey from (select ae1.accountkey as accountkey, count(ae1.accountkey) as entitlementassigned from account_entitlements1 ae1, accounts a, endpoints e where a.accountkey=ae1.accountkey and a.endpointkey=e.endpointkey and e.endpointname='TETRAAD' and a.status=1 group by ae1.accountkey) tab where tab.entitlementassigned >1) 

[rushikeshvartak]

SELECT
a.name AS 'Accname',
u.username AS 'Username',
e.endpointname AS 'EndpointName',
ev.entitlement_value AS 'Groups',
CASE u.STATUSKEY
WHEN '0' THEN 'INACTIVE'
WHEN '1' THEN 'ACTIVE'
END AS USERSTATUS,
a.ACCOUNTID AS 'DistinguishedName',
ae1.accountkey AS 'acctKey',
'Deprovision Access' AS 'Default_Action_For_Analytics',
ae1.entitlement_valuekey AS 'entvaluekey'
FROM
users u
JOIN
user_accounts ua ON u.userkey = ua.userkey
JOIN
accounts a ON a.accountkey = ua.accountkey
JOIN
account_entitlements1 ae1 ON a.accountkey = ae1.accountkey
JOIN
entitlement_values ev ON ev.entitlement_valuekey = ae1.entitlement_valuekey
JOIN
entitlement_types et ON et.entitlementtypekey = ev.entitlementtypekey
JOIN
endpoints e ON e.endpointkey = et.endpointkey
WHERE
e.endpointname = 'TETRAAD'
AND ev.entitlement_value != 'CN=SG-SAVAD-Test-App89-Users,OU=Saviynt-Dev,DC=tetra,DC=com'
AND a.accountkey IN (
SELECT
tab.accountkey
FROM (
SELECT
ae1.accountkey AS accountkey,
COUNT(ae1.accountkey) AS entitlementassigned
FROM
account_entitlements1 ae1
JOIN
accounts a ON a.accountkey = ae1.accountkey
JOIN
endpoints e ON a.endpointkey = e.endpointkey
WHERE
e.endpointname = 'TETRAAD'
AND a.status = 1
GROUP BY
ae1.accountkey
) tab
WHERE
tab.entitlementassigned > 1
);

[Pratham]

Hi @rushikeshvartak ,

The above query is working fine. I have one doubt this query will Deprovision access of these accounts or the groups from that account?

[rushikeshvartak]

Group

✅Please click the 'Accept As Solution' button on the reply (or replies) that best answered your original question and hit 'Kudos' button 👍.