How to query "CREATE USER" Request in report

[ejeong]

we need to query create user request including requestor information to send this to Audit team. 

I copied one request ID and tried to search this from ARSTASKS table and ARS_REQUESTS table but I couldnt find anything. can you please let me know where to search this?

Please let me know where this information in stored

[KirtiAjrot]

Request type for Create User = 18 . 

You could use this to query the ars_requests table for the create user requests . 

Alternatively , use the column jbpmprocessinstanceid like '%268xxxx%'  in ars_requests table. 

[ejeong]

thanks, if we want to show "requested for" as well. where is it stored? 

I cant find that column in ars_reqeusts table 

[ejeong]

also where can I see "approved by" information as well.

[rushikeshvartak]

What’s your requirements 

requested for - you need join users

approved by access_approvers

[ejeong]

I need to show

Requestkey

Requested for

Requestor

Approved by

For all "create user" request.

But in ars_requests table, i can see requestor which is userkey for requestor

But i dont see userkey for requested for and approved by

Thanks

[rushikeshvartak]

select
SUBSTR(jbpmprocessinstanceid, INSTR(jbpmprocessinstanceid, '.') + 1, LENGTH(jbpmprocessinstanceid)) AS 'REQUEST ID',
ar.REQUESTDATE AS 'REQUEST SUBMIT DATE',
u1.username as requestedby,
u2.username as requestedfor,
u3.username as approver

from ars_Requests ar ,users u1,request_access ra,users u2,access_approvers aa,users u3

where ar.requesttype=18 and
ar.requestor=u1.userkey
and ar.requestkey=ra.requestkey
and ra.userkey=u2.userkey and
aa.request_access_key=ra.request_accesskey
and u3.userkey=aa.approverkey