Showing posts with label SPT_IDENTITY_REQUEST_ITEM. Show all posts
Showing posts with label SPT_IDENTITY_REQUEST_ITEM. Show all posts

Thursday, February 10, 2022

Sailpoint IdentityIQ Request Details SQL

 Request Details Using SQL

 SELECT 
	SPT_IDENTITY_REQUEST.NAME AS REQUESTID,
    SPT_IDENTITY_REQUEST.COMPLETION_STATUS AS REQUESTIDSTATUS,
    '"'||SPT_IDENTITY_REQUEST_ITEM.APPLICATION||'"' AS APPLICATIONNAME,
    '"'||SPT_IDENTITY_REQUEST.TARGET_DISPLAY_NAME||'"' AS REQUESTEENAME,
    SPT_IDENTITY_REQUEST_ITEM.OPERATION AS OPERATION,
    SPT_IDENTITY_REQUEST_ITEM.PROVISIONING_STATE AS PROVISIONINGSTATE,
    '"'||SPT_IDENTITY_REQUEST_ITEM.NAME||'"' AS ATTRNAME,
    '"'||SPT_IDENTITY_REQUEST_ITEM.VALUE||'"' AS ATTRVALUE
    FROM SPT_IDENTITY_REQUEST,
		SPT_IDENTITY_REQUEST_ITEM 
    WHERE 
    SPT_IDENTITY_REQUEST.ID=SPT_IDENTITY_REQUEST_ITEM.IDENTITY_REQUEST_ID AND 
    SPT_IDENTITY_REQUEST.TYPE='AccessRequest' AND 
    SPT_IDENTITY_REQUEST_ITEM.PROVISIONING_STATE != 'Finished' AND 
    SPT_IDENTITY_REQUEST.COMPLETION_STATUS IN ('Failure','Incomplete') AND  
    SPT_IDENTITY_REQUEST_ITEM.OPERATION != 'Set' AND 
    ((CAST(SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) AS DATE) - TO_DATE('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400*1000) -1*86400*1000 < SPT_IDENTITY_REQUEST.END_DATE