Monday, September 9, 2019

SQL ACCESS REVIEW CERTIFICATION ACTION ITEMS DETAILS SAILPOINT IDENTITY IQ

This query is used to get the details about the Access Review , Certification which is generated and will give all the details for all the items for which the action is already taken.
SELECT SPT_CERTIFICATION.ID AS "CERT_ID", ( TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION.ACTIVATED) / 1000 / 60 / 60 / 24 ) AS "CREATE DATE", ( TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION.EXPIRATION) / 1000 / 60 / 60 / 24 ) AS "EXPIRATION DATE", ( TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION.SIGNED) / 1000 / 60 / 60 / 24 ) AS "SIGNED DATE", SPT_CERTIFICATION.SHORT_NAME AS "CERT_NAME", SPT_CERTIFICATION_ITEM.SUMMARY_STATUS AS "CERT_STATUS", SPT_CERTIFICATION.MANAGER AS "CERT_ASSIGNED_TO_ID", SPT_IDENTITY.EMAIL AS "CERT_ASSIGNED_TO_EMAIL", SPT_IDENTITY.DISPLAY_NAME AS "CERT_ASSIGNED_TO_NAME", user2.DISPLAY_NAME AS "MANAGER_DISPLAY_NAME", USER2.EMAIL AS "MANAGER_EMAIL", SPT_CERTIFICATION_ENTITY.TARGET_NAME AS "USER_ID", SPT_CERTIFICATION_ENTITY.TARGET_DISPLAY_NAME AS "USER_DISPLAY_NAME", SPT_CERTIFICATION_ENTITY.FIRSTNAME AS "USER_FIRST_NAME", SPT_CERTIFICATION_ENTITY.LASTNAME AS "USER_LAST_NAME", user1.EMAIL AS "USER_EMAIL", SPT_CERTIFICATION.TOTAL_ENTITIES, SPT_CERTIFICATION.EXCLUDED_ENTITIES, SPT_CERTIFICATION.COMPLETED_ENTITIES, SPT_CERTIFICATION.PERCENT_COMPLETE, SPT_CERTIFICATION.CERTIFIED_ENTITIES, SPT_CERTIFICATION.TOTAL_ITEMS, SPT_CERTIFICATION.EXCLUDED_ITEMS, SPT_CERTIFICATION.COMPLETED_ITEMS, SPT_CERTIFICATION.ITEM_PERCENT_COMPLETE, SPT_CERTIFICATION.CERTIFIED_ITEMS, SPT_CERTIFICATION.REMEDIATIONS_KICKED_OFF, SPT_CERTIFICATION.REMEDIATIONS_COMPLETED, ( TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION.MODIFIED) / 1000 / 60 / 60 / 24 ) AS "CERT_UPDATE_DATE", SPT_CERTIFICATION_ITEM.EXCEPTION_APPLICATION AS "ACCOUNT_APPLICATION_NAME", SPT_CERTIFICATION_ITEM.EXCEPTION_ATTRIBUTE_NAME AS "RECORD_TYPE", SPT_IDENTITY_ENTITLEMENT.NATIVE_IDENTITY AS "ACCOUNT_NAME", SPT_CERTIFICATION_ITEM.EXCEPTION_ATTRIBUTE_VALUE AS "ACCOUNT_ENTITLEMENT_NAME", SPT_CERTIFICATION_ACTION.STATUS AS "CERT_DECISION", SPT_CERTIFICATION_ACTION.DESCRIPTION AS "CERT_DECISION_COMMENTS", SPT_CERTIFICATION_ACTION.REMEDIATION_ACTION AS "REMEDIATION_ACTION", TO_CHAR(SPT_CERTIFICATION_ACTION.REMEDIATION_DETAILS) AS "REMEDIATION_DETAILS", ( TO_DATE('1970-01-01 00', 'YYYY-MM-DD HH24') + (SPT_CERTIFICATION_ACTION.DECISION_DATE) / 1000 / 60 / 60 / 24 ) AS "CERT_DECISION_DATE", TO_CHAR(Regexp_substr(SPT_CERTIFICATION_ACTION.REMEDIATION_DETAILS, 'requestID="(.*?)"\sstatus', 1, 1, NULL, 1)) AS TICKET, TO_CHAR(Regexp_substr(SPT_CERTIFICATION_ACTION.REMEDIATION_DETAILS, 'status="(.*?)"\/', 1, 1, NULL, 1)) AS TICKET_STATUS, SPT_CERTIFICATION_ITEM.ACTION AS "ACTION" FROM SPT_CERTIFICATION_ENTITY, SPT_CERTIFICATION, SPT_CERTIFICATION_ITEM, SPT_IDENTITY, SPT_CERTIFICATION_ACTION, SPT_IDENTITY user1, SPT_IDENTITY user2, SPT_IDENTITY_ENTITLEMENT, SPT_APPLICATION WHERE SPT_CERTIFICATION_ENTITY.CERTIFICATION_ID = SPT_CERTIFICATION.ID AND SPT_CERTIFICATION_ITEM.CERTIFICATION_ENTITY_ID = SPT_CERTIFICATION_ENTITY.ID AND SPT_IDENTITY.NAME = SPT_CERTIFICATION.MANAGER AND SPT_CERTIFICATION_ACTION.ID = SPT_CERTIFICATION_ITEM.ACTION AND SPT_CERTIFICATION_ENTITY.TARGET_NAME = user1.NAME AND user1.MANAGER = user2.ID AND SPT_CERTIFICATION_ITEM.EXCEPTION_APPLICATION = SPT_APPLICATION.NAME AND SPT_CERTIFICATION_ITEM.EXCEPTION_ATTRIBUTE_VALUE = SPT_IDENTITY_ENTITLEMENT.VALUE AND user1.ID = SPT_IDENTITY_ENTITLEMENT.IDENTITY_ID AND SPT_APPLICATION.ID = SPT_IDENTITY_ENTITLEMENT.APPLICATION