Fetch access request details from the IdentityIQ database

IdentityIQ (IIQ) IIQ Discussion and Questions
1

Hey everyone!
Can anyone help with the SQL query to fetch access request details from the IdentityIQ database? Looking to pull information like request ID, requester, requested for, access items, status and submission date.

2

Hi @utkirjonkamiljanov

This question is specific to IIQ. You can post this in IIQ Discussions and Questions.

3

I have worked on a similar query for my environment. You can try this:

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'))
4

Thanks for letting me know!

5

@msingh900 T he query has worked. I really appreciate your prompt reply!