Query Entitlement Metadata

SELECT IFNULL(EV.ENTITLEMENT_VALUE, 'blank') AS 'Role Name', IFNULL(EV.DISPLAYNAME, 'blank') AS 'Role Display Name', IFNULL(EP.ENDPOINTNAME, 'blank') AS 'EndpointName', IFNULL( ( CASE WHEN EV.RISK = 0 THEN 'None' WHEN EV.RISK = 1 THEN 'Very Low' WHEN EV.RISK = 2 THEN 'Low' WHEN EV.RISK = 3 THEN 'Medium' WHEN EV.RISK = 4 THEN 'High' WHEN EV.RISK = 5 THEN 'Critical' END ), 'blank' ) AS 'Risk', IFNULL(EV.DESCRIPTION, 'blank') AS 'Description', IFNULL(U.USERNAME, 'blank') AS 'Role Owner', IFNULL(EO.RANK, 'blank') AS 'Owner Rank', IFNULL(CONCAT(U.FIRSTNAME, " ",U.LASTNAME), 'blank') AS 'Role Owner Full Name', IFNULL( ( CASE WHEN EV.STATUS = 0 THEN 'None' WHEN EV.STATUS = 1 THEN 'Active' WHEN EV.STATUS = 2 THEN 'Inactive' WHEN EV.STATUS = 3 THEN 'Decommission Active' WHEN EV.STATUS = 4 THEN 'Decommission Inactive' END ), 'blank' ) AS 'Role Status', IFNULL (EV.CUSTOMPROPERTY1, 'blank') as 'Standard Account Requestable CP1',IFNULL (EV.CUSTOMPROPERTY24, 'blank') as 'Service Account Requestable CP24',IFNULL (EV.CUSTOMPROPERTY25, 'blank') as 'Endpoint Name CP25',ifnull( CASE when U.STATUSKEY =0 then 'INACTIVE' WHEN U.STATUSKEY =1 then 'ACTIVE' END, 'blank') as 'Role Owner Status',IFNULL (EV.CUSTOMPROPERTY16, 'blank') as 'Actual CP16',( select case when group_concat(distinct r.RULESET) is not null then concat('Yes - ',group_concat(distinct r.RULESET)) else 'No' end as 'Actual' from function_entitlements fe, functions f ,rulesets r where fe.ENTITLEMENT_VALUEKEY=ev.ENTITLEMENT_VALUEKEY and f.functionkey=fe.functionkey and r.RULESETKEY=f.RULESETKEY and f.status =1 and r.DEFAULTRULESET=1) as 'Expected CP16',IFNULL(ET.ENTITLEMENTNAME, 'blank') AS 'Role Type' FROM SECURITYSYSTEMS SS JOIN ENDPOINTS EP ON SS.SYSTEMKEY = EP.SECURITYSYSTEMKEY AND UPPER(EP.ENDPOINTNAME)=UPPER('BLACKPOINT') JOIN ENTITLEMENT_TYPES ET ON ET.ENDPOINTKEY = EP.ENDPOINTKEY JOIN ENTITLEMENT_VALUES  EV ON ET.ENTITLEMENTTYPEKEY = EV.ENTITLEMENTTYPEKEY LEFT JOIN ENTITLEMENT_OWNERS EO ON EV.ENTITLEMENT_VALUEKEY = EO.ENTITLEMENT_VALUEKEY LEFT JOIN USERS U ON EO.USERKEY = U.USERKEY

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