Need tasktype for Remove account

[Souvik]

We have a requirement to generate a dashboard report for X application when any user "X" account is removed (Leaver event for X account is triggered and remove account task created).We need to get all the user details for whom X application remove account task got processed.

We tried the below query:

select a.taskkey as "tasksid", e1.username,e1.firstname,e1.lastname,u1.entitlement_value from arstasks a join entitlement_values u1 on a.ENTITLEMENT_VALUEKEY = u1.ENTITLEMENT_VALUEKEY join users e1 on a.userkey =e1.userkey where a.endpoint = 29 and a.tasktype=2 and a.status=3

However this query fetches remove access/remove account both request id details.

I went through Queries for generating the Arstasks dashboard report : Customer Portal (freshdesk.com) Link but didnt find any suitable tasktype for remove account.

Can any one please help me out.

Thanks in Advance

[Saathvik]

Tasktype =8 is for Delete Account

Tasktype =2 is for Remove Access.

If you want user details of revoke Account then use tasktype as 8 but don't join with entitlements table

[Saathvik]

Below query will give users whose access removed and account revoked along with task type

select a.taskkey as "tasksid", case when a.entitlement_value is not null then 'REMOVE ACCESS' when a.entitlement_value is null then 'REVOKE ACCOUNT' else a.tasktype end as Task_TYPE, u.username, u.firstname, u.lastname, ev.entitlement_value from arstasks a inner join users u on a.userkey =u.userkey left join entitlement_values ev on a.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY where a.endpoint = 29 and a.tasktype in (2,8) and a.status=3 

Below query will give users whose account is revoked

select a.taskkey as "tasksid", case when a.entitlement_value is not null then 'REMOVE ACCESS' when a.entitlement_value is null then 'REVOKE ACCOUNT' else a.tasktype end as Task_TYPE, u.username, u.firstname, u.lastname from arstasks a inner join users u on a.userkey =u.userkey where a.endpoint = 29 and a.tasktype in (2,8) and a.status=3 and a.entitlement_valuekey is null

[avinashchhetri · Accepted solution]

@Souvik,

Remove Account and Remove Access both have the tasktype as 2 with the only difference being that Remove Access also has the entitlement_valuekey populated in the arstasks table.

If you just need to get the details of the accounts that were revoked, you need not join the entitlement_values table, just query the users, accounts and the arstasks table with the condition that arstasks.tasktype=2 and arstasks.entitlement_valuekey is null and ars.status=3

[Saathvik]

@avinashchhetri : Don't we have to include tasktype 8 as well? If not when does tasktype 8 occurs?

[avinashchhetri]

@Saathvik, It depends on how you have configured your user update rules.

If Deprovision Access was the action in the rule, then it always creates a Remove Account task and if Im not mistaken, Disable User Accounts results in tasktype 8.

[Saathvik]

Will the task type changes when they trigger from rules? I know for Deprovision Access it has three options Account only, Access only or Account and Access. will that make task type change compared to requests? Just trying to understand the behaviour

Because as per schema guide 2 - remove access, 8 - delete account, 14- Disable Account

[avinashchhetri]

Deprovision Access should always give you tasktype 2. Whether its for an account or entitlement will be determined by the value in Entitlement_valuekey as discussed in the thread above.

[rushikeshvartak]

select a.taskkey as "tasksid", e1.username,e1.firstname,e1.lastname,u1.entitlement_value,
case when u1.entitlement_value is null then 'Remove Account' else 'Remove Access' end as taskkttype
from arstasks a left join entitlement_values u1 on a.ENTITLEMENT_VALUEKEY = u1.ENTITLEMENT_VALUEKEY join users e1 on a.userkey =e1.userkey where a.endpoint = 2 and a.tasktype=29 and a.status=3

[Souvik · Accepted solution]

Thank you all for your response.

The below query worked.

select a.taskkey as "tasksid", e1.username,e1.firstname,e1.lastname from arstasks a join users e1 on a.userkey =e1.userkey where a.endpoint = 29 and a.tasktype=2 and a.entitlement_valuekey is null and a.status=3