All Tcode value is not displaying

[PreethiPandi]

Hi Team,

We are trying to fetch the Tcode information from the request(request type: Emergency Session request) but we got only one tcode (ME22N)

Please find the below query:

SELECT DISTINCT
SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) AS REQUESTNUMBER ,
AR.ENDPOINTASCSV AS 'ENDPOINT NAME',
concat(U.USERNAME,'[',U.FIRSTNAME,' ',U.LASTNAME,']') AS 'USERNAME',
A.NAME AS 'ACCOUNTNAME',
EV.ENTITLEMENT_VALUE AS 'TCODE' ,
RAA.REQUEST_ACCESS_KEY as 'ReqAccessKey',
RAA.ATTRIBUTE_VALUE as Ent_Keys,
(select GROUP_CONCAT(ENTITLEMENT_VALUE) from entitlement_values where ENTITLEMENT_VALUEKEY IN (RAA.ATTRIBUTE_VALUE)) as 'Entitlement_Value_key',
EV.ENTITLEMENT_VALUE
FROM ARS_REQUESTS AR LEFT JOIN REQUEST_ACCESS RA ON AR.REQUESTKEY=RA.REQUESTKEY
left join request_access_attrs RAA on RAA.request_access_key=RA.REQUEST_ACCESSKEY
LEFT JOIN USERS U ON RA.USERKEY=U.USERKEY
LEFT JOIN ACCOUNTS A ON RA.ACCESSKEY=A.ACCOUNTKEY
LEFT JOIN ENTITLEMENT_VALUES EV ON RAA.ATTRIBUTE_VALUE=EV.ENTITLEMENT_VALUEKEY
WHERE RAA.ATTRIBUTE_NAME LIKE '%TCODE%' AND AR.REQUESTTYPE='23' and SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) = '24908132';

o/p:

If we are passing the entitlement value key directly we are getting the values,

select GROUP_CONCAT(ENTITLEMENT_VALUE) as 'tcode' from entitlement_values where ENTITLEMENT_VALUEKEY IN (12598529,12618579,12712090,12616869,12598603,12712076);

Please help us on this

[nimitdave]

@PreethiPandi Pls try with below and modify as required.

select ar.REQUESTKEY,ar.REQUESTTYPE,ar.STATUS,et.DISPLAYNAME ,ev.ENTITLEMENT_VALUE,ev.DISPLAYNAME
from ars_requests ar
inner join request_access ra on ra.REQUESTKEY =ar.REQUESTKEY
inner join entitlement_values ev on ra.ACCESSKEY =ev.ENTITLEMENT_VALUEKEY
inner join entitlement_types et on ev.ENTITLEMENTTYPEKEY = et.ENTITLEMENTTYPEKEY
where SUBSTRING_INDEX(ar.JBPMPROCESSINSTANCEID, '.', - 1) = '<reqid>';

[PreethiPandi]

@nimitdave I tried the query which you have posted. But still Tcode Value is not displaying

[nimitdave]

@PreethiPandi , what the above is returning in your case , if its the emergency role then you can join with role_entitlements., to get the SAP roles for the emergency access  and then join with entitlements2 to get the tcodes.

The query I shared will give tcodes if tcodes are requeted directly in the request.

[PreethiPandi]

@nimitdave This is was it is returning

[rushikeshvartak]

Refer https://forums.saviynt.com/t5/identity-governance/can-t-able-to-fetch-child-entitlement-s-child-enti...

[nimitdave]

@PreethiPandi , did you try   to join with role_entitlements., to get the SAP roles for the emergency access  and then join with entitlements2 to get the tcodes.

[PreethiPandi]

Hi @rushikeshvartak ,

I have tried with the link what you have referred it is not returning the data tcode value is empty .

Please find the below query which i have used.

SELECT DISTINCT
SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) AS REQUESTNUMBER ,
concat(U.USERNAME,'[',U.FIRSTNAME,' ',U.LASTNAME,']') AS 'USERNAME',
A.NAME AS 'ACCOUNTNAME',
RAA.ATTRIBUTE_VALUE as Ent_Keys,
ev2.entitlement_value AS 'SAP Role',
ev22.entitlement_value AS 'TCODE'
FROM ARS_REQUESTS AR LEFT JOIN REQUEST_ACCESS RA ON AR.REQUESTKEY=RA.REQUESTKEY
left join request_access_attrs RAA on RAA.request_access_key=RA.REQUEST_ACCESSKEY
LEFT JOIN USERS U ON RA.USERKEY=U.USERKEY
LEFT JOIN ACCOUNTS A ON RA.ACCESSKEY=A.ACCOUNTKEY
LEFT JOIN ENTITLEMENT_VALUES EV ON RAA.ATTRIBUTE_VALUE=EV.ENTITLEMENT_VALUEKEY
LEFT JOIN entitlements2 e2 ON RAA.ATTRIBUTE_VALUE = e2.entitlement_value1key
LEFT JOIN entitlement_values ev2 ON ev2.entitlement_valuekey = e2.entitlement_value2key
LEFT JOIN entitlements2 e22 ON e2.entitlement_value2key = e22.entitlement_value1key
LEFT JOIN entitlement_values ev22 ON ev22.entitlement_valuekey = e22.entitlement_value2key
WHERE RAA.ATTRIBUTE_NAME LIKE '%TCODE%' AND AR.REQUESTTYPE='23' and SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) = '24908132';

Hi @nimitdave  

I have  joined with role_entitlements., to get the SAP roles for the emergency access  and then joined with entitlements2 to get the tcodes but nodata is displayed.

Please find the below query where i have added role_entitlements and entitlements2,

SELECT DISTINCT SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) AS REQUESTNUMBER,
AR.ENDPOINTASCSV AS 'ENDPOINT NAME',
concat(U.USERNAME,'[',U.FIRSTNAME,' ',U.LASTNAME,']') AS 'USERNAME',
A.NAME AS 'ACCOUNTNAME',
EV.ENTITLEMENT_VALUE,ev2.entitlement_value AS 'SAP Role',
ev22.entitlement_value AS 'TCODE'
FROM ARS_REQUESTS AR
inner JOIN REQUEST_ACCESS RA ON AR.REQUESTKEY=RA.REQUESTKEY
inner join roles R on R.ROLEKEY=RA.ACCESSKEY
inner join role_entitlements RE on R.rolekey = RE.rolekey
inner JOIN ENTITLEMENT_VALUES EV ON EV.ENTITLEMENT_VALUEKEY=RE.entitlement_valuekey
LEFT JOIN entitlements2 e2 ON EV.entitlement_valuekey = e2.entitlement_value1key
LEFT JOIN entitlement_values ev2 ON ev2.entitlement_valuekey = e2.entitlement_value2key
LEFT JOIN entitlements2 e22 ON e2.entitlement_value2key = e22.entitlement_value1key
LEFT JOIN entitlement_values ev22 ON ev22.entitlement_valuekey = e22.entitlement_value2key
inner JOIN USERS U ON RA.USERKEY=U.USERKEY
inner JOIN ACCOUNTS A ON RA.ACCESSKEY=A.ACCOUNTKEY
WHERE AR.REQUESTTYPE='23' and SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) = '24908132';

Please help us on this.

Regards,

Preethi

[rushikeshvartak]

Please share request screenshot again

[PreethiPandi]

Hi @rushikeshvartak ,

We are trying to fetch the Tcode information from the request(request type: Emergency Session request) but we got only one tcode (ME22N)

Please find the below query:

SELECT DISTINCT
SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) AS REQUESTNUMBER ,
AR.ENDPOINTASCSV AS 'ENDPOINT NAME',
concat(U.USERNAME,'[',U.FIRSTNAME,' ',U.LASTNAME,']') AS 'USERNAME',
A.NAME AS 'ACCOUNTNAME',
EV.ENTITLEMENT_VALUE AS 'TCODE' ,
RAA.REQUEST_ACCESS_KEY as 'ReqAccessKey',
RAA.ATTRIBUTE_VALUE as Ent_Keys,
(select GROUP_CONCAT(ENTITLEMENT_VALUE) from entitlement_values where ENTITLEMENT_VALUEKEY IN (RAA.ATTRIBUTE_VALUE)) as 'Entitlement_Value_key',
EV.ENTITLEMENT_VALUE
FROM ARS_REQUESTS AR LEFT JOIN REQUEST_ACCESS RA ON AR.REQUESTKEY=RA.REQUESTKEY
left join request_access_attrs RAA on RAA.request_access_key=RA.REQUEST_ACCESSKEY
LEFT JOIN USERS U ON RA.USERKEY=U.USERKEY
LEFT JOIN ACCOUNTS A ON RA.ACCESSKEY=A.ACCOUNTKEY
LEFT JOIN ENTITLEMENT_VALUES EV ON RAA.ATTRIBUTE_VALUE=EV.ENTITLEMENT_VALUEKEY
WHERE RAA.ATTRIBUTE_NAME LIKE '%TCODE%' AND AR.REQUESTTYPE='23' and SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) = '24908132';

o/p:

If we are passing the entitlement value key directly we are getting the values,

select GROUP_CONCAT(ENTITLEMENT_VALUE) as 'tcode' from entitlement_values where ENTITLEMENT_VALUEKEY IN (12598529,12618579,12712090,12616869,12598603,12712076);

Regards,

Preethi

[PreethiPandi]

Hi Team,

Any update?

Can you please help us on this.

Regards,

Preethi

[nimitdave]

@PreethiPandi pls try this one:

select ar.REQUESTKEY,ar.REQUESTTYPE,ar.STATUS,et.DISPLAYNAME 'enttype',ev.ENTITLEMENT_VALUE,ev.DISPLAYNAME 'entname'
from ars_requests ar
inner join request_access ra on ra.REQUESTKEY =ar.REQUESTKEY
inner join roles ro on ro.ROLEKEY = ra.ACCESSKEY
inner join role_entitlements re on ro.ROLEKEY = re.ROLEKEY
inner join entitlement_values ev on re.ENTITLEMENT_VALUEKEY =ev.ENTITLEMENT_VALUEKEY
inner join entitlement_types et on ev.ENTITLEMENTTYPEKEY = et.ENTITLEMENTTYPEKEY
where SUBSTRING_INDEX(ar.JBPMPROCESSINSTANCEID, '.', - 1) = '<reqid>';

If above does not work the check below:

select ar.REQUESTKEY,ar.REQUESTTYPE,ar.STATUS,ra.ACCESSKEY
from ars_requests ar
inner join request_access ra on ra.REQUESTKEY =ar.REQUESTKEY
where SUBSTRING_INDEX(ar.JBPMPROCESSINSTANCEID, '.', - 1) = '<reqid>';

above will give you an accesskey, so check if that accesskey is a rolekey for the emergencyrole in roles table or is it a entitlement_valuekey in the entitlement

[PreethiPandi]

Hi @nimitdave 

First code is returning no value

Second code with 

and second code is accesskey is not displaying any roles or entitlement and it is not mapping with rolekey and entitlement_valuekey.

Regards,

Preethi

[PreethiPandi]

Hi Team,

Any update?

Can you please help us on this.

Regards,

Preethi

[rushikeshvartak]

Can you provide proper screenshot , as screenshot is blur 

[PreethiPandi]

Hi @rushikeshvartak,

Please find the below screenshot,

We are trying to fetch the Tcode information from the request(request type: Emergency Session request) but we got only one tcode (ME22N)

Please find the below query:

SELECT DISTINCT
SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) AS REQUESTNUMBER ,
AR.ENDPOINTASCSV AS 'ENDPOINT NAME',
concat(U.USERNAME,'[',U.FIRSTNAME,' ',U.LASTNAME,']') AS 'USERNAME',
A.NAME AS 'ACCOUNTNAME',
EV.ENTITLEMENT_VALUE AS 'TCODE' ,
RAA.REQUEST_ACCESS_KEY as 'ReqAccessKey',
RAA.ATTRIBUTE_VALUE as Ent_Keys,
(select GROUP_CONCAT(ENTITLEMENT_VALUE) from entitlement_values where ENTITLEMENT_VALUEKEY IN (RAA.ATTRIBUTE_VALUE)) as 'Entitlement_Value_key',
EV.ENTITLEMENT_VALUE
FROM ARS_REQUESTS AR LEFT JOIN REQUEST_ACCESS RA ON AR.REQUESTKEY=RA.REQUESTKEY
left join request_access_attrs RAA on RAA.request_access_key=RA.REQUEST_ACCESSKEY
LEFT JOIN USERS U ON RA.USERKEY=U.USERKEY
LEFT JOIN ACCOUNTS A ON RA.ACCESSKEY=A.ACCOUNTKEY
LEFT JOIN ENTITLEMENT_VALUES EV ON RAA.ATTRIBUTE_VALUE=EV.ENTITLEMENT_VALUEKEY
WHERE RAA.ATTRIBUTE_NAME LIKE '%TCODE%' AND AR.REQUESTTYPE='23' and SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) = '24908132';

o/p:

If we are passing the entitlement value key directly we are getting the values,

select GROUP_CONCAT(ENTITLEMENT_VALUE) as 'tcode' from entitlement_values where ENTITLEMENT_VALUEKEY IN (12598529,12618579,12712090,12616869,12598603,12712076);

Regards,

Preethi

[PreethiPandi]

Hi Team,

Any update?

Can you please help us on this.

Regards,

Preethi

[rushikeshvartak]

Can you show under which tab from Admin - Entitlements tcodes are visible

also share result of below query

SELECT DISTINCT
SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) AS REQUESTNUMBER ,
AR.ENDPOINTASCSV AS 'ENDPOINT NAME',

RAA.ATTRIBUTE_NAME,

RAA.ATTRIBUTE_VALUE,

FROM ARS_REQUESTS AR LEFT JOIN REQUEST_ACCESS RA ON AR.REQUESTKEY=RA.REQUESTKEY
left join request_access_attrs RAA on RAA.request_access_key=RA.REQUEST_ACCESSKEY
LEFT JOIN USERS U ON RA.USERKEY=U.USERKEY
LEFT JOIN ACCOUNTS A ON RA.ACCESSKEY=A.ACCOUNTKEY
LEFT JOIN ENTITLEMENT_VALUES EV ON RAA.ATTRIBUTE_VALUE=EV.ENTITLEMENT_VALUEKEY
WHERE AR.REQUESTTYPE='23' and SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) = '24908132';

[PreethiPandi]

Hi @rushikeshvartak ,

Please find the result for the above query which you have requested.

For Entitlements tcodes are visible I will check and update on this.

Regards,

Preethi

[PreethiPandi]

Hi @rushikeshvartak ,

Please find the Entitlements tcodes screenshot

Regards,

Preethi
[This message has been edited by moderator to mask sensitive info from image]

[rushikeshvartak]

Keep company-specific private information masked on public forums, such as the name and URL.

SELECT DISTINCT
SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) AS REQUESTNUMBER ,
AR.ENDPOINTASCSV AS 'ENDPOINT NAME',
concat(U.USERNAME,'[',U.FIRSTNAME,' ',U.LASTNAME,']') AS 'USERNAME',
A.NAME AS 'ACCOUNTNAME',
EV.ENTITLEMENT_VALUE AS 'TCODE' ,
RAA.REQUEST_ACCESS_KEY as 'ReqAccessKey',
RAA.ATTRIBUTE_VALUE as Ent_Keys,
(select GROUP_CONCAT(ENTITLEMENT_VALUE) from entitlement_values where ENTITLEMENT_VALUEKEY IN (RAA.ATTRIBUTE_VALUE)) as 'Entitlement_Value_key',
EV.ENTITLEMENT_VALUE
FROM ARS_REQUESTS AR LEFT JOIN REQUEST_ACCESS RA ON AR.REQUESTKEY=RA.REQUESTKEY
left join request_access_attrs RAA on RAA.request_access_key=RA.REQUEST_ACCESSKEY
LEFT JOIN USERS U ON RA.USERKEY=U.USERKEY
LEFT JOIN ACCOUNTS A ON RA.ACCESSKEY=A.ACCOUNTKEY
LEFT JOIN ENTITLEMENT_VALUES EV ON RAA.ATTRIBUTE_VALUE=EV.ENTITLEMENT_VALUEKEY
WHERE RAA.ATTRIBUTE_NAME LIKE '%Tcodes_To_Execute%' AND AR.REQUESTTYPE='23' and SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) = '24908132';

[PreethiPandi]

Hi @rushikeshvartak,

Sure.

Can you please help in fetching all the tcode value.

Regards,

Preethi

[rushikeshvartak]

Didn’t above query return required result, provide screenshot 

[PreethiPandi]

Hi @rushikeshvartak ,

We have 6 entitlement value key but from the query it is returning only one entitlement value.

We need all the 6 entitlement value to be displayed.

Please find the result screen shot for the above query which it is returning.

Regards,

Preethi

[PreethiPandi]

Hi Team,

Any update?

Can you please help us on this.

Regards,

Preethi

[rushikeshvartak]

select GROUP_CONCAT(ENTITLEMENT_VALUE) from entitlement_values where ENTITLEMENT_VALUEKEY IN (replace(RAA.ATTRIBUTE_VALUE,',',","))

[PreethiPandi]

Hi @rushikeshvartak,

select GROUP_CONCAT(ENTITLEMENT_VALUE) from entitlement_values where ENTITLEMENT_VALUEKEY IN (replace(RAA.ATTRIBUTE_VALUE,',',","))

The above query is  returning only one tcode not all the tcode.

Can you please check this.

Regards,

Preethi

[rushikeshvartak]

SELECT GROUP_CONCAT(ENTITLEMENT_VALUE)
FROM entitlement_values
WHERE ENTITLEMENT_VALUEKEY IN (SELECT REPLACE(RAA.ATTRIBUTE_VALUE, ',', ',') FROM dual);

[PreethiPandi]

Hi @rushikeshvartak,

SELECT GROUP_CONCAT(ENTITLEMENT_VALUE)
FROM entitlement_values
WHERE ENTITLEMENT_VALUEKEY IN (SELECT REPLACE(RAA.ATTRIBUTE_VALUE, ',', ',') FROM dual);

The above query is  returning only one tcode same as the above one not all the tcode.

Can you please check this.

Regards,

Preethi

[PreethiPandi]

Hi Team,

Can you please provide any update on this.

Regards,

Preethi

[PreethiPandi]

Hi Team,

Can you please provide any update on this.

Regards,

Preethi

[Dhruv_S]

Hi @PreethiPandi 

We have created a ticket INC-2019225 on this issue. We will be reaching out to you to connect for further steps and information needed.

Thanks,

Dhruv Sharma