SQL Query Analytics for list of accounts having no entitlements except one entitlement_type

[PKSAKS]

Hi Team ,

In Salesforce application there is one entitlementtype i.e. PROFILE and entitlements of which cannot be removed( Service account does not have the access). So we need to check if an account is not having any entitlements other than entitlement type Profile then we need to disable that account.
Note: Entitlement type profile includes license entitlementvalue so it is a mandatory entitltmentype and user will always be having.
I am trying to achieve this usecase by actionable analytics. I have tried applying different logic but still it is failing.

Can someone please help here.

I have tried this 

select a.name from accounts a where a.name not in(SELECT a.name
FROM accounts A
JOIN endpoints E
ON A.endpointkey = E.endpointkey
JOIN account_entitlements1 AE1
ON AE1.accountkey = A.accountkey
JOIN entitlement_values EV
ON EV.entitlement_valuekey = AE1.entitlement_valuekey
JOIN entitlement_types ET
ON ET.entitlementtypekey = EV.entitlementtypekey
WHERE E.endpointname = 'SALESFORCE DEV'
AND A.status IN ( 1, 'MANUALLY PROVISIONED', 'ACTIVE' )
AND A.NAME NOT LIKE '%DELETED%'
AND ET.entitlementname NOT in ('PROFILE')
GROUP BY A.NAME) and a.endpointkey=32 AND A.NAME NOT LIKE '%DELETED%' AND A.status IN ( 1, 'MANUALLY PROVISIONED', 'ACTIVE' )

But it is not giving correct results.

[This message has been edited by moderator to merge reply comment]

[rushikeshvartak]

Use below query

select * from(SELECT a.name,group_concat(ev.entitlement_Value)ev,group_concat(distinct et.entitlementname)enttype
FROM accounts A
LEFT JOIN endpoints E
ON A.endpointkey = E.endpointkey
JOIN account_entitlements1 AE1
ON AE1.accountkey = A.accountkey
JOIN entitlement_values EV
ON EV.entitlement_valuekey = AE1.entitlement_valuekey
JOIN entitlement_types ET
ON ET.entitlementtypekey = EV.entitlementtypekey
WHERE E.endpointname = 'Salesforce-Rushi'
AND A.status IN ( 1, 'MANUALLY PROVISIONED', 'ACTIVE' )
AND A.NAME NOT LIKE '%DELETED%'
GROUP BY A.NAME)data where enttype in ('Profile')

[Rajesh-R]

@PKSAKS 

Appreciate your question.

You can use analytics to trigger the Disable Account Task. There are two options of disablement.

1. inactivate the account and keep the profile with subscription.

2. Keep the account active + remove the profile with subscription and assign a profile which does not need any subscription.

Salesforce always comes with a base profile and there is a profile id for the base profile. This is automatically handled by the product, if you remove the last profile, system will always assign the base profile id / link it to a profile which has no license impact and keep the account active.

CUSTOMCONFIGJSON:

{
"defaultEntitlementId":"00e5I000000ta0eQAA"
}

Refer: https://docs.saviyntcloud.com/bundle/Salesforce-v23x/page/Content/Specifying-the-Custom-Configuratio...