This Query can be used to get the Access review details which is assigned to the Reviewer. This will contain the basic information of the Access Review and will not contain any information related to the identity details which is to be reviewed.
SELECT
SPT_CERTIFICATION_DEFINITION.NAME AS "CERTIFICATION DEFINITION NAME",
SHORT_NAME AS "CERTIFICATION NAME",
(
TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (ACTIVATED) / 1000 / 60 / 60 / 24
)
AS "CREATE DATE",
(
TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (EXPIRATION) / 1000 / 60 / 60 / 24
)
AS "EXPIRATION DATE",
(
TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SIGNED) / 1000 / 60 / 60 / 24
)
AS "SIGNED DATE",
SPT_CERTIFICATION.MANAGER AS "ASSIGNED TO USER",
SPT_IDENTITY.EMAIL AS "ASSIGNED USER EMAIL",
SPT_CERTIFICATION.TOTAL_ENTITIES AS "TOTAL IDENTITY INCLUDED",
SPT_CERTIFICATION.EXCLUDED_ENTITIES AS "TOTAL IDENTITY EXCLUDED",
SPT_CERTIFICATION.COMPLETED_ENTITIES AS "COUNT ACTION TAKEN ON IDENTITY",
SPT_CERTIFICATION.PERCENT_COMPLETE AS "PERCENTAGE ACTION TAKEN ON IDENTITY",
SPT_CERTIFICATION.CERTIFIED_ENTITIES,
SPT_CERTIFICATION.TOTAL_ITEMS AS "TOTAL ITEMS ASSIGNED",
SPT_CERTIFICATION.EXCLUDED_ITEMS AS "TOTAL ITEMS EXCLUDED",
SPT_CERTIFICATION.COMPLETED_ITEMS AS "COUNT ACTION TAKEN ON ITEM",
SPT_CERTIFICATION.ITEM_PERCENT_COMPLETE AS "PERCENTAGE ACTION TAKEN ON ITEM",
SPT_CERTIFICATION.CERTIFIED_ITEMS,
SPT_CERTIFICATION.REMEDIATIONS_KICKED_OFF,
SPT_CERTIFICATION.REMEDIATIONS_COMPLETED
FROM
SPT_CERTIFICATION,
SPT_IDENTITY,
SPT_CERTIFICATION_GROUPS,
SPT_CERTIFICATION_GROUP,
SPT_CERTIFICATION_DEFINITION
WHERE
SPT_IDENTITY.NAME = SPT_CERTIFICATION.MANAGER
AND SPT_CERTIFICATION_DEFINITION.ID = SPT_CERTIFICATION_GROUP.CERTIFICATION_DEFINITION
AND SPT_CERTIFICATION_GROUP.ID = SPT_CERTIFICATION_GROUPS.GROUP_ID
AND SPT_CERTIFICATION_GROUPS.CERTIFICATION_ID = SPT_CERTIFICATION.ID
AND SPT_CERTIFICATION_DEFINITION.ID = SPT_CERTIFICATION.CERTIFICATION_DEFINITION_ID
AND SPT_CERTIFICATION_GROUP.NAME = '<CERTIFICATION NAME>'
Same Query can be modified and can be used to get the data with more details like extended attribute from Identity and other deatils.
SPT_CERTIFICATION_DEFINITION - Certification Definition
SPT_CERTIFICATION_GROUP - Certification Details
SPT_CERTIFICATION_GROUPS - Link Certification and Access Review
SPT_CERTIFICATION - Access Review details doesn't include the Actioned Identity details
SELECT
SPT_CERTIFICATION_DEFINITION.NAME AS "CERTIFICATION DEFINITION NAME",
SHORT_NAME AS "CERTIFICATION NAME",
(
TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (ACTIVATED) / 1000 / 60 / 60 / 24
)
AS "CREATE DATE",
(
TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (EXPIRATION) / 1000 / 60 / 60 / 24
)
AS "EXPIRATION DATE",
(
TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SIGNED) / 1000 / 60 / 60 / 24
)
AS "SIGNED DATE",
SPT_CERTIFICATION.MANAGER AS "ASSIGNED TO USER",
SPT_IDENTITY.EMAIL AS "ASSIGNED USER EMAIL",
SPT_CERTIFICATION.TOTAL_ENTITIES AS "TOTAL IDENTITY INCLUDED",
SPT_CERTIFICATION.EXCLUDED_ENTITIES AS "TOTAL IDENTITY EXCLUDED",
SPT_CERTIFICATION.COMPLETED_ENTITIES AS "COUNT ACTION TAKEN ON IDENTITY",
SPT_CERTIFICATION.PERCENT_COMPLETE AS "PERCENTAGE ACTION TAKEN ON IDENTITY",
SPT_CERTIFICATION.CERTIFIED_ENTITIES,
SPT_CERTIFICATION.TOTAL_ITEMS AS "TOTAL ITEMS ASSIGNED",
SPT_CERTIFICATION.EXCLUDED_ITEMS AS "TOTAL ITEMS EXCLUDED",
SPT_CERTIFICATION.COMPLETED_ITEMS AS "COUNT ACTION TAKEN ON ITEM",
SPT_CERTIFICATION.ITEM_PERCENT_COMPLETE AS "PERCENTAGE ACTION TAKEN ON ITEM",
SPT_CERTIFICATION.CERTIFIED_ITEMS,
SPT_CERTIFICATION.REMEDIATIONS_KICKED_OFF,
SPT_CERTIFICATION.REMEDIATIONS_COMPLETED
FROM
SPT_CERTIFICATION,
SPT_IDENTITY,
SPT_CERTIFICATION_GROUPS,
SPT_CERTIFICATION_GROUP,
SPT_CERTIFICATION_DEFINITION
WHERE
SPT_IDENTITY.NAME = SPT_CERTIFICATION.MANAGER
AND SPT_CERTIFICATION_DEFINITION.ID = SPT_CERTIFICATION_GROUP.CERTIFICATION_DEFINITION
AND SPT_CERTIFICATION_GROUP.ID = SPT_CERTIFICATION_GROUPS.GROUP_ID
AND SPT_CERTIFICATION_GROUPS.CERTIFICATION_ID = SPT_CERTIFICATION.ID
AND SPT_CERTIFICATION_DEFINITION.ID = SPT_CERTIFICATION.CERTIFICATION_DEFINITION_ID
AND SPT_CERTIFICATION_GROUP.NAME = '<CERTIFICATION NAME>'
Same Query can be modified and can be used to get the data with more details like extended attribute from Identity and other deatils.
SPT_CERTIFICATION_DEFINITION - Certification Definition
SPT_CERTIFICATION_GROUP - Certification Details
SPT_CERTIFICATION_GROUPS - Link Certification and Access Review
SPT_CERTIFICATION - Access Review details doesn't include the Actioned Identity details
