SAP - analytics report to fetch sap roles, tcode, auth object details

[shivmano]

Hi Team - 

I am looking for an analytics query to fetch users with SAP accounts along with their assigned SAP roles, Profiles, tcode and Auth Object details. Please can someone advise how this can be pulled ? 

Thank you

[Raghu]

@shivmano  use below query and update endpointnmae and as per requiment u can add columns

select distinct acc.name as 'ACCOUNT NAME', ep.ENDPOINTNAME as 'ENDPOINT', ev.ENTITLEMENT_VALUE as 'EntitlementTypeName'
from accounts acc, endpoints ep, entitlement_types et, entitlement_values ev, account_entitlements1 ae1
where acc.ENDPOINTKEY = ep.ENDPOINTKEY and ep.ENDPOINTKEY = et.ENDPOINTKEY and ae1.ACCOUNTKEY = acc.ACCOUNTKEY
and ae1.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY and et.ENTITLEMENTTYPEKEY = ev.ENTITLEMENTTYPEKEY
and ep.ENDPOINTNAME = 'XXX';

[rushikeshvartak · Accepted solution]

SELECT a.NAME,
       ev.entitlement_value,
       ev.entitlement_valuekey,
       ao.objectname              AS 'Auth Object',
       Concat('Field Name ', f.fieldname, ' Min Value ', eo.minvalue,
       ' Max Value ', eo.mxvalue) value
FROM   account_entitlements1 ae,
       accounts a,
       entitlement_values ev,
       endpoints e,
       securitysystems ss,
       entitlement_objects eo,
       access_objects ao,
       fields f
WHERE  ae.accountkey = 1085788
       AND ev.entitlement_value = 'ZP-CF1-FI271'
       AND a.accountkey = ae.accountkey
       AND ae.entitlement_valuekey = ev.entitlement_valuekey
       AND e.endpointkey = a.endpointkey
       AND e.securitysystemkey = ss.systemkey
       AND eo.entitlement_valuekey = ev.entitlement_valuekey
       AND ao.objectkey = eo.objectkey

https://forums.saviynt.com/t5/identity-governance/how-to-export-sap-account-details-with-associated-... 

[shivmano]

Thank you @rushikeshvartak , I am also looking to get the associated T-CODE in the same query. Please can you let me know how to get that

[shivmano]

I was able to fetch the TCODEs additionally by using the below query. Thank you @rushikeshvartak 

SELECT
U.USERNAME AS 'USERNAME',
CASE WHEN U.STATUSKEY IS NULL THEN 'NOT AVAILABLE' WHEN U.STATUSKEY = 0 THEN 'INACTIVE' WHEN U.STATUSKEY = 1 THEN 'ACTIVE' ELSE U.STATUSKEY END AS 'USER STATUS',
e.ENDPOINTNAME AS APPLICATION,
A.NAME AS 'SAP ACCOUNT NAME',
CASE WHEN A.STATUS = 1 THEN 'Active' WHEN A.STATUS = 2 THEN 'Inactive' ELSE A.STATUS END AS 'Account Status',
ev.entitlement_value AS 'SAP Role',
ev2.ENTITLEMENT_VALUE AS 'TCODE',
ev3.ENTITLEMENT_VALUE AS grandchildEntitlement,
ao.objectname AS 'Auth Object',
Concat(
'Field Name ', f.fieldname, ' Min Value ',
eo.minvalue, ' Max Value ', eo.mxvalue
) AS Value
FROM
ACCOUNTS A
LEFT JOIN USER_ACCOUNTS UA ON A.ACCOUNTKEY = UA.ACCOUNTKEY
LEFT JOIN USERS U ON U.USERKEY = UA.USERKEY
LEFT JOIN account_entitlements1 ae ON A.ACCOUNTKEY = AE.ACCOUNTKEY
LEFT JOIN endpoints e ON A.ENDPOINTKEY = e.ENDPOINTKEY
LEFT JOIN entitlement_values ev ON ae.ENTITLEMENT_VALUEKEY = ev.ENTITLEMENT_VALUEKEY
LEFT JOIN entitlement_types et ON ev.ENTITLEMENTTYPEKEY = et.ENTITLEMENTTYPEKEY
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 e3 ON e2.ENTITLEMENT_VALUE2KEY = e3.entitlement_value1key
LEFT JOIN entitlement_values ev3 ON ev3.ENTITLEMENT_VALUEKEY = e3.entitlement_value2key
LEFT JOIN entitlement_objects eo ON ev.ENTITLEMENT_VALUEKEY = eo.ENTITLEMENT_VALUEKEY
LEFT JOIN access_objects ao ON eo.OBJECTKEY = ao.OBJECTKEY
LEFT JOIN fields f ON eo.FIELD_KEY = f.FIELDKEY
WHERE
e.ENDPOINTNAME = < enpointname >

[rushikeshvartak]

Please click the 'Accept As Solution' button on the reply (or replies) that best answered your original question.