SQL - Ownership Transfer

 Select UserData.GroupName,

CASE WHEN (UserData.OldOwnerUsername!='' or UserData.OldOwnerUsername is not NULL) THEN UserData.OldOwnerUsername

ELSE 'Not Present' END as 'OldOwnerUsername',

CASE WHEN (NewAccountData.NewOwnerUserName!='' or NewAccountData.NewOwnerUserName is not NULL) THEN NewAccountData.NewOwnerUserName

ELSE 'Not Present' END as 'NewOwnerUserName',

CASE WHEN (OldAccountData.OldOwnerObjectID!='' or OldAccountData.OldOwnerObjectID is not NULL) THEN OldAccountData.OldOwnerObjectID

ELSE 'Not Present' END  as 'OldOwnerObjectID',

CASE WHEN (NewAccountData.NewOwnerObjectID!='' or NewAccountData.NewOwnerObjectID is not NULL) THEN NewAccountData.NewOwnerObjectID

ELSE 'Not Present' END  as 'NewOwnerObjectID',

UserData.GroupObjectID,

UserData.EntitlementKey,

UserData.EndpointName


FROM

(Select u1.username as 'OldOwnerUsername',

u2.userkey as 'NewOwner_Userkey',

u2.username as 'OwnerOnTerminate',

EV.ENTITLEMENT_VALUEKEY as 'EntitlementKey',

EV.ENTITLEMENT_VALUE as 'GroupName',

EV.ENTITLEMENTID as 'GroupObjectID',

EO.RANK as 'OwnerRank',

EP.ENDPOINTNAME as 'EndpointName'


from

users u1 INNER JOIN  users u2 ON u2.userkey=u1.owneronterminate

INNER JOIN entitlement_owners EO On EO.USERKEY=u1.userkey

INNER JOIN entitlement_values EV ON EV.ENTITLEMENT_VALUEKEY=EO.ENTITLEMENT_VALUEKEY

INNER JOIN entitlement_types ET ON ET.ENTITLEMENTTYPEKEY=EV.ENTITLEMENTTYPEKEY

INNER JOIN endpoints EP on EP.ENDPOINTKEY=ET.ENDPOINTKEY


where

u1.customproperty64 in ('Terminated','Mover Initiated')

and EO.RANK=1

and EP.ENDPOINTNAME in ('Azure_AD_MasterTest-Primary Accounts')

and ET.ENTITLEMENTNAME='AADGroup'

and u1.statuskey=1

and ev.status=1

and ev.EntitlementID is not NULL

and (ev.customproperty11 is null or  ev.customproperty11 not like 'Dynamic%' and ev.customproperty11 not like 'Unified') 

and  (ev.customproperty8 !='true' or  ev.customproperty8 is NULL)

and  (ev.customproperty17 !='true' or  ev.customproperty17 is NULL)


and ev.entitlement_value in ('TestGroupCreation28','TestGroupCreation30','TestGroupCreation45','TestGroupCreation60','Azure-Test-Office365-Contributor-TestNewPOC004','MARCTestAutomationGroup22','MARCTestAutomationGroup26','MARCTestAutomationGroup29')


GROUP BY EntitlementKey ) UserData


LEFT JOIN


(Select 

u1.username as 'OldOwnerUserName', 

E.ENDPOINTNAME as 'OldApplicationName',

A1.name as 'OldOwnerAccntName', 

a1.accountID as 'OldOwnerObjectID', 

a1.status as 'OldAccountStatus'



from

users u1 INNER JOIN user_accounts UA1 ON UA1.USERKEY=u1.Userkey

INNER JOIN  accounts A1 ON A1.ACCOUNTKEY=UA1.ACCOUNTKEY

INNER JOIN Endpoints E ON E.ENDPOINTKEY=A1.ENDPOINTKEY


where

u1.customproperty64 in ('Terminated','Mover Initiated')

and A1.Status in (1,'Manually Provisioned')

and a1.accountID is NOT NULL

and E.ENDPOINTNAME in ('Azure_AD_MasterTest-Primary Accounts')


Group by OldOwnerUserName




)  OldAccountData


ON UserData.OldOwnerUsername=OldAccountData.OldOwnerUserName



LEFT JOIN


(

Select u1.username as 'OldOwnerUserName', 

u2.username as 'NewOwnerUserName', 

E.ENDPOINTNAME as 'OldApplicationName',

A2.name as 'NewOwnerAccntName', 

a2.accountID as 'NewOwnerObjectID', 

a2.status as 'NewAccountStatus'



from

users u1  INNER JOIN  users u2 ON u2.userkey=u1.owneronterminate

INNER JOIN user_accounts UA2 ON UA2.USERKEY=u2.Userkey

INNER JOIN  accounts A2 ON A2.ACCOUNTKEY=UA2.ACCOUNTKEY

INNER JOIN Endpoints E ON E.ENDPOINTKEY=A2.ENDPOINTKEY


where

u1.customproperty64 in ('Terminated','Mover Initiated')

and A2.Status in (1,'Manually Provisioned')

and a2.accountID is NOT NULL

and  E.ENDPOINTNAME in ('Azure_AD_MasterTest-Primary Accounts')


GROUP BY OldOwnerUserName

)  NewAccountData


ON UserData.OldOwnerUsername=NewAccountData.OldOwnerUserName;

Comments

Post a Comment

Popular posts from this blog

Enhanced Query - To Update Account Status and Name

select accountkey as accounts__primarykey, 'SUSPENDED FROM IMPORT SERVICE' as accounts__status, CONCAT(name,'-Deleted on-', DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'))as accounts__name from accounts where endpointkey=527 and name='CN=INRVCH,OU=User Accounts Vista,OU=User Accounts,OU=User Directory,DC=asia-pac,DC=xx,DC=com'  and status not in ('SUSPENDED FROM IMPORT SERVICE') limit 1;
Read more

Connection Config - STATUS_THRESHOLD_CONFIG

 {   "statusAndThresholdConfig": {    "inactivateAccountsNotInFile": true    "statusColumn":"userlock",    "activeStatus":["0","128","192"],     "accountThresholdValue": 500,     "deleteLinks": false,     "correlateInactiveAccounts": true,        } } ----------------------------------------------------------------------------- When account import run above STATUS_THRESHOLD_CONFIG is used to check. ----------------------------------- inactivateAccountsNotInFile  ==> True/false true ---> Account status 1/2 ( Active/Inactive) Day 1 --> 100 Accounts import Day 2 --> 90 Diff= 10  ( 90= Active , 10 Inactive)  ------------------------------------------------------------------------------ ----------------------------------- inactivateAccountsNotInFile  ==> True/false false ---> Account status 1/SFIS ( Active/SUSPENDED FROM IMPORT SERVICE.) Day 1 --> 100 Ac...
Read more

Email variables _ Register User

ServiceAccountOwnerMap ------- [:] entitlements ------- [] accessapprovers ------- com.saviynt.ecm.workflow.Access_Approvers : 812 jbpm_activity_name ------- Third Party Registration Approvers allApproversComments ------- null fullrowhtmltablerows ------- listofallrolesinrequest ------- [] approvedEntitlementsOwners ------- [] entitlementshtmltablerowsv2 ------- entitlementshtmltablerowsv3 ------- ServiceAccountType ------- requestlink ------- null/jbpmworkflowmanagement/showrequestdetails/TP Registration Approvals.553050?reqid=433 allRejectorsComments ------- null rolesApprovedOrRejected ------- [] rolename ------- request_access ------- com.saviynt.ecm.workflow.Request_Access : 456 users ------- XXXX allRejectors ------- [] requestid ------- 553050 rolemap ------- [:] baseUrlForEmail ------- https://dev.saviyntcloud.com/ECM assignee ------- XXXX activityname ------- Third Party Registration Approvers ServiceAccountFlag ------- false request ------- com.saviynt.ecm.workflow.ARS_Reques...
Read more