TPAG - Who raised access to what approver details

[MM]

Hi All,

Does anyone have a query to find out who raised the tpag request who approved them and for whom?

Regards,

[rushikeshvartak]

• You can join request_access and ars_requests table to get same information

[MM]

Thanks - but what is the identifier for TPAG?

[rushikeshvartak]

SELECT a.comments,
       a.requestdate,
       Substr(jbpmprocessinstanceid, Instr(jbpmprocessinstanceid, '.') + 1,
       Length(
       jbpmprocessinstanceid)) AS requestid,
       CASE
         WHEN a.status = 1 THEN 'New'
         WHEN a.status = 2 THEN 'INPROCESS'
         WHEN a.status = 3 THEN 'COMPLETED'
         WHEN a.status = 4 THEN 'EXPIRES'
         WHEN a.status = 6 THEN 'DISCONTINUE'
       END                     'REQUEST STATUS',
       u.displayname           AS requestor
FROM   ars_requests AS a
       JOIN users AS u
         ON a.requestor = u.userkey
       JOIN request_access ra
         ON ra.requestkey = a.requestkey
WHERE  a.requesttype IN ( 16, 33 ) 

[MM]

Hi Rushikesh,

Thanks for the query. I can use this query and get data from requesttype however, I am still unable to filter it to request for TPAG users. in your query above a.requesttype is a key value for requesttype but how do I get this value for TPAG users

[rushikeshvartak]

• Please share sample request screenshot

[MM]

[rushikeshvartak]

Does old query required output? or what is missing

[MM]

The query you mentioned has "WHERE  a.requesttype IN ( 16, 33 ) ". My question is how do I find out whats the integer value for requesttype in my env for TPAG requests

[rushikeshvartak]

SELECT a.comments,
a.requestdate,
Substr(jbpmprocessinstanceid, Instr(jbpmprocessinstanceid, '.') + 1,
Length(
jbpmprocessinstanceid)) AS requestid,
CASE
WHEN a.status = 1 THEN 'New'
WHEN a.status = 2 THEN 'INPROCESS'
WHEN a.status = 3 THEN 'COMPLETED'
WHEN a.status = 4 THEN 'EXPIRES'
WHEN a.status = 6 THEN 'DISCONTINUE'
END 'REQUEST STATUS',
u.displayname AS requestor,
u2.displayname AS enduser
FROM ars_requests AS a
JOIN users AS u
ON a.requestor = u.userkey
JOIN request_access ra
ON ra.requestkey = a.requestkey
JOIN users u2
ON ra.userkey =u2.userkey
WHERE a.requesttype IN ( 18 )

[MM]

[MM]

There is nothing with requesttype IN ( 18 ). I just want to know way to find requesttype key for tpag users

[rushikeshvartak]

You can find request type on any existing requests on url if not then with requestkey run query on data analyzer table